Zero-downtime database migrations: expand and contract in Postgres

A schema change that took two seconds on your laptop can freeze a production app for minutes. Nothing errors. Requests just hang, the load balancer starts timing out, and by the time someone finds the stuck migration the support inbox is full. If you run a custom app or internal tool on PostgreSQL and deploy during working hours, you'll hit this sooner or later.

Two things cause most of the pain. Some statements take a lock that stops everyone else touching the table, and some changes break the version of your code that's still running while the deploy rolls out. This guide covers both, using the PostgreSQL 18 documentation (18 is the current release as of October 2026) and a pattern called expand and contract.

Why a quick ALTER TABLE can take your app down#

The ALTER TABLE reference is blunt about the default: "An ACCESS EXCLUSIVE lock is acquired unless explicitly noted." If one statement has several subcommands, it takes the strictest lock any of them needs.

The locking chapter explains what that lock means. ACCESS EXCLUSIVE conflicts with every other lock mode and "guarantees that the holder is the only transaction accessing the table in any way". It's also the only mode that blocks a plain SELECT. And once acquired, a lock is normally held until the end of the transaction, so a migration file that does several things inside one transaction keeps the table closed for all of them.

The nasty part is the wait, not the change itself. Say a reporting query has been running on orders for three minutes. Your migration asks for ACCESS EXCLUSIVE and has to wait for that query to finish. Meanwhile every new query on orders, reads included, lines up behind the migration. Chris Sinjakli describes this in a GoCardless engineering post: if the operation has to wait for its exclusive lock, "all queries which arrive after it will queue up behind it". Nikolay Samokhvalov reproduces the same effect in a PostgresAI walkthrough, where the waiting DDL ends up "blocking even read-only transactions that deal with our table".

So an ALTER TABLE that only needs the lock for a millisecond can still cause an outage that lasts as long as the slowest transaction already running.

Set a lock timeout and retry#

The fix for the queue is to let the migration give up quickly. The lock_timeout setting aborts any statement that waits longer than the given time to acquire a lock, and the limit applies separately to each lock attempt. It's off by default. statement_timeout is the broader cousin: it aborts any statement that runs too long, whatever it's doing.

SET lock_timeout = '2s';
SET statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN delivery_notes text;

If the lock isn't available within two seconds, the statement fails, the queue behind it clears, and users barely notice. Then you retry. The GoCardless library sets these limits automatically before Rails migrations run (750 ms for lock waits and 1,500 ms per statement by default, per the same post), and the post says plainly that hitting a limit means retrying the migration later. The PostgresAI article goes further with a 50 ms timeout and a retry loop that backs off exponentially with jitter.

Pick numbers that fit your traffic. What matters is that a timeout exists, that the migration runner treats the failure as "try again soon" instead of "deploy failed, page someone", and that you look for long-running transactions if retries keep failing.

Which changes are cheap and which rewrite the table#

A lock timeout stops the queue from forming. It doesn't help if the statement, once it has the lock, spends ten minutes rewriting every row. The ALTER TABLE page tells you which changes do that.

The constraint row deserves a closer look. The docs say that with NOT VALID the command "does not scan the table and can be committed immediately", and that a later VALIDATE CONSTRAINT takes only a SHARE UPDATE EXCLUSIVE lock. That mode, per the locking chapter, blocks other schema changes and VACUUM but not normal reads and writes. In PostgreSQL 18, NOT VALID works for foreign-key, CHECK and not-null constraints. On older versions you can get the same result for NOT NULL with a check constraint: add CHECK (email IS NOT NULL) NOT VALID, validate it, then run SET NOT NULL, which the docs say skips the full scan when a valid check constraint already proves no nulls exist.

Indexes have their own trade-off. The CREATE INDEX page says a concurrent build scans the table twice, waits for existing transactions that could use the index, and "takes significantly longer to complete". It also can't run inside a transaction block. If your migration tool wraps each file in a transaction, you'll need its opt-out for these steps. And if a concurrent build fails partway, it leaves an "invalid" index behind. The recommended fix is to drop it and run the concurrent build again.

Expand and contract: changing a column without breaking running code#

Locks are only half the problem. During a rolling deploy, old and new versions of your app run at the same time against the same database. If the migration renames phone to mobile_phone, the old version starts failing the moment the rename commits.

The answer is a pattern Danilo Sato describes on martinfowler.com as parallel change, also known as expand and contract. You expand the interface so it supports old and new versions, migrate every client to the new version, then contract by removing the old one. The article notes that most database refactorings follow this shape.

Here's the rename, done safely, as a series of separate deploys:

1. Expand the schema. Add mobile_phone as a nullable column with no default. That's a metadata-only change, so it's quick once it gets its lock.
2. Ship code that writes to both phone and mobile_phone but still reads phone. Old and new app versions both work.
3. Backfill. Copy phone into mobile_phone for existing rows in small batches (a few thousand rows per transaction, with a short pause between) so you never hold row locks for long or flood replication.
4. Ship code that reads mobile_phone, still writing both. Compare the columns for a day if the data matters.
5. Tighten. If the new column must be NOT NULL, add the constraint NOT VALID and validate it as a separate step.
6. Contract. Ship code that stops touching phone. Once that version is the only one running, drop the column.

It's more steps than one RENAME COLUMN. But each step is small, each one leaves both the previous and next code versions working, and you can stop or roll back the app at any point before step 6 because the old column is still there and still current. Type changes, splitting one column into two, and moving data to a new table all follow the same sequence.

A short checklist for each migration#

Before a schema change goes to production, ask:

• Does any statement take ACCESS EXCLUSIVE, and is lock_timeout set with a retry?
• Does it rewrite or scan the whole table? If so, is there a NOT VALID, concurrent or batched version?
• Is anything that needs CONCURRENTLY running outside a transaction?
• Can the currently deployed code keep working after this change commits?
• If the deploy goes wrong halfway, can you roll the app back without touching the schema?
• Are slow transactions (reports, exports, stuck sessions) running right now?

Write the answers into the pull request. At TheAILAB we treat that as part of reviewing any migration on a live system, because the SQL diff alone rarely shows how long a lock will be held.

Start with the cheapest win: add SET lock_timeout to every migration your team runs against production this week. It won't make a slow change fast, but it turns "the app froze for four minutes" into "the migration retried twice", and that's the difference users notice.

Sources

1. Parallel Change — Danilo Sato, martinfowler.com (13 May 2014) (checked 2026-10-25)
2. ALTER TABLE (PostgreSQL 18 documentation) — The PostgreSQL Global Development Group (checked 2026-10-25)
3. Explicit Locking (PostgreSQL documentation, current) — The PostgreSQL Global Development Group (checked 2026-10-25)
4. CREATE INDEX (PostgreSQL documentation, current) — The PostgreSQL Global Development Group (checked 2026-10-25)
5. Client Connection Defaults (PostgreSQL documentation, current) — The PostgreSQL Global Development Group (checked 2026-10-25)
6. Zero-downtime Postgres migrations - a little help — Chris Sinjakli, GoCardless engineering blog (checked 2026-10-25)
7. Zero-downtime Postgres schema migrations need this: lock_timeout and retries — Nikolay Samokhvalov, PostgresAI (23 September 2021) (checked 2026-10-25)