Zero-downtime PostgreSQL migrations: the expand-and-contract playbook

Most PostgreSQL outages we get called in for are not caused by hardware or traffic. They are caused by a migration: a column rename that broke the previous release still serving traffic, an index build that locked a hot table, or an ALTER TABLE that sat in the lock queue and took every query behind it down with it.

The fix is not a better migration tool. It is a discipline called expand and contract (sometimes called parallel change). You never make a breaking schema change in one step. You add the new shape alongside the old one, move the application over, and only then remove the old shape.

This post is the playbook we use with clients running PostgreSQL 14 to 18 under continuous deployment. It covers the sequence of steps, the lock each operation takes, the SQL we actually run, and the traps that catch experienced teams.

Why a single-step migration breaks

During a rolling deploy, old and new versions of your application run at the same time, for anywhere from 30 seconds to 30 minutes. If the migration runs before the deploy, the old code sees the new schema. If it runs after, the new code sees the old schema. Either way, one version is talking to a schema it does not understand.

Consider renaming orders.customer_ref to orders.customer_id. A one-step ALTER TABLE orders RENAME COLUMN is instant for PostgreSQL, but every old application pod still issuing SELECT customer_ref starts failing immediately. Rollback is equally broken: the previous release cannot run against the renamed column.

Expand and contract makes every step backward compatible with the release before it, so you can deploy and roll back at any point.

The five phases

For our rename example, the full sequence looks like this.

Each phase is its own deploy. It feels slow, but each step is small and boring, and boring is the goal. On a typical team this takes two to five days of calendar time, most of it waiting to be confident the previous step is healthy.

Locks: the thing that actually takes you down

Almost every ALTER TABLE takes an ACCESS EXCLUSIVE lock, even if only for milliseconds. The danger is not the lock itself; it is the queue. If a long-running query holds a conflicting lock, your ALTER TABLE waits. While it waits, every new query on that table queues behind it. A 3-second analytics query can turn a 5-millisecond DDL statement into a full outage.

The defence is to always set lock_timeout in migration sessions and retry on failure.

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

ALTER TABLE orders ADD COLUMN customer_id bigint;

If the lock cannot be acquired in three seconds, the statement fails cleanly instead of stalling the table. Your migration runner should retry a few times with a backoff. Most frameworks let you set these per migration; if yours does not, put them at the top of the migration file.

Here are the operations we use most and what they cost.

Phase 1: expand

Add the new column as nullable with no default, or with a constant default. Since PostgreSQL 11, adding a column with a non-volatile default is a metadata-only change, so it is instant regardless of table size.

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

Do not add NOT NULL, foreign keys or indexes yet. Each of those needs its own careful step.

Phase 2: dual write

Deploy application code that writes to both columns on every insert and update, and still reads from the old one. In most ORMs this is a model hook or a small change in the repository layer.

Some teams use a trigger instead, which has the advantage of catching writes from scripts and other services:

CREATE OR REPLACE FUNCTION orders_sync_customer_id() RETURNS trigger AS $$
BEGIN
  NEW.customer_id := NEW.customer_ref;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_sync_customer_id
  BEFORE INSERT OR UPDATE OF customer_ref ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_sync_customer_id();

We prefer application-level dual writes when one service owns the table, and triggers when several writers exist. Either way, remove the mechanism in the contract phase.

Phase 3: backfill in batches

Never backfill with a single UPDATE orders SET customer_id = customer_ref. On a large table that one statement holds row locks on every row, generates a huge amount of WAL, bloats the table, and can put replicas minutes behind.

Batch by primary key, commit each batch, and pause between them:

-- Run repeatedly from a script until it updates 0 rows
WITH batch AS (
  SELECT id FROM orders
  WHERE customer_id IS NULL AND customer_ref IS NOT NULL
  ORDER BY id
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
)
UPDATE orders o
SET customer_id = o.customer_ref
FROM batch
WHERE o.id = batch.id;

Our backfill scripts watch replica lag (pg_stat_replication.replay_lag on the primary) and sleep when it exceeds a threshold, typically five seconds. On a 400-million-row table for a Series C marketplace client, this ran for about nine hours at a steady 12,000 rows a second, with no visible impact on production latency.

Phase 4: constraints, indexes, then switch reads

With the column fully populated, add what the old column had.

Indexes

Always build indexes concurrently. It cannot run inside a transaction block, so make sure your migration tool does not wrap it in one (Rails has disable_ddl_transaction!, Django has atomic = False, and most Node tools have an equivalent).

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id
  ON orders (customer_id);

If a concurrent build fails, it leaves an INVALID index behind. Check pg_index.indisvalid, drop the invalid index, and retry.

NOT NULL without a full-table lock

SET NOT NULL normally scans the whole table under an ACCESS EXCLUSIVE lock. Since PostgreSQL 12, it skips the scan if a valid CHECK (column IS NOT NULL) constraint already exists. So you add the check as NOT VALID (instant), validate it (scans, but does not block reads or writes), then set NOT NULL (instant) and drop the check.

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

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_not_null;

ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_not_null;

On PostgreSQL 18 you can add a NOT NULL constraint as NOT VALID directly and validate it later, which removes the need for the temporary check. The pattern above works on every supported version.

Foreign keys

The same idea applies. ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID takes brief locks on both tables and skips checking existing rows; VALIDATE CONSTRAINT then checks them without blocking writes.

Switch reads

Now deploy code that reads from customer_id and still writes to both columns. Keep writing both so you can roll back to the previous release. Leave it running for at least one full business cycle; for most clients that means a week, which covers month-end jobs and weekly reports that nobody remembers until they fail.

Phase 5: contract

Once nothing reads the old column, deploy code that stops writing it, then drop the trigger (if any) and the column.

Before dropping, verify nothing still uses it. pg_stat_statements is the quickest check:

SELECT calls, query
FROM pg_stat_statements
WHERE query ILIKE '%customer_ref%'
ORDER BY calls DESC;

Reset the statistics after the reads switch (SELECT pg_stat_statements_reset();), wait a few days, and look again. BI tools, cron jobs and data pipelines are the usual stragglers.

Also check whether your ORM selects every column by name. Some frameworks cache the column list at startup, so dropping a column the model still knows about can break inserts until the next restart. Rails, for example, supports ignored_columns for exactly this reason: mark the column ignored one deploy before you drop it.

Tooling

Tools like pgroll automate expand and contract by serving old and new schema versions through views at the same time. They are worth evaluating if you make many breaking changes. For most teams, though, the bigger improvement comes from lint rules in CI: tools such as Squawk flag CREATE INDEX without CONCURRENTLY, missing lock_timeout, and NOT NULL additions that would scan the table. We add one to every client pipeline.

What we'd do differently

• Add migration linting before the first incident, not after. It costs an hour to set up.
• Set lock_timeout as a role default for the migration user (ALTER ROLE migrator SET lock_timeout = '3s') so nobody can forget it.
• Write the contract step as a ticket at the same time as the expand step. Half-finished migrations, with both columns living on for a year, are the most common failure of this approach.
• Test backfills on a restored production snapshot. Row counts and replica behaviour on staging data are rarely representative.

Checklist

1. Every migration is compatible with the previous application release.
2. lock_timeout is set and the runner retries on lock failure.
3. New columns are added nullable or with a constant default.
4. Backfills run in batches with replica-lag throttling.
5. Indexes use CREATE INDEX CONCURRENTLY, outside a transaction.
6. NOT NULL and foreign keys go through NOT VALID then VALIDATE.
7. Reads switch only after the backfill is verified, and dual writes continue for at least one business cycle.
8. The old column is dropped only after pg_stat_statements shows no remaining use.

None of this is clever. It is a set of small, reversible steps that let you change a schema under live traffic without anyone noticing, which is exactly what a migration should be.