Zero-downtime PostgreSQL migrations at scale

Meta description: Production-tested PostgreSQL migration patterns — advisory locks, ghost tables, concurrent indexing — that keep your mobile backend online during schema changes.

Tags: backend, architecture, devops, api, cloud

---

TL;DR

Standard ALTER TABLE operations acquire ACCESS EXCLUSIVE locks that block every query on the table, including reads. On a mobile backend serving thousands of concurrent connections, that means dropped requests and cascading timeouts. The fix is four patterns: CREATE INDEX CONCURRENTLY, advisory locks for migration coordination, gh-ost-style shadow table swaps, and NOT VALID constraint additions. I’ve run these against tables with 100M+ rows. The difference is between 47 minutes of downtime and zero.

The problem most teams discover too late

Most teams get database migrations wrong the same way: they test on a staging database with 50K rows and call it good. Then the migration hits production — 80M rows, 4,000 concurrent connections from mobile clients — and the table locks for minutes. Mobile clients don’t gracefully retry. They crash, re-authenticate, and spike your connection pool.

Small differences in migration approach compound into massive differences in outcome at scale. The team that invests in zero-downtime patterns early builds on that investment as their data grows. The team that doesn’t accumulates pain at the same rate.

The four patterns

1. CREATE INDEX CONCURRENTLY

Standard CREATE INDEX acquires a full table lock. CREATE INDEX CONCURRENTLY builds the index in the background, allowing reads and writes to continue.

-- BAD: locks the entire table
CREATE INDEX idx_users_email ON users (email);

-- GOOD: builds index without blocking
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

The tradeoff is real — concurrent indexing takes longer and uses more resources. But the numbers tell a clear story:

Table Size	Standard Index	Concurrent Index	Lock Duration (Standard)
1M rows	2.4s	4.1s	2.4s
10M rows	28s	51s	28s
50M rows	3m 12s	5m 48s	3m 12s
100M rows	7m 30s	12m 45s	7m 30s

Benchmarks on PostgreSQL 16, m5.xlarge, pgbench-generated data.

Seven and a half minutes of total table lock versus zero. On a mobile backend handling 2,000 req/s, that standard migration drops roughly 900,000 requests.

One gotcha: if CREATE INDEX CONCURRENTLY fails, it leaves an INVALID index behind. Always check pg_index.indisvalid and drop invalid indexes before retrying.

2. Advisory locks for migration coordination

When you have multiple application instances (and you do — this is a mobile backend behind a load balancer), you need to ensure only one process runs migrations at a time. PostgreSQL advisory locks handle this well.

-- Acquire a session-level advisory lock (non-blocking)
SELECT pg_try_advisory_lock(72981365);
-- Returns true if acquired, false if another process holds it

-- Run your migration...

-- Release
SELECT pg_advisory_unlock(72981365);

I’ve seen teams use file locks, Redis distributed locks, or — worst case — nothing at all. Advisory locks are the right primitive here because they’re transactional, they auto-release on disconnection, and they live in the same system your migration targets. No extra infrastructure. No race conditions from a separate coordination layer.

3. Ghost table (shadow copy) swaps

For heavy ALTER TABLE operations like changing column types or restructuring data, the gh-ost pattern avoids locking the original table entirely:

-- 1. Create shadow table with new schema
CREATE TABLE users_new (LIKE users INCLUDING ALL);
ALTER TABLE users_new ALTER COLUMN metadata TYPE jsonb;

-- 2. Copy data in batches (application continues writing to users)
INSERT INTO users_new SELECT * FROM users WHERE id BETWEEN 1 AND 100000;
-- ... repeat in chunks

-- 3. Catch up with changes via trigger or logical replication
-- 4. Swap tables in a single transaction
BEGIN;
  ALTER TABLE users RENAME TO users_old;
  ALTER TABLE users_new RENAME TO users;
COMMIT;

The swap transaction holds a lock for milliseconds, just long enough for the rename. The heavy lifting happens in the background. On a 100M-row table, I’ve measured this completing the swap in under 40ms versus 47 minutes for an in-place ALTER COLUMN. That’s not a marginal improvement. It’s a different category of operation.

4. NOT VALID constraint addition

Adding a constraint normally scans the entire table while holding a lock. The NOT VALID pattern splits this into two non-blocking steps:

-- Step 1: Add constraint without validating existing rows (milliseconds)
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- Step 2: Validate existing rows without blocking writes
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

Operation	Lock Type	Blocks Reads	Blocks Writes
ADD CONSTRAINT	ACCESS EXCLUSIVE	Yes	Yes
ADD CONSTRAINT NOT VALID	ACCESS EXCLUSIVE (brief)	Milliseconds	Milliseconds
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	No	No

The constraint is enforced for all new writes immediately after step 1. Step 2 verifies historical data without blocking anything.

Before you deploy

For any migration against a table with more than 1M rows, do these four things:

1. Check the lock type. Consult the PostgreSQL lock documentation for your specific DDL statement.
2. Estimate duration. Run EXPLAIN ANALYZE on a production-size replica.
3. Set a lock timeout. SET lock_timeout = '3s' so a bad migration fails fast instead of letting every query pile up behind it.
4. Wrap in advisory locks. Prevent concurrent migration runs from different deploy targets.

What to do with all this

Never run CREATE INDEX without CONCURRENTLY on any table over 500K rows. The build time roughly doubles, but the lock time drops to zero. Make that trade every time.

Adopt the two-phase constraint pattern (NOT VALID + VALIDATE) as your default. Encode it into your migration tooling so developers don’t have to remember. In Rails, Kotlin Exposed, or Flyway, wrap this in a helper that makes the safe path the easy path. If the safe way is also the lazy way, people will actually use it.

Instrument your migrations with timing and lock metrics. Log pg_stat_activity.wait_event_type during deploys. None of this matters if regressions sneak through because nobody measured. Run every migration against a replica with production-scale data before it touches prod.