The Hidden Risk in Every Database Migration

Most development teams treat database migrations as a solved problem: write a migration file, run it in a maintenance window, done. But for enterprise systems with millions of rows and strict SLAs, a naive migration can lock tables for minutes — causing cascading timeouts that are indistinguishable from a service outage.

PostgreSQL uses Access Exclusive Locks for most DDL operations. While a lock is held, all reads and writes to that table are blocked. For a table with 50 million rows, a simple ALTER TABLE ADD COLUMN NOT NULL DEFAULT can hold a lock for 10+ minutes while PostgreSQL rewrites every row.

The Expand-Contract Pattern

The cornerstone of zero-downtime migrations is the Expand-Contract (also called Parallel Change) pattern. Every breaking migration is split into three phases deployed independently:

1. Expand — Add new columns alongside old ones. Application writes to both.
2. Migrate — Backfill existing rows in small batches to avoid lock contention.
3. Contract — Once all rows are migrated, drop the old column.

Adding a NOT NULL Column Without Downtime

The zero-downtime approach: First add the column as nullable (near-instant, no rewrite). Then set a default at the PostgreSQL level (instant in pg 11+). Then backfill in 10k-row batches with short pauses. Then add a NOT NULL constraint with the NOT VALID flag (skips scanning existing rows). Then validate the constraint separately using ShareUpdateExclusiveLock (allows reads and writes throughout). Finally, set NOT NULL — now instant because PostgreSQL knows the constraint is already validated.

Creating Indexes Concurrently

Never use CREATE INDEX without CONCURRENT — it locks the table for the full index build. Always use CREATE INDEX CONCURRENTLY which builds the index in the background without blocking reads or writes. It takes longer but your application stays available throughout.

Renaming Columns Safely

Column renames require a 5-step process: add the new column, deploy an app version that writes to both columns, backfill all existing rows, deploy an app version that reads from the new column, and finally drop the old column. This process takes multiple deployment cycles but guarantees zero downtime.

Our Migration Toolchain

We use Prisma Migrate to generate migration files, golang-migrate to execute them in production with proper locking, pgBadger for post-migration log analysis, and pg_activity for real-time monitoring during migration execution.