Zero-Downtime Postgres Migrations: The Playbook We Actually Follow

The first time I ran a naive database migration on a production table with a few million rows, I locked writes for almost four minutes and took down checkout for anyone unlucky enough to hit the site during that window. Nobody warned me that ALTER TABLE users ADD COLUMN loyalty_tier text NOT NULL DEFAULT 'standard' could do that, and in fairness, on modern Postgres versions adding a column with a constant default is actually fast because Postgres 11 and later handle that case without a full table rewrite. But plenty of other common migrations still take an exclusive lock for their full duration, and knowing which is which is the actual skill here, not memorizing a list of forbidden commands.

The core idea: expand, migrate, contract

The pattern that has saved us the most pain is commonly called expand and contract, and it means never making a breaking schema change in a single deploy. Instead you split any breaking change into multiple, individually safe steps, each one deployed and allowed to bake before the next one goes out.

Say you need to rename a column, which sounds trivial but is actually one of the riskier operations because a naive ALTER TABLE RENAME COLUMN breaks any currently-running application instance that still references the old name, and during a rolling deploy you always have old and new code running simultaneously for at least a few minutes. The safe sequence looks like this. First, expand: add the new column alongside the old one, and backfill it.

ALTER TABLE users ADD COLUMN account_status text;

Then deploy application code that writes to both columns on every insert or update, while still reading from the old column, so both stay in sync going forward without touching historical rows yet.

await prisma.user.update({

where: { id },

data: { status: newStatus, account_status: newStatus },

});

Next, backfill existing rows in batches, never as one giant UPDATE that locks the whole table.

UPDATE users SET account_status = status

WHERE id IN (

SELECT id FROM users WHERE account_status IS NULL LIMIT 5000

);

Run that in a loop from a script with a short sleep between batches, checking replication lag as you go if you have read replicas, and stop or slow down if lag climbs too high. Once the backfill is complete and verified, deploy application code that reads from the new column instead of the old one, still writing to both for safety. Only after that has been running cleanly for a while, typically at least a full deploy cycle so you are confident nothing is still depending on the old column, do you contract: stop writing to the old column, and in a later, separate migration, drop it.

ALTER TABLE users DROP COLUMN status;

That is four separate deploys for what looks like a one-line rename, and the first time you do this it feels absurdly slow. But every single step is individually safe, reversible, and does not require a maintenance window, which is the entire point.

Indexes are the other classic trap

Creating an index on a large table with a plain CREATE INDEX takes a lock that blocks writes for the duration of the build, which on a large table can be minutes. Postgres has supported CREATE INDEX CONCURRENTLY for a long time specifically to solve this, building the index without holding that exclusive lock, at the cost of taking somewhat longer and requiring two passes over the table.

CREATE INDEX CONCURRENTLY idx_users_account_status ON users (account_status);

The catch that trips people up with Prisma specifically is that Prisma Migrate wraps each migration in a transaction by default, and CONCURRENTLY cannot run inside a transaction, Postgres will simply reject it. The fix is to mark that specific migration as using no transaction:

-- prisma-client-js migration.sql

-- @prisma-migrate-no-transaction

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_account_status ON users (account_status);

In newer Prisma versions you configure this by adding a migration_lock or by editing the generated SQL directly and re-running with the appropriate flag, but the underlying requirement is the same regardless of tooling version: concurrent index builds and transactional migrations do not mix, and you have to explicitly opt a migration out of the transaction wrapper to use them together.

NOT NULL constraints need the same two-step treatment

Adding a NOT NULL constraint to an existing column requires Postgres to scan the entire table to verify no existing row violates it, which is another full-table lock on older Postgres versions. Since Postgres 12, you can add a CHECK constraint as NOT VALID first, which does not scan existing rows and does not take a long lock, then validate it separately in a step that only takes a lightweight lock while it scans.

ALTER TABLE users ADD CONSTRAINT account_status_not_null

CHECK (account_status IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT account_status_not_null;

The VALIDATE step still scans the table but does so without blocking concurrent writes the way a naive NOT NULL addition would, because it only takes a SHARE UPDATE EXCLUSIVE lock rather than an access exclusive one.

What we actually check before every migration

Before any schema change goes to production, we run it against a staging database seeded with a realistic row count, not a nearly-empty dev database, because lock duration scales with table size and a migration that is instant on a thousand rows can be a multi-minute lock on ten million. We also check pg_stat_activity for long-running transactions before kicking off a migration, since even a well-written CONCURRENTLY migration can get stuck waiting behind an old idle transaction holding a conflicting lock, a nasty subtlety when a forgotten open transaction from a debugging session on the connection pool blocks the migration script indefinitely.

The uncomfortable truth about this whole topic

Zero-downtime migrations are not really a database problem, they are a deployment sequencing problem that happens to involve the database. The database commands themselves are usually simple once you know the right flag or the right two-step pattern. The actual discipline is resisting the urge to make one big schema change in one deploy, and instead accepting that safe migrations on a live system are slower and more tedious than the naive version, in exchange for never having to explain to your team why checkout was down for four minutes on a Tuesday afternoon.

One last practical habit worth adopting: keep a running document per table listing the actual size, the write volume, and the current indexes, updated whenever a significant migration touches that table. It sounds like busywork until the day someone needs to plan a migration against a table they have never touched before and can immediately see, without querying anything, that it has forty million rows and a write-heavy workload, which changes the entire migration plan compared to a lightly used, ten-thousand-row configuration table. We started this after a migration on what we assumed was a small internal table turned out to be much larger than anyone remembered, because it had quietly grown over two years of a feature nobody thought about much anymore. That five-minute lookup would have prevented an unplanned, tense afternoon, and the document has paid for the time it takes to maintain many times over since.