postgres-database-migration

# PostgreSQL Database Migrations A 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. ## DDL Lock Reference Every schema change acquires a lock. The critical question is: **does it block reads and writes, and for how long?** ### Fast, Non-Blocking Operations These complete in milliseconds regardless of table size. They only hold a brief `AccessExclusiveLock` for the catalog update, not for data rewriting. | Operation | Lock Level | Notes | |-----------|-----------|-------| | `ADD COLUMN` (nullable, no default) | `AccessExclusiveLock` (brief) | **Fast.** No table rewrite. Metadata-only change. | | `ADD COLUMN ... DEFAULT x` (PG 11+) | `AccessExclusiveLock` (brief) | **Fast.** Non-volatile defaults stored in catalog, not backfilled. | | `DROP COLUMN` | `AccessExclusiveLock` (brief) | **Fast.** Column marked invisible; space reclaimed by VACUUM over time. | | `SET DEFAULT` / `DROP DEFAULT` | `AccessExclusiveLock` (brief) | Metadata change only. Does not touch existing rows. | | `CREATE INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Allows reads and writes during build. Slower than regular index creation. | | `DROP INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Waits for queries using the index to finish, then drops. No table-level exclusive lock. | | `RENAME COLUMN` | `AccessExclusiveLock` (brief) | Metadata change only. Fast. | | `RENAME TABLE` | `AccessExclusiveLock` (brief) | Metadata change only. Fast. | | `ADD CONSTRAINT ... NOT VALID` | `ShareUpdateExclusiveLock` | Adds constraint for new rows only. Does not scan existing data. | | `VALIDATE CONSTRAINT` | `ShareUpdateExclusiveLock` | Scans existing rows but allows concurrent reads and writes. | | `CREATE/DROP TRIGGER` | `ShareRowExclusiveLock` | Brief catalog update. | ### Slow or Blocking Operations These rewrite the table or scan all rows. On large tables, they can lock out all access for seconds to hours. | Operation | Lock Level | Why It's Slow | |-----------|-----------|---------------| | `ADD COLUMN ... DEFAULT x` (volatile, e.g. `now()`, `gen_random_uuid()`) | `AccessExclusiveLock` | Full table rewrite. Every row gets the computed value. | | `ALTER COLUMN TYPE` (most type changes) | `AccessExclusiveLock` | Full table rewrite to convert stored data. | | `SET NOT NULL` (PG < 12, or without existing CHECK) | `AccessExclusiveLock` | Full table scan to verify no NULLs. See safe pattern below. | | `ADD CONSTRAINT ... CHECK/UNIQUE/FK` (validated) | `AccessExclusiveLock` or `ShareRowExclusiveLock` | Scans all rows to verify, blocks writes. | | `CREATE INDEX` (without CONCURRENTLY) | `ShareLock` | Blocks writes for the entire build duration. | | `CLUSTER` | `AccessExclusiveLock` | Rewrites entire table in index order. | | `VACUUM FULL` | `AccessExclusiveLock` | Rewrites table to reclaim space. | **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. ## Safe Migration Patterns ### Add a Column ```sql -- SAFE: nullable column, no default — instant ALTER TABLE orders ADD COLUMN tracking_number TEXT; -- SAFE (PG 11+): column with non-volatile default — instant ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0; -- UNSAFE: column with volatile default — full table rewrite -- DON'T: ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ DEFAULT now(); -- DO: add nullable, then backfill, then set default + NOT NULL ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ; -- Backfill in batches (see Backfill section) ALTER TABLE orders ALTER COLUMN created_at SET DEFAULT now(); ALTER TABLE orders ALTER COLUMN created_at SET NOT NULL; -- only if PG12+ or CHECK exists ``` ### Drop a Column ```sql -- SAFE: instant (column marked invisible, space reclaimed by VACUUM) ALTER TABLE orders DROP COLUMN old_status; ``` **Application coordination:** Ensure your application no longer references the column before dropping it. For zero-downtime deploys, this requires two steps: 1. Deploy code that doesn't read/write the column 2. Then drop the column in a separate migration **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. ### Rename a Column ```sql -- SAFE: instant metadata change ALTER TABLE orders RENAME COLUMN status TO order_status; ``` **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: 1. Add the new column 2. Deploy code that writes to both columns 3. Backfill old rows 4. Deploy code that reads from the new column 5. Drop the old column ### Change a Column Type Most type changes rewrite the entire table. Safe alternatives: ```sql -- UNSAFE: full table rewrite, blocks everything -- DON'T: ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(12,2); -- SAFE: use a new column + backfill ALTER TABLE orders ADD COLUMN amount_new NUMERIC(12,2); -- Backfill in batches (see Backfill section below) UPDATE orders SET amount_new = amount WHERE id BETWEEN 1 AND 10000; -- ... continue in batches ... -- Swap columns ALTER TABLE orders DROP COLUMN amount; ALTER TABLE orders RENAME COLUMN amount_new TO amount; ``` **Exception:** Some casts don't require a rewrite and are fast: | From | To | Rewrite? | |------|----|----------| | `VARCHAR(n)` → `VARCHAR(m)` where m > n | No | Metadata only | | `VARCHAR(n)` → `TEXT` | No | Metadata only | | `NUMERIC(p,s)` → `NUMERIC(p2,s)` where p2 > p (same scale) | No | Metadata only | | `INTEGER` → `BIGINT` | **Yes** | Full rewrite | | `TIMESTAMP` → `TIMESTAMPTZ` | **Yes** | Full rewrite | ### Add a NOT NULL Constraint ```sql -- PG 18+: simplified two-step pattern ALTER TABLE orders ALTER COLUMN order_status SET NOT NULL NOT VALID; ALTER TABLE orders VALIDATE NOT NULL ON order_status; -- PG 12–17: fast if a valid CHECK constraint already exists -- Step 1: add CHECK (non-blocking scan) ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (order_status IS NOT NULL) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn; -- Step 2: add NOT NULL (PG12+ recognizes the CHECK and skips the scan) ALTER TABLE orders ALTER COLUMN order_status SET NOT NULL; -- Step 3: drop the now-redundant CHECK ALTER TABLE orders DROP CONSTRAINT orders_status_nn; -- PG < 12: SET NOT NULL always scans the full table. -- Ensure no NULLs exist first, then accept the brief lock. ``` ### Add a Foreign Key ```sql -- UNSAFE: validates all existing rows while holding a heavy lock -- DON'T: ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id); -- SAFE: two-step approach -- Step 1: add without validation (blocks writes briefly, doesn't scan data) ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID; -- Step 2: validate existing rows (allows concurrent reads and writes) ALTER TABLE orders VALIDATE CONSTRAINT fk_user; ``` ### Add an Index ```sql -- UNSAFE on large tables: blocks all writes for the entire build -- DON'T: CREATE INDEX idx_orders_user ON orders (user_id); -- SAFE: concurrent index creation (allows reads and writes) CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id); -- IMPORTANT: if concurrent index creation fails (crashes, deadlock), -- it leaves an INVALID index behind. Check and clean up: SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE schemaname = 'public' AND indexrelname = 'idx_orders_user'; -- Check for invalid indexes SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE NOT indisvalid; -- Drop and retry if invalid DROP INDEX CONCURRENTLY idx_orders_user; CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id); ``` ### Add a Unique Constraint ```sql -- A UNIQUE constraint creates an index. Use CONCURRENTLY to avoid blocking: -- Step 1: create a unique index concurrently CREATE UNIQUE INDEX CONCURRENTLY idx_orders_tracking_uniq ON orders (tracking_number); -- Step 2: attach it as a constraint (instant) ALTER TABLE orders ADD CONSTRAINT orders_tracking_uniq UNIQUE USING INDEX idx_orders_tracking_uniq; ``` ### Redefine a Primary Key Redefining 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: ```sql -- Step 1: add CHECK NOT NULL constraint without validation (brief lock) ALTER TABLE orders ADD CONSTRAINT orders_new_id_nn CHECK (new_id IS NOT NULL) NOT VALID; -- Step 2: validate existing rows (allows concurrent reads and writes) ALTER TABLE orders VALIDATE CONSTRAINT orders_new_id_nn; -- Step 3: build unique index concurrently (non-blocking) CREATE UNIQUE INDEX CONCURRENTLY idx_orders_new_pkey ON orders (new_id); -- Step 4: drop the old PK ALTER TABLE orders DROP CONSTRAINT orders_pkey; -- Step 5: add new PK using the existing index (instant — also implicitly adds NOT NULL) ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX idx_orders_new_pkey; -- Step 6: drop the now-redundant CHECK constraint ALTER TABLE orders DROP CONSTRAINT orders_new_id_nn; ``` **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+). ### Drop a Constraint ```sql -- SAFE: instant metadata change ALTER TABLE orders DROP CONSTRAINT orders_tracking_uniq; -- If dropping a FK that has a supporting index you no longer need: ALTER TABLE orders DROP CONSTRAINT fk_user; DROP INDEX idx_orders_user_id; -- only if no other queries use it ``` ## Backfill Strategies Always 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. ## Migration Validation Run 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. ## Rollback Planning Every migration should have a rollback plan documented before execution. ### Reversible Operations | Operation | Rollback | |-----------|----------| | `ADD COLUMN` | `DROP COLUMN` | | `ADD CONSTRAINT` | `DROP CONSTRAINT` | | `CREATE INDEX` | `DROP INDEX` | | `RENAME COLUMN x TO y` | `RENAME COLUMN y TO x` | | `SET DEFAULT x` | `SET DEFAULT old_value` or `DROP DEFAULT` | | `ADD COLUMN new + DROP COLUMN old` | Cannot directly undo — need to re-add old column and backfill from a backup | ### Irreversible Operations These require restoring from a backup or the database fork to undo: - **`DROP COLUMN`** — data is gone once VACUUM reclaims it - **`ALTER COLUMN TYPE`** with lossy cast (e.g., `NUMERIC` → `INTEGER`, `TEXT` → `VARCHAR(50)`) - **`DELETE` / `TRUNCATE`** during data cleanup - **`DROP TABLE`** **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. ## Transaction Strategy There are two approaches for executing multiple DDL statements. Each has tradeoffs: **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). ```sql BEGIN; ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0; ALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}'; CREATE INDEX ON orders USING GIN (tags); ALTER TABLE orders DROP COLUMN old_priority; -- Verify before committing SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders' ORDER BY ordinal_position; COMMIT; -- Or ROLLBACK; if something looks wrong ``` **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. ```sql -- Each statement auto-commits ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0; ALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}'; ALTER TABLE orders DROP COLUMN old_priority; ``` **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. **Cannot use transactions with:** - `CREATE INDEX CONCURRENTLY` (explicitly disallowed inside a transaction) - `DROP INDEX CONCURRENTLY` - Any statement that requires its own transaction context ## Dealing with Long-Running Queries A 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: ``` Session 1: SELECT COUNT(*) FROM orders; -- long query, holds AccessShareLock Session 2: ALTER TABLE orders ADD COLUMN ...; -- waits for Session 1 (needs AccessExclusiveLock) Session 3: SELECT * FROM orders WHERE id = 123; -- BLOCKED by Session 2's lock queue entry Session 4: INSERT INTO orders (...) VALUES (...); -- also BLOCKED -- All sessions freeze until Session 1 finishes and Session 2 completes or times out ``` This is why `lock_timeout` is critical — without it, a single slow query can cascade into an application-wide outage. ### Set Timeouts **`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. **`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. **Choosing timeout values:** There are two schools of thought: - **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. - **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. Pick 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). ```sql -- Fail fast instead of blocking all queries behind you SET lock_timeout = '5s'; SET statement_timeout = '30s'; ALTER TABLE orders ADD COLUMN tracking_number TEXT; -- If it fails with "canceling statement due to lock timeout": -- 1. Find what's blocking SELECT pid, state, query, now() - query_start AS duration FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC; -- 2. Wait for the blocker to finish, or cancel it if appropriate -- SELECT pg_cancel_backend(<pid>); -- 3. Retry the ALTER TABLE SET lock_timeout = '5s'; ALTER TABLE orders ADD COLUMN tracking_number TEXT; -- Reset timeouts when done RESET lock_timeout; RESET statement_timeout; ``` ### The Retry-With-Timeout Pattern For automated migration runners, wrap DDL in a retry loop with a short lock timeout: ```sql DO $$ DECLARE max_attempts INTEGER := 5; attempt INTEGER := 1; success BOOLEAN := FALSE; BEGIN WHILE attempt <= max_attempts AND NOT success LOOP BEGIN SET lock_timeout = '3s'; -- Replace with your DDL statement ALTER TABLE orders ADD COLUMN tracking_number TEXT; success := TRUE; RAISE NOTICE 'DDL succeeded on attempt %', attempt; EXCEPTION WHEN lock_not_available THEN RAISE NOTICE 'Attempt % failed (lock not available), retrying...', attempt; PERFORM pg_sleep(2 * attempt); -- linear backoff