Zero-Downtime Postgres Migrations: The Expand-and-Contract Playbook

Most migration outages I've seen weren't caused by a bad idea. They were caused by a perfectly reasonable ALTER TABLE that grabbed a lock at 2 PM on a Tuesday and sat there while every request queued up behind it.

Postgres is very good at schema changes. It's also very honest: if a statement needs an ACCESS EXCLUSIVE lock, it will wait for it, and everything else will wait for it. This post is the playbook I use to avoid that.

The core problem: lock queues

Here's the thing people miss. Your ALTER TABLE might only need its lock for a few milliseconds. But if a long-running analytics query is holding a weaker lock on the same table, your migration waits. And while it waits, every new SELECT on that table lines up behind the migration. One slow report plus one innocent migration equals a full outage.

The fix is boring and effective. Set a lock timeout in every migration:

SET lock_timeout = '3s';
SET statement_timeout = '15min';

ALTER TABLE orders ADD COLUMN currency text;

If the lock can't be taken in three seconds, the migration fails fast, the queue drains, and you retry. A failed migration is annoying. A locked orders table is an incident.

Expand, migrate, contract

The pattern itself is simple. You never make a breaking change in one step. You split it into phases that each work with both the old and new versions of your app code.

Say we want to rename orders.amount (an integer in cents) to orders.amount_cents.

Phase 1, expand. Add the new column. Nullable, no default that needs a rewrite.

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN amount_cents bigint;

Since Postgres 11, adding a column with a constant default is also metadata-only, so DEFAULT 0 is fine. A volatile default like now() or gen_random_uuid() still rewrites the table, so avoid those on big tables.

Phase 2, dual write. Deploy app code that writes to both columns. A trigger works too if you'd rather keep it in the database:

CREATE OR REPLACE FUNCTION orders_sync_amount() RETURNS trigger AS $$
BEGIN
  NEW.amount_cents := COALESCE(NEW.amount_cents, NEW.amount);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_sync_amount_trg
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_sync_amount();

Phase 3, backfill. Copy old rows in small batches. Never one giant UPDATE; that's one huge transaction, a pile of dead tuples, and replication lag you'll feel.

import time
import psycopg

BATCH = 5_000

with psycopg.connect("postgresql://app@db/app", autocommit=True) as conn:
    while True:
        cur = conn.execute(
            """
            UPDATE orders SET amount_cents = amount
            WHERE id IN (
                SELECT id FROM orders
                WHERE amount_cents IS NULL
                ORDER BY id
                LIMIT %s
                FOR UPDATE SKIP LOCKED
            )
            """,
            (BATCH,),
        )
        if cur.rowcount == 0:
            break
        print(f"backfilled {cur.rowcount}")
        time.sleep(0.2)  # give replicas and autovacuum room to breathe

autocommit=True matters here. Each batch commits on its own, so locks are short and progress survives a crash.

Phase 4, switch reads. Deploy code that reads from amount_cents. Keep writing both for a release or two in case you need to roll back.

Phase 5, contract. Once nothing reads the old column, drop the trigger and the column.

SET lock_timeout = '3s';
DROP TRIGGER orders_sync_amount_trg ON orders;
DROP FUNCTION orders_sync_amount();
ALTER TABLE orders DROP COLUMN amount;

Yes, it's five steps instead of one. That's the price of never having to schedule a maintenance window.

Indexes: always concurrently

A plain CREATE INDEX blocks writes for the whole build. On a big table that can be minutes.

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

Two gotchas:

• It can't run inside a transaction block. Many migration tools wrap everything in a transaction by default, so you'll need to turn that off for this migration (Alembic, Rails, Django and Flyway all have a way).
• If it fails halfway, it leaves an INVALID index behind. Check and clean up:

SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_id;

NOT NULL and foreign keys without the scan lock

Adding NOT NULL directly makes Postgres scan the whole table under an exclusive lock. The trick is to add a CHECK constraint as NOT VALID first, validate it separately (which takes a weaker lock), and then let Postgres 12+ use it to skip the scan:

SET lock_timeout = '3s';
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_cents_not_null
  CHECK (amount_cents IS NOT NULL) NOT VALID;

-- later, can run for a while but doesn't block reads or writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_cents_not_null;

-- now fast: Postgres sees the validated check and skips the scan
ALTER TABLE orders ALTER COLUMN amount_cents SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_amount_cents_not_null;

Foreign keys work the same way:

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Things that quietly rewrite the table

Keep a short list of statements that need extra care:

Widening varchar(50) to varchar(100) or to text is metadata-only, so that one's safe.

Make it a team habit

At ByteBridge Talent LLC, the first thing we check in any migration review is a missing lock_timeout, because that one line prevents most of the 2 PM surprises.

A few more habits that pay off:

• Run migrations as a separate deploy step, not on app boot. Ten pods racing to run the same migration is its own kind of fun.
• Test against a production-sized copy. A migration that takes 40 ms on your laptop can take 40 minutes on 200 million rows.
• Watch pg_stat_activity while it runs:

SELECT pid, wait_event_type, state, now() - query_start AS running_for, query
FROM pg_stat_activity
WHERE datname = current_database() AND state <> 'idle'
ORDER BY running_for DESC;

• Write the rollback plan before the migration, not after.

Wrapping up

Zero-downtime migrations aren't about clever SQL. They're about splitting one risky change into several boring ones, giving every lock an exit, and doing heavy lifting in small batches. Once the team gets used to expand-and-contract, schema changes stop being events and go back to being Tuesday.