Zero-Downtime Postgres Migrations: Expand-Contract in Practice
KU
Kiril Urbonas
5 days ago • 5 min read•
A single ALTER TABLE took a lock and stalled every write for 40 seconds during peak traffic. Expand-contract is how we stopped shipping outages.
We renamed a column. ALTER TABLE orders RENAME COLUMN amount TO amount_cents. It ran in milliseconds in staging. In production it took an ACCESS EXCLUSIVE lock, and because a long-running report query was already holding a read lock on the table, our rename sat in the lock queue, and every write behind it also queued. For 40 seconds the checkout flow returned timeouts. The migration "worked," but we took an outage to run it.
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.
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.
Add the new column. This is cheap in modern Postgres because adding a nullable column with no default is metadata-only, no table rewrite, no long lock.
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.
# both writes, old read
order.amount = value
order.amount_cents = value # dual write
# reads still use order.amount
Do not run UPDATE orders SET amount_cents = amount. On a large table that's one giant transaction holding locks and bloating WAL. Batch it, commit between batches, and throttle so you don't saturate I/O.
-- 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. We run this from a small script that sleeps 200ms between batches and watches replication lag, pausing if a replica falls more than 30 seconds behind. A 40-million-row backfill finished in about two hours this way with zero impact on live queries.
Once the backfill shows zero remaining nulls, deploy code that reads from amount_cents. Now add any NOT NULL constraint you need, but do it the safe way. A plain SET NOT NULL scans the whole table under a lock. Instead add a validated CHECK constraint first, which can be validated without an exclusive 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;
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. This is the trick that keeps the whole thing online.
Stop writing to the old column, confirm nothing references it (check your query logs, not your memory), then drop it.
ALTER TABLE orders DROP COLUMN amount;
DROP COLUMN is fast and metadata-only. By now no code touches amount, so dropping it is invisible to the application.
The other classic lock is CREATE INDEX, which locks the table against writes for the whole build. Always 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 taking longer and needing two table scans. One warning: it can't run inside a transaction block, and if it fails partway it leaves an invalid index you must drop manually. Check with \d orders for an INVALID index after any failed concurrent build and clean it up before retrying.
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 our original rename did.
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. This one setting would have turned our 40-second outage into a failed migration and a Slack message.
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 a schema change that pages the on-call.
Get the latest tutorials, guides, and insights on AI, DevOps, Cloud, and Infrastructure delivered directly to your inbox.
Users kept asking the same questions in slightly different words, and we paid full price every time. Semantic caching cut our LLM bill by a third.
A user got our support bot to recite its system prompt and then draft a refund it wasn't authorized to give. Two layers of guardrails, one on input, one on output, closed both holes.
Explore more articles in this category
The Backstage demo always wows leadership. Then six months later the catalog has 400 stale entries and nobody trusts it. Here's what got ours to actually stick.
We inherited 200-odd AWS resources built by hand over four years, with no state file anywhere. Here's how import blocks and a generation workflow got them under Terraform without a rebuild.
Adding a read replica cut primary load 60%, then support tickets rolled in about users not seeing their own edits. Replication lag turned into a correctness bug we had to route around.
Evergreen posts worth revisiting.
