Zero-downtime database schema migrations on a high-throughput system
In development, running `ALTER TABLE orders ADD COLUMN status VARCHAR NOT NULL DEFAULT "pending"` finishes in 3 milliseconds. In production on a table with 25 million active records and 1,500 transactions per second, that exact command acquires an `ACCESS EXCLUSIVE` lock. All read and write queries queue up behind it, exhausting the connection pool and taking the entire site down.
The Expand and Contract pattern
We codified a mandatory zero-downtime policy for all schema updates, based on the Expand-and-Contract (parallel-run) strategy:
Phase 1 (Expand): Add the new column as nullable without a default constraint. This operation completes instantly without rewriting table pages on disk.
Phase 2 (Dual-write): Deploy application code that reads from the old column but writes to both old and new columns.
Phase 3 (Backfill): Run asynchronous background workers to backfill data for legacy rows in batches of 5,000 with sleep intervals to keep replication lag near zero.
Adding indexes safely with CONCURRENTLY
Creating a standard index locks the entire table against writes for the duration of the scan. In PostgreSQL, always use `CREATE INDEX CONCURRENTLY`.
It takes roughly twice as long to build because it performs multiple table passes, but it requires only a weak lock that allows normal inserts, updates, and deletes to continue unhindered.
Lock timeouts are your safety net
Never run a migration in production without setting a strict lock timeout. By running `SET lock_timeout = "2s"` before any DDL statement, you guarantee that if the migration cannot acquire its necessary lock within two seconds, it aborts cleanly rather than blocking incoming user queries in a massive traffic jam.
Have a similar challenge?
We build production-grade software for companies that need it done right.
Let's Talk
