{"id":"postgres-database-migration","name":"postgres-database-migration","summary":"このスキルを活用して、特に本番データや共有データベースを扱う際に、PostgreSQLスキーマの移行を計画、テスト、安全に実行してください。","body":"# PostgreSQL Database Migrations\n\nA schema migration that works on an empty dev database can fail, lock, or corrupt data on a production table with millions of rows. This guide covers how to assess risk, test against real data, and execute migrations safely.\n\n## DDL Lock Reference\n\nEvery schema change acquires a lock. The critical question is: **does it block reads and writes, and for how long?**\n\n### Fast, Non-Blocking Operations\n\nThese complete in milliseconds regardless of table size. They only hold a brief `AccessExclusiveLock` for the catalog update, not for data rewriting.\n\n| Operation | Lock Level | Notes |\n|-----------|-----------|-------|\n| `ADD COLUMN` (nullable, no default) | `AccessExclusiveLock` (brief) | **Fast.** No table rewrite. Metadata-only change. |\n| `ADD COLUMN ... DEFAULT x` (PG 11+) | `AccessExclusiveLock` (brief) | **Fast.** Non-volatile defaults stored in catalog, not backfilled. |\n| `DROP COLUMN` | `AccessExclusiveLock` (brief) | **Fast.** Column marked invisible; space reclaimed by VACUUM over time. |\n| `SET DEFAULT` / `DROP DEFAULT` | `AccessExclusiveLock` (brief) | Metadata change only. Does not touch existing rows. |\n| `CREATE INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Allows reads and writes during build. Slower than regular index creation. |\n| `DROP INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Waits for queries using the index to finish, then drops. No table-level exclusive lock. |\n| `RENAME COLUMN` | `AccessExclusiveLock` (brief) | Metadata change only. Fast. |\n| `RENAME TABLE` | `AccessExclusiveLock` (brief) | Metadata change only. Fast. |\n| `ADD CONSTRAINT ... NOT VALID` | `ShareUpdateExclusiveLock` | Adds constraint for new rows only. Does not scan existing data. |\n| `VALIDATE CONSTRAINT` | `ShareUpdateExclusiveLock` | Scans existing rows but allows concurrent reads and writes. |\n| `CREATE/DROP TRIGGER` | `ShareRowExclusiveLock` | Brief catalog update. |\n\n### Slow or Blocking Operations\n\nThese rewrite the table or scan all rows. On large tables, they can lock out all access for seconds to hours.\n\n| Operation | Lock Level | Why It's Slow |\n|-----------|-----------|---------------|\n| `ADD COLUMN ... DEFAULT x` (volatile, e.g. `now()`, `gen_random_uuid()`) | `AccessExclusiveLock` | Full table rewrite. Every row gets the computed value. |\n| `ALTER COLUMN TYPE` (most type changes) | `AccessExclusiveLock` | Full table rewrite to convert stored data. |\n| `SET NOT NULL` (PG < 12, or without existing CHECK) | `AccessExclusiveLock` | Full table scan to verify no NULLs. See safe pattern below. |\n| `ADD CONSTRAINT ... CHECK/UNIQUE/FK` (validated) | `AccessExclusiveLock` or `ShareRowExclusiveLock` | Scans all rows to verify, blocks writes. |\n| `CREATE INDEX` (without CONCURRENTLY) | `ShareLock` | Blocks writes for the entire build duration. |\n| `CLUSTER` | `AccessExclusiveLock` | Rewrites entire table in index order. |\n| `VACUUM FULL` | `AccessExclusiveLock` | Rewrites table to reclaim space. |\n\n**Key insight:** `AccessExclusiveLock` blocks everything — reads and writes. Even if the operation itself is fast (milliseconds), it must wait for all in-flight transactions to finish before acquiring the lock. A long-running query or idle transaction can cause an `ALTER TABLE` to hang and queue up all subsequent queries behind it.\n\n## Safe Migration Patterns\n\n### Add a Column\n\n```sql\n-- SAFE: nullable column, no default — instant\nALTER TABLE orders ADD COLUMN tracking_number TEXT;\n\n-- SAFE (PG 11+): column with non-volatile default — instant\nALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;\n\n-- UNSAFE: column with volatile default — full table rewrite\n-- DON'T: ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();\n-- DO: add nullable, then backfill, then set default + NOT NULL\nALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ;\n-- Backfill in batches (see Backfill section)\nALTER TABLE orders ALTER COLUMN created_at SET DEFAULT now();\nALTER TABLE orders ALTER COLUMN created_at SET NOT NULL;  -- only if PG12+ or CHECK exists\n```\n\n### Drop a Column\n\n```sql\n-- SAFE: instant (column marked invisible, space reclaimed by VACUUM)\nALTER TABLE orders DROP COLUMN old_status;\n```\n\n**Application coordination:** Ensure your application no longer references the column before dropping it. For zero-downtime deploys, this requires two steps:\n1. Deploy code that doesn't read/write the column\n2. Then drop the column in a separate migration\n\n**Security caveat:** `DROP COLUMN` does not physically delete the data. The column is marked as dropped in `pg_attribute` but the values remain on disk until `VACUUM` reclaims the space — and even then, a superuser could recover them. If the column contains sensitive data, run `VACUUM FULL` on the table after dropping, or use dump/restore to ensure the data is truly gone.\n\n### Rename a Column\n\n```sql\n-- SAFE: instant metadata change\nALTER TABLE orders RENAME COLUMN status TO order_status;\n```\n\n**Warning:** This breaks any application code, views, or functions that reference the old column name. For zero-downtime deploys, use the column-swap pattern instead:\n1. Add the new column\n2. Deploy code that writes to both columns\n3. Backfill old rows\n4. Deploy code that reads from the new column\n5. Drop the old column\n\n### Change a Column Type\n\nMost type changes rewrite the entire table. Safe alternatives:\n\n```sql\n-- UNSAFE: full table rewrite, blocks everything\n-- DON'T: ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(12,2);\n\n-- SAFE: use a new column + backfill\nALTER TABLE orders ADD COLUMN amount_new NUMERIC(12,2);\n\n-- Backfill in batches (see Backfill section below)\nUPDATE orders SET amount_new = amount WHERE id BETWEEN 1 AND 10000;\n-- ... continue in batches ...\n\n-- Swap columns\nALTER TABLE orders DROP COLUMN amount;\nALTER TABLE orders RENAME COLUMN amount_new TO amount;\n```\n\n**Exception:** Some casts don't require a rewrite and are fast:\n\n| From | To | Rewrite? |\n|------|----|----------|\n| `VARCHAR(n)` → `VARCHAR(m)` where m > n | No | Metadata only |\n| `VARCHAR(n)` → `TEXT` | No | Metadata only |\n| `NUMERIC(p,s)` → `NUMERIC(p2,s)` where p2 > p (same scale) | No | Metadata only |\n| `INTEGER` → `BIGINT` | **Yes** | Full rewrite |\n| `TIMESTAMP` → `TIMESTAMPTZ` | **Yes** | Full rewrite |\n\n### Add a NOT NULL Constraint\n\n```sql\n-- PG 18+: simplified two-step pattern\nALTER TABLE orders ALTER COLUMN order_status SET NOT NULL NOT VALID;\nALTER TABLE orders VALIDATE NOT NULL ON order_status;\n\n-- PG 12–17: fast if a valid CHECK constraint already exists\n-- Step 1: add CHECK (non-blocking scan)\nALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (order_status IS NOT NULL) NOT VALID;\nALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;\n\n-- Step 2: add NOT NULL (PG12+ recognizes the CHECK and skips the scan)\nALTER TABLE orders ALTER COLUMN order_status SET NOT NULL;\n\n-- Step 3: drop the now-redundant CHECK\nALTER TABLE orders DROP CONSTRAINT orders_status_nn;\n\n-- PG < 12: SET NOT NULL always scans the full table.\n-- Ensure no NULLs exist first, then accept the brief lock.\n```\n\n### Add a Foreign Key\n\n```sql\n-- UNSAFE: validates all existing rows while holding a heavy lock\n-- DON'T: ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);\n\n-- SAFE: two-step approach\n-- Step 1: add without validation (blocks writes briefly, doesn't scan data)\nALTER TABLE orders ADD CONSTRAINT fk_user\n    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;\n\n-- Step 2: validate existing rows (allows concurrent reads and writes)\nALTER TABLE orders VALIDATE CONSTRAINT fk_user;\n```\n\n### Add an Index\n\n```sql\n-- UNSAFE on large tables: blocks all writes for the entire build\n-- DON'T: CREATE INDEX idx_orders_user ON orders (user_id);\n\n-- SAFE: concurrent index creation (allows reads and writes)\nCREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);\n\n-- IMPORTANT: if concurrent index creation fails (crashes, deadlock),\n-- it leaves an INVALID index behind. Check and clean up:\nSELECT indexrelname, idx_scan\nFROM pg_stat_user_indexes\nWHERE schemaname = 'public'\n  AND indexrelname = 'idx_orders_user';\n\n-- Check for invalid indexes\nSELECT indexrelid::regclass AS index_name, indisvalid\nFROM pg_index\nWHERE NOT indisvalid;\n\n-- Drop and retry if invalid\nDROP INDEX CONCURRENTLY idx_orders_user;\nCREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);\n```\n\n### Add a Unique Constraint\n\n```sql\n-- A UNIQUE constraint creates an index. Use CONCURRENTLY to avoid blocking:\n\n-- Step 1: create a unique index concurrently\nCREATE UNIQUE INDEX CONCURRENTLY idx_orders_tracking_uniq ON orders (tracking_number);\n\n-- Step 2: attach it as a constraint (instant)\nALTER TABLE orders ADD CONSTRAINT orders_tracking_uniq UNIQUE USING INDEX idx_orders_tracking_uniq;\n```\n\n### Redefine a Primary Key\n\nRedefining a PK (e.g., switching from `id` to a composite key, or from `int` to `bigint`) requires both a UNIQUE constraint and NOT NULL — both of which can cause long-lasting locks if done naively. The zero-downtime approach builds each ingredient separately:\n\n```sql\n-- Step 1: add CHECK NOT NULL constraint without validation (brief lock)\nALTER TABLE orders ADD CONSTRAINT orders_new_id_nn\n    CHECK (new_id IS NOT NULL) NOT VALID;\n\n-- Step 2: validate existing rows (allows concurrent reads and writes)\nALTER TABLE orders VALIDATE CONSTRAINT orders_new_id_nn;\n\n-- Step 3: build unique index concurrently (non-blocking)\nCREATE UNIQUE INDEX CONCURRENTLY idx_orders_new_pkey\n    ON orders (new_id);\n\n-- Step 4: drop the old PK\nALTER TABLE orders DROP CONSTRAINT orders_pkey;\n\n-- Step 5: add new PK using the existing index (instant — also implicitly adds NOT NULL)\nALTER TABLE orders ADD CONSTRAINT orders_pkey\n    PRIMARY KEY USING INDEX idx_orders_new_pkey;\n\n-- Step 6: drop the now-redundant CHECK constraint\nALTER TABLE orders DROP CONSTRAINT orders_new_id_nn;\n```\n\n**Why this works:** Step 5 is fast because Postgres reuses the already-built unique index and recognizes the existing CHECK constraint, skipping both the index build and the full-table NOT NULL scan (PG12+).\n\n### Drop a Constraint\n\n```sql\n-- SAFE: instant metadata change\nALTER TABLE orders DROP CONSTRAINT orders_tracking_uniq;\n\n-- If dropping a FK that has a supporting index you no longer need:\nALTER TABLE orders DROP CONSTRAINT fk_user;\nDROP INDEX idx_orders_user_id;  -- only if no other queries use it\n```\n\n## Backfill Strategies\n\nAlways backfill in batches — never in a single UPDATE. See [backfill-strategies](references/backfill-strategies.md) for batch-by-PK patterns, resumable progress tracking, and tuning guidance.\n\n## Migration Validation\n\nRun validation queries before and after every migration. See [validation-queries](references/validation-queries.md) for the full set of checks: NULL detection, duplicate detection, orphan rows, cast failures, duration estimation, schema verification, data integrity, and query performance.\n\n## Rollback Planning\n\nEvery migration should have a rollback plan documented before execution.\n\n### Reversible Operations\n\n| Operation | Rollback |\n|-----------|----------|\n| `ADD COLUMN` | `DROP COLUMN` |\n| `ADD CONSTRAINT` | `DROP CONSTRAINT` |\n| `CREATE INDEX` | `DROP INDEX` |\n| `RENAME COLUMN x TO y` | `RENAME COLUMN y TO x` |\n| `SET DEFAULT x` | `SET DEFAULT old_value` or `DROP DEFAULT` |\n| `ADD COLUMN new + DROP COLUMN old` | Cannot directly undo — need to re-add old column and backfill from a backup |\n\n### Irreversible Operations\n\nThese require restoring from a backup or the database fork to undo:\n\n- **`DROP COLUMN`** — data is gone once VACUUM reclaims it\n- **`ALTER COLUMN TYPE`** with lossy cast (e.g., `NUMERIC` → `INTEGER`, `TEXT` → `VARCHAR(50)`)\n- **`DELETE` / `TRUNCATE`** during data cleanup\n- **`DROP TABLE`**\n\n**This is where a database fork is invaluable.** If you forked before the migration, the original database has the pre-migration state. If the migration went wrong, your production data is untouched — just delete the fork and start over.\n\n## Transaction Strategy\n\nThere are two approaches for executing multiple DDL statements. Each has tradeoffs:\n\n**Wrapped in one transaction** — all changes succeed or all roll back. Use this when atomicity matters more than lock duration, and all operations are fast (milliseconds).\n\n```sql\nBEGIN;\n\nALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;\nALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';\nCREATE INDEX ON orders USING GIN (tags);\nALTER TABLE orders DROP COLUMN old_priority;\n\n-- Verify before committing\nSELECT column_name, data_type\nFROM information_schema.columns\nWHERE table_name = 'orders'\nORDER BY ordinal_position;\n\nCOMMIT;\n-- Or ROLLBACK; if something looks wrong\n```\n\n**Separate transactions** — each DDL runs and commits independently. Use this when lock duration matters more than atomicity. In a single transaction, all locks are held until `COMMIT` — so if you have 5 DDL statements, the `AccessExclusiveLock` from the first one blocks traffic for the entire duration of all 5. Separate transactions release locks between statements.\n\n```sql\n-- Each statement auto-commits\nALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;\nALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';\nALTER TABLE orders DROP COLUMN old_priority;\n```\n\n**The tradeoff:** separate transactions can leave the schema in a partially migrated state if a later statement fails. You'll need a rollback plan for each step individually.\n\n**Cannot use transactions with:**\n- `CREATE INDEX CONCURRENTLY` (explicitly disallowed inside a transaction)\n- `DROP INDEX CONCURRENTLY`\n- Any statement that requires its own transaction context\n\n## Dealing with Long-Running Queries\n\nA fast `ALTER TABLE` can still hang if it's waiting to acquire `AccessExclusiveLock` behind a long-running query. Worse, the waiting DDL blocks all subsequent queries too — even simple SELECTs pile up behind it:\n\n```\nSession 1: SELECT COUNT(*) FROM orders;          -- long query, holds AccessShareLock\nSession 2: ALTER TABLE orders ADD COLUMN ...;     -- waits for Session 1 (needs AccessExclusiveLock)\nSession 3: SELECT * FROM orders WHERE id = 123;   -- BLOCKED by Session 2's lock queue entry\nSession 4: INSERT INTO orders (...) VALUES (...);  -- also BLOCKED\n-- All sessions freeze until Session 1 finishes and Session 2 completes or times out\n```\n\nThis is why `lock_timeout` is critical — without it, a single slow query can cascade into an application-wide outage.\n\n### Set Timeouts\n\n**`lock_timeout`** — How long to wait for a lock before giving up. Use this on every production DDL statement. Without it, an `ALTER TABLE` can queue behind a long-running query and block all subsequent queries behind it indefinitely.\n\n**`statement_timeout`** — How long the statement can run once it has the lock. This is a safety net against unexpectedly slow operations (e.g., a type change that triggers a table rewrite you didn't anticipate). The tradeoff: if the timeout fires mid-operation, the entire statement rolls back — which is safe for DDL (no partial changes), but means a long `CREATE INDEX CONCURRENTLY` could be killed near completion. For that reason, avoid setting `statement_timeout` on operations you know will be slow (like concurrent index builds on large tables) and instead monitor them manually.\n\n**Choosing timeout values:**\n\nThere are two schools of thought:\n\n- **Conservative (50-100ms lock_timeout, hundreds of retries):** Minimizes the window where a waiting DDL blocks other queries. Each attempt is nearly invisible to application traffic, but requires retry logic. Best for high-traffic OLTP systems where even a few seconds of blocked queries is unacceptable.\n- **Pragmatic (3-5s lock_timeout, few retries):** Gives the lock a reasonable chance to be acquired on each attempt, reducing the need for complex retry logic. Acceptable for most applications where brief pauses are tolerable.\n\nPick based on your traffic profile: the higher your query throughput, the shorter your `lock_timeout` should be — because even a brief queue-up affects more queries per second. For `statement_timeout`, set it to a generous multiple of what you expect the operation to take (e.g., 30s for metadata-only changes, minutes for VALIDATE CONSTRAINT on large tables, disabled for CREATE INDEX CONCURRENTLY).\n\n```sql\n-- Fail fast instead of blocking all queries behind you\nSET lock_timeout = '5s';\nSET statement_timeout = '30s';\n\nALTER TABLE orders ADD COLUMN tracking_number TEXT;\n\n-- If it fails with \"canceling statement due to lock timeout\":\n-- 1. Find what's blocking\nSELECT pid, state, query, now() - query_start AS duration\nFROM pg_stat_activity\nWHERE state != 'idle'\nORDER BY duration DESC;\n\n-- 2. Wait for the blocker to finish, or cancel it if appropriate\n-- SELECT pg_cancel_backend(<pid>);\n\n-- 3. Retry the ALTER TABLE\nSET lock_timeout = '5s';\nALTER TABLE orders ADD COLUMN tracking_number TEXT;\n\n-- Reset timeouts when done\nRESET lock_timeout;\nRESET statement_timeout;\n```\n\n### The Retry-With-Timeout Pattern\n\nFor automated migration runners, wrap DDL in a retry loop with a short lock timeout:\n\n```sql\nDO $$\nDECLARE\n    max_attempts INTEGER := 5;\n    attempt INTEGER := 1;\n    success BOOLEAN := FALSE;\nBEGIN\n    WHILE attempt <= max_attempts AND NOT success LOOP\n        BEGIN\n            SET lock_timeout = '3s';\n            -- Replace with your DDL statement\n            ALTER TABLE orders ADD COLUMN tracking_number TEXT;\n            success := TRUE;\n            RAISE NOTICE 'DDL succeeded on attempt %', attempt;\n        EXCEPTION\n            WHEN lock_not_available THEN\n                RAISE NOTICE 'Attempt % failed (lock not available), retrying...', attempt;\n                PERFORM pg_sleep(2 * attempt);  -- linear backoff\n                attempt := attempt + 1;\n        END;\n    END LOOP;\n\n    IF NOT success THEN\n        RAISE EXCEPTION 'DDL failed after % attempts', max_attempts;\n    END IF;\nEND $$;\n```\n\nThis prevents the migration from creating a pile-up of blocked queries behind it. Each attempt either succeeds quickly or gives up and lets normal traffic flow.\n\n**Alternative: `NOWAIT`** — For the highest-traffic systems, use `LOCK TABLE ... NOWAIT` to test lock availability before running DDL. Unlike `lock_timeout`, `NOWAIT` fails instantly without ever entering the lock queue, so there is zero risk of cascading blocked queries. The tradeoff is more retries:\n\n```sql\nBEGIN;\nLOCK TABLE orders IN ACCESS EXCLUSIVE MODE NOWAIT;\n-- If we get here, we have the lock — run DDL\nALTER TABLE orders ADD COLUMN tracking_number TEXT;\nCOMMIT;\n-- If LOCK fails with \"could not obtain lock\", retry after a short sleep\n```\n\n## Fork-Based Migration Testing\n\nThe safest way to test a migration is to run it against a copy of your actual database — same schema, same data, same edge cases. The only two providers that support fast database forking are [Neon](https://neon.tech) and [Ghost](https://ghost.build). Without database forking, you need to manually dump and restore your database, which can take a long time for large datasets.\n\n### With Forking\n\n1. **Fork your database** — create a full copy using your provider's fork feature (takes seconds)\n2. **Inspect the current schema** on the fork to confirm it matches production\n3. **Run your migration** on the fork\n4. **Validate** — run your checks (see Pre/Post-Migration Validation sections above)\n5. **If it worked:** apply the same migration to production\n6. **If it failed:** delete the fork — your production database is untouched\n\nThis catches problems that never show up in empty test databases:\n- Data that violates a new constraint\n- Type casts that fail on real values\n- Migrations that are fast on 100 rows but lock the table for minutes on 10 million\n- Index creation that runs out of memory or disk space\n\n**Limitation:** fork-based testing runs your migration in isolation — it won't catch issues caused by concurrent database traffic (e.g., lock contention under load, deadlocks with concurrent writes, or replication lag from heavy WAL generation). For most applications, fork-based testing is sufficient. For very high-uptime applications, use [PgDog](https://pgdog.dev)'s mirroring feature to replay production traffic against the fork — it reproduces queries byte-for-byte with realistic timing, and you can filter to DDL-only or DML-only and control exposure percentage to ramp up gradually.\n\n### Without Forking\n\nCreate a test database from a backup or dump:\n\n```bash\n# Dump your production database\npg_dump -Fc my_app_db > backup.dump\n\n# Restore into a test database\ncreatedb migration_test\npg_restore -d migration_test backup.dump\n\n# Or clone from a live database (requires downtime on source during copy)\ncreatedb migration_test -T my_app_db\n```\n\n## Complete Migration Example\n\nFor a full end-to-end walkthrough (plan, fork, run, validate, apply, clean up), see [complete-example](references/complete-example.md).\n\n## Advanced Considerations\n\n**Subtransactions in PL/pgSQL retry loops:** The `BEGIN/EXCEPTION WHEN/END` block in the retry-with-timeout pattern creates implicit subtransactions. Under high write throughput, this can trigger SubtransSLRU contention on replicas — especially if the retry loop runs as a long-lived transaction with many attempts. If you see replica lag during retries, move the retry logic to the application layer (separate transactions per attempt) instead of using PL/pgSQL exception handling.\n\n**Autovacuum can block VALIDATE CONSTRAINT:** `VALIDATE CONSTRAINT` acquires `ShareUpdateExclusiveLock`, which conflicts with autovacuum running in transaction ID wraparound prevention mode. If `VALIDATE` hangs unexpectedly, check `pg_stat_activity` for autovacuum processes on the same table. You may need to wait for wraparound-prevention autovacuum to finish — do not cancel it, as that can lead to data loss if the table approaches the XID wraparound limit.\n\n## Common Pitfalls\n\n1. **Testing migrations on empty tables** — a migration that runs in 1ms on an empty table can lock a 10M-row table for minutes. Always test against realistic data volumes.\n2. **Forgetting `CONCURRENTLY` on index creation** — `CREATE INDEX` (without `CONCURRENTLY`) blocks all writes. On a table with active traffic, this causes downtime.\n3. **Adding NOT NULL without the two-step pattern** — on large tables in PG < 12, `SET NOT NULL` scans every row while holding `AccessExclusiveLock`. Use the CHECK constraint pattern.\n4. **No lock timeout** — a fast ALTER TABLE can block behind a long-running query, and every subsequent query stacks up behind it. Always `SET lock_timeout` for production DDL.\n5. **Backfilling in one big transaction** — a single `UPDATE orders SET x = y` on 10M rows generates enormous WAL, bloats the table, and holds locks for the entire duration. Always batch.\n6. **Leaving invalid indexes behind** — if `CREATE INDEX CONCURRENTLY` fails, it leaves an invisible invalid index that consumes space and slows writes. Check `pg_index.indisvalid` after every concurrent index operation.\n7. **Dropping columns before updating application code** — in a running system, the old code still references the column. Deploy the code change first, then drop the column in a subsequent migration.\n8. **Not checking replication lag** — large backfills generate heavy WAL. If you have read replicas, monitor `pg_stat_replication` during and after the migration.\n9. **Assuming ALTER COLUMN TYPE is safe** — most type changes rewrite the entire table. Use the add-new-column + backfill + swap pattern for large tables.","author":"@timescale","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/postgres-database-migration","license":"Apache-2.0","category":"testing","lang":"en","tokens":5406,"stars":0,"calls30d":2,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[{"path":"references/backfill-strategies.md","size":2389,"sha256":"67f337a606288a6d90408493c712bb17207dba44ce8c808c0e621ef850cd7376"},{"path":"references/complete-example.md","size":2098,"sha256":"e237ef18624dcc2e7bc4bf20167f8908142f2d962ad5f2829ef1116951d43a3f"},{"path":"references/validation-queries.md","size":3527,"sha256":"19c7d3eed6c369ff8fe141bca4c36313df2005fdb391b182790c30125bca1c3b"}],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":["ghost.build","neon.tech","pgdog.dev"]}}