Zero-Downtime Postgres Migrations: Expand-Contract in Practice
A single ALTER TABLE can queue behind one long query and stall every write behind it. Expand-contract is how you stop shipping outages with schema changes.
Key takeaways
- Zero-downtime Postgres migrations split every breaking change into expand, migrate and contract deploys, so old code and new schema never meet.
- Add the new column, dual-write, backfill in small batches, switch reads, then drop the old column.
- Use NOT VALID constraints, CREATE INDEX CONCURRENTLY and a short lock_timeout to keep locks brief.
On this page
To change a Postgres schema without downtime, never ship a breaking change in one step. Expand (add the new column or table), migrate (dual-write, backfill in batches, switch reads), then contract (drop the old one) in a later deploy. Add constraints as NOT VALID, build indexes CONCURRENTLY, and set lock_timeout so no migration can freeze traffic.
Verified against
PostgreSQL 18 (docs for 18.6), against the postgresql.org ALTER TABLE, CREATE INDEX, explicit locking and client connection defaults pages. Checked October 2026.
Here is the failure this pattern exists to prevent. Someone renames a column: ALTER TABLE orders RENAME COLUMN amount TO amount_cents. It runs in milliseconds in staging. In production it needs an ACCESS EXCLUSIVE lock, and a long-running report query already holds a lock on the table, so the rename waits in the lock queue. Every write that arrives behind it queues too. Checkout times out until the report finishes. The migration "worked," and it still caused an outage.
The rename itself wasn't the problem. Deploying a schema change and the code that depends on it as one atomic step is the problem, because your old code and new schema have to coexist during a rolling deploy. Expand-contract solves this by never having a moment where old code meets new schema and breaks.
The pattern in one sentence
Expand: add the new thing without removing the old. Migrate: backfill data and switch reads and writes over gradually. Contract: only after nothing uses the old thing, remove it. Each step is independently safe and reversible.
Take that column rename. As one step it's an outage. As expand-contract it's four boring deploys.
Step 1: expand
Add the new column. This is cheap in modern Postgres: the PostgreSQL 18 docs say adding a column with no default, or with a non-volatile default, stores the value in metadata, and "in neither case is a rewrite of the table required."
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN amount_cents bigint;
Deploy application code that writes to both columns but still reads from the old one. Every new row now populates amount_cents; existing rows still have it null.
# app/orders.py: both writes, old read
order.amount = value
order.amount_cents = value # dual write
# reads still use order.amount
Step 2: backfill in batches
Do not run UPDATE orders SET amount_cents = amount. On a large table that's one giant transaction holding row locks and bloating WAL. Batch it, commit between batches, and throttle so you don't saturate I/O.
-- migrations/002_backfill.sql: run repeatedly until zero rows affected
WITH batch AS (
SELECT id FROM orders
WHERE amount_cents IS NULL
LIMIT 5000
FOR UPDATE SKIP LOCKED
)
UPDATE orders o
SET amount_cents = o.amount
FROM batch
WHERE o.id = batch.id;
FOR UPDATE SKIP LOCKED means the backfill steps around rows currently being written by live traffic instead of blocking on them. Drive it from a small script that sleeps a few hundred milliseconds between batches and pauses when replica lag climbs past whatever your read traffic tolerates. Our notes on replication lag and failover cover how to measure that lag. Batch size and sleep are tuning knobs, not constants: start small, watch I/O and lag, then grow.
Step 3: switch reads, add the constraint
Once the backfill shows zero remaining nulls, deploy code that reads from amount_cents. Now add any NOT NULL constraint you need, the safe way. A plain SET NOT NULL scans the whole table while holding an ACCESS EXCLUSIVE lock. Instead add a CHECK constraint as NOT VALID, validate it under a weaker lock, then promote it.
ALTER TABLE orders
ADD CONSTRAINT amount_cents_not_null
CHECK (amount_cents IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT amount_cents_not_null;
ALTER TABLE orders ALTER COLUMN amount_cents SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT amount_cents_not_null;
The NOT VALID clause means Postgres enforces the constraint on new rows immediately but skips scanning existing ones. VALIDATE CONSTRAINT then scans with only a SHARE UPDATE EXCLUSIVE lock, which doesn't block reads or writes. The final SET NOT NULL is fast because, per the ALTER TABLE docs, a valid CHECK constraint that proves no nulls exist lets Postgres skip the table scan. On PostgreSQL 18 you can also add the not-null constraint itself as NOT VALID and validate it later, which removes the helper CHECK.
Step 4: contract
Stop writing to the old column, confirm nothing references it (check your query logs, not your memory), then drop it.
SET lock_timeout = '3s';
ALTER TABLE orders DROP COLUMN amount;
DROP COLUMN does not rewrite the table; the docs describe it as making the column invisible to SQL, so it is quick. It still needs a brief ACCESS EXCLUSIVE lock, which is why the lock_timeout line is there. By now no code touches amount, so dropping it is invisible to the application.
Indexes have the same trap
The other classic lock is CREATE INDEX, which, in the docs' words, "locks out writes (but not reads) on the table until it's done." Use the concurrent variant in production.
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
CONCURRENTLY builds the index without blocking writes, at the cost of two table scans and waiting for existing transactions to finish. It can't run inside a transaction block, and if it fails partway it leaves an INVALID index that is ignored for queries but still adds write overhead. Check \d orders after any failed build, then DROP INDEX and retry (or REINDEX INDEX CONCURRENTLY). Leftover invalid indexes are one source of the bloat we cover in detecting and fixing index bloat.
The lock_timeout safety net
Even with the right approach, set a lock_timeout on migration sessions so a migration that unexpectedly needs a lock fails fast instead of queueing behind a long query and freezing traffic like the rename above.
SET lock_timeout = '3s';
If the migration can't get its lock in 3 seconds, it errors out and you retry, rather than silently stalling every write behind it. That one setting turns the opening scenario from an outage into a failed migration and a retry. Pair it with a statement_timeout on the backfill session so a runaway batch can't hold row locks for minutes.
Where this breaks
Expand-contract assumes you control every writer. A cron job, a BI tool or a second service writing the old column will reintroduce nulls after your backfill, so find all writers before step 1. It also costs calendar time: four deploys instead of one, and dual-write code that someone must remember to delete. Renaming a table, or changing a column type that forces a rewrite, follows the same shape with a new table or column plus a view or trigger to bridge the gap.
Questions people ask
Does adding a column lock a Postgres table?
Briefly. ALTER TABLE ADD COLUMN takes an ACCESS EXCLUSIVE lock, but with no default or a non-volatile default it only updates metadata, so the lock lasts milliseconds. The danger is waiting for that lock behind a long query, which blocks everything queued after it. Set lock_timeout first. A volatile default like clock_timestamp() forces a full table rewrite.
How do I rename a column in Postgres without downtime?
Don't rename in place while old code is running. Add the new column, deploy code that writes both and reads the old one, backfill existing rows in batches, deploy code that reads the new column, stop writing the old one, then drop it. Each step is a separate deploy that both old and new application versions tolerate.
Can CREATE INDEX CONCURRENTLY run inside a migration transaction?
No. Postgres rejects CREATE INDEX CONCURRENTLY inside a transaction block, so migration tools that wrap each file in BEGIN/COMMIT need that wrapping disabled for the index migration. If the build fails, it leaves an INVALID index; drop it and run the build again.
The call we'd make
Never couple a schema change to the code that needs it in a single deploy. Split every migration into expand and contract phases separated by at least one deploy, so old code and new schema never meet. Batch every backfill with SKIP LOCKED, add constraints as NOT VALID then VALIDATE, build indexes CONCURRENTLY, and set lock_timeout on every migration connection. It's more steps and more patience, and it's the difference between a schema change nobody notices and one that pages the on-call.
Sources
Cite this article
Kiril Urbonas (2026). Zero-Downtime Postgres Migrations: Expand-Contract in Practice. DevOpsNess. Updated 2026-10-31. https://www.devopsness.com/blog/zero-downtime-postgres-migrations-expand-contract
[Zero-Downtime Postgres Migrations: Expand-Contract in Practice](https://www.devopsness.com/blog/zero-downtime-postgres-migrations-expand-contract) (Kiril Urbonas, DevOpsNess, updated 2026-10-31)
More from Infrastructure
Explore more articles in this category
Infrastructure Documentation as Code: How One Platform Team Reduced Audit Fire Drills
This infrastructure documentation as code guide shows how a platform team moved runbooks, ownership maps, and architecture decisions into versioned workflows that people actually trusted.
Infrastructure as Code with Ansible
We use Ansible for configuration management on hosts where Terraform stops. The workflow that keeps it tractable and what we wish we'd known about idempotency.
GitOps Explained: What It Is and Why Teams Adopt It
GitOps in plain words: what it actually is, the workflow it enables, and a hands-on demo using Argo CD on a local Kubernetes cluster.
You might have missed
Evergreen posts worth revisiting.