Zero Downtime Postgres Migrations in 2024

TL;DR — Zero-downtime schema changes use expand-contract with three deploys. Know which DDL statements take which locks. Backfill in batches with statement_timeout. Postgres 17 made ALTER TABLE for NOT NULL checks online for new constraints.

The bar for “zero downtime” in 2024 isn’t “no errors during the deploy”. It’s “no spike in latency above SLO, no failed requests, no rollback needed”. Schema migrations are the hardest place to clear that bar, because Postgres DDL is mostly synchronous and takes table-level locks.

The good news is that the patterns are well-established. Expand-contract, lock-aware DDL, batched backfills with timeouts, and a few specific Postgres features (concurrent indexes, attached partitions, online column adds) cover 95% of cases. The bad news is that you have to be disciplined about applying them — one short-lived ALTER TABLE on the wrong table at the wrong time can drop production.

This post is the migration patterns I follow and the operational discipline around them. Postgres 17 added a few useful improvements but the core technique is unchanged from 14.

The expand-contract pattern

Every irreversible schema change is really three deployments.

1. Expand. Add the new structure alongside the old. Both work. App writes to both, reads from old.
2. Migrate. Backfill data into the new structure. Switch reads to new. Verify.
3. Contract. Stop writing to old. Drop the old structure.

The discipline is that each step is independently safe and reversible. If the expand deploy goes wrong, roll it back without touching data. If the migrate step has issues, you keep reading from the old side until you fix them.

Consider renaming email to primary_email:

-- expand
ALTER TABLE users ADD COLUMN primary_email text;
-- app deploy: dual-write to email and primary_email, read from email

-- migrate
UPDATE users SET primary_email = email WHERE primary_email IS NULL;
-- in batches, see backfill section
-- app deploy: read from primary_email, dual-write continues

-- contract (later, after confidence)
-- app deploy: stop writing to email
ALTER TABLE users DROP COLUMN email;

Three separate deploys. Each is online. Nothing locks for more than milliseconds. The whole thing might take a week.

Lock types you need to know about

Postgres has eight table lock levels. For DDL, three matter:

• ACCESS EXCLUSIVE: blocks everything, including reads. Held by ALTER TABLE for most operations, DROP TABLE, TRUNCATE, REINDEX (non-concurrent), VACUUM FULL.
• SHARE UPDATE EXCLUSIVE: blocks DDL and VACUUM but not reads or writes. Held by CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, autovacuum.
• ROW EXCLUSIVE: held by INSERT/UPDATE/DELETE. Doesn’t conflict with itself, conflicts with ACCESS EXCLUSIVE.

The rule: avoid ACCESS EXCLUSIVE on live tables. If you must take it, hold it for milliseconds, not seconds.

DDL that’s surprisingly cheap in 17:

• ALTER TABLE ... ADD COLUMN ... [DEFAULT constant]: instant, the default is logical metadata not a rewrite. Default for NULL works since 11.
• ALTER TABLE ... DROP COLUMN: instant, the column is hidden not removed (cleanup at next rewrite).
• ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) NOT VALID: instant, doesn’t scan existing rows. Follow with ALTER TABLE ... VALIDATE CONSTRAINT which uses a weaker lock.
• ALTER TABLE ... ATTACH PARTITION: brief lock, validates constraint.

DDL that’s expensive:

• ALTER TABLE ... ADD COLUMN ... [non-constant default]: rewrites the table. AccessExclusive for the duration. Avoid.
• ALTER TABLE ... ALTER COLUMN ... TYPE (most type changes): rewrites the table. Avoid.
• ALTER TABLE ... ADD COLUMN ... NOT NULL without default: scans the table. Long lock. Avoid.

Always set a short lock_timeout when running DDL against a live table:

SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN primary_email text;
RESET lock_timeout;

If acquiring the lock takes more than 2 seconds, the statement errors. Better to error and retry than to queue up behind a long-running query and block every read until the lock is acquired. The explicit locking docs cover the lock matrix.

Adding columns the right way

The most common migration. Done correctly it’s instant:

SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN signup_source text;  -- nullable, no default: instant
ALTER TABLE users ADD COLUMN is_verified boolean DEFAULT false NOT NULL;  -- also instant in 17

The second statement was a table rewrite in 10 and earlier. Since 11 it’s metadata-only because the default is constant. The NOT NULL constraint check is also fast because Postgres knows the default satisfies it.

Where it gets expensive: a non-constant default like DEFAULT now() or DEFAULT gen_random_uuid(). Each row needs its own value, so the table rewrites. Don’t do this on a live table. Instead:

-- expand
ALTER TABLE users ADD COLUMN created_at timestamptz;  -- instant
-- app: set created_at = now() on inserts going forward

-- backfill in batches
-- (see next section)

-- contract: enforce NOT NULL after backfill
ALTER TABLE users ALTER COLUMN created_at SET NOT NULL;  -- requires full scan in <17
-- or in 17, use the new check pattern:
ALTER TABLE users ADD CONSTRAINT users_created_at_nn CHECK (created_at IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_created_at_nn;
ALTER TABLE users ALTER COLUMN created_at SET NOT NULL;  -- now fast, uses the constraint

The CHECK-then-NOT-NULL trick works in 12+ but Postgres 17 also picked up the optimization that recognizes existing constraints, so a fresh ALTER ... SET NOT NULL is faster when a matching CHECK (col IS NOT NULL) already exists.

Backfills that don’t melt the database

Never run UPDATE users SET primary_email = email. On 50M rows that’s an hour of WAL, autovacuum chaos, and replication lag.

Batch it:

DO $$
DECLARE
  batch_size int := 5000;
  rows_updated int := 1;
BEGIN
  WHILE rows_updated > 0 LOOP
    WITH batch AS (
      SELECT id FROM users
      WHERE primary_email IS NULL
      ORDER BY id
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    )
    UPDATE users SET primary_email = email
    FROM batch WHERE users.id = batch.id;
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    PERFORM pg_sleep(0.05);
    COMMIT;
  END LOOP;
END $$;

FOR UPDATE SKIP LOCKED makes the batch safely concurrent with other writes. pg_sleep gives autovacuum air. Each batch is its own transaction.

In practice I run this from a Python or Go script with retry logic, idempotency on the WHERE clause, and visible progress reporting. Doing 50M rows takes a few hours at 5k/batch with 50 ms sleep, and the database doesn’t notice.

For very large backfills, partition the work by primary key range and run multiple workers in parallel — but watch concurrent autovacuum and replication lag.

Indexes are special

CREATE INDEX takes ACCESS EXCLUSIVE. CREATE INDEX CONCURRENTLY doesn’t:

CREATE INDEX CONCURRENTLY users_primary_email_idx ON users (primary_email);

Concurrent index creation is slower (two passes) but online. There’s a known failure mode: if the build fails (constraint violation, OOM, cancellation), it leaves an INVALID index that the planner ignores. Check for these regularly:

SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index WHERE NOT indisvalid OR NOT indisready;

Drop INVALID indexes and retry, or use REINDEX INDEX CONCURRENTLY to repair.

Foreign keys also take expensive locks. The pattern is ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT:

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

NOT VALID is instant. VALIDATE takes a weaker lock that allows reads and writes. Same trick works for CHECK constraints.

Coordination with the app

This is where most teams trip. The schema and the application code change together, but they deploy at different times.

Three rules:

1. Forward compatible reads. The old app must tolerate new columns it doesn’t know about. Use explicit column lists in SELECT, never SELECT *.
2. Backward compatible writes. The new app must work against the old schema during a rolling deploy. If the new code requires primary_email, you can’t deploy the new code until after the column exists.
3. Dual-write during transitions. During the migrate phase, the app writes to both old and new columns. Removing dual-write is the contract step.

For broader migration context including blue-green clusters as an alternative pattern, see logical replication for blue green Postgres deploys.

Tooling

I use a few tools so I’m not hand-writing all of this.

• pgroll: turns a single declarative migration into the expand-contract DDL, runs them, tracks state. Open source, works on 14+.
• sqlmigrate / dbmate: traditional migration runners. Fine but don’t help with expand-contract.
• pg_locks queries: I keep a Grafana panel of “longest-held locks” so I see the moment a migration causes contention.

SELECT pid, relation::regclass, mode, granted, query_start, query
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE NOT granted
ORDER BY query_start;

That query shows any backend blocked waiting on a lock, with the query they’re trying to run. Essential during migrations.

Common Pitfalls

The mistakes that cost the most.

• No lock_timeout. A DDL that waits 30 seconds for a lock blocks every read for 30 seconds. Always set lock_timeout = '2s' for live DDL.
• SELECT * in the app. Locks you out of additive schema changes. Always select explicit columns.
• Forgetting to validate NOT VALID constraints. They don’t enforce until validated. Easy to leave half-done.
• ALTER TABLE ... ALTER COLUMN TYPE on a live table. Almost always rewrites. Add a new column, dual-write, swap.
• Backfills without statement_timeout. A misbehaving batch can lock for hours. Cap it.
• No rollback plan for the expand. If the new schema breaks production, can you revert without losing data? Plan it before deploying.
• Migrations during peak traffic. Even a fast online migration costs some CPU and WAL. Schedule for low traffic.

The single most common mistake is treating migrations as a single deploy. Three deploys, with bake time between, is the discipline that distinguishes “we have schema changes” from “we have outages”.

Wrapping Up

Zero downtime Postgres migrations are a solved problem in 2024, but the solution is operational, not technical. The Postgres features have been there since 11 or 12; what changes is the discipline. Expand-contract, lock-aware DDL, batched backfills, dual-write windows.

Build the muscle once. The patterns generalize across every schema change you’ll ever make. And once your migrations stop being a source of incidents, every other reliability improvement gets easier.