Database Migrations at Scale Without Downtime
Adding a NOT NULL column with a default value used to be a single migration step for us — until we ran one against a 40 million row orders table in Postgres and watched it hold an ACCESS EXCLUSIVE lock for over four minutes while it rewrote the table. Every write to that table queued behind it. Since Postgres 11, adding a column with a constant default is supposed to be fast, but we'd added a non-constant default expression, which still forces a full rewrite.
The pattern we adopted for anything client-facing: split what used to be one migration into three deploys. First add the column as nullable with no default, deploy code that writes to it going forward, then backfill existing rows in small batches (we used 5,000 rows per batch with a short sleep between batches to avoid saturating replication lag), and only add the NOT NULL constraint once the backfill is confirmed complete.
For index creation on large tables, CREATE INDEX CONCURRENTLY was non-negotiable once we'd been burned once — a regular CREATE INDEX takes a lock that blocks writes for the full build time, which on a large table can be tens of minutes. Concurrent index builds take roughly twice as long and can fail silently leaving an invalid index behind, so we always follow up with a check for INVALID indexes after the fact.
Renaming or dropping columns follows the same expand-and-contract pattern in reverse: stop reading the old column in application code, deploy, wait a full release cycle to be sure nothing else depends on it, then drop it in a separate migration. We got burned once by dropping a column the same week we removed the last application reference, only to discover a reporting script outside the main codebase still queried it directly.
We now run every migration against a production-sized snapshot in staging first and check pg_stat_activity during the run to estimate lock duration before it ever touches production. It adds a day to migrations that used to ship same-day, but it's replaced a category of incident that used to show up as a spike in request timeouts with no obvious cause in application logs.
