Safe Database Migrations at Scale

Adding a column to a table sounds minor. A developer writes the migration, CI runs it, and the system moves on. In a small database with a short deployment window, that often works fine.

In production, at scale, the same migration can lock a busy table for minutes, with reads and writes queuing behind the lock on a table that thousands of users interact with per hour. That’s when a routine schema change becomes an incident.

Why migrations break in production

Most migration problems come from the same root cause: operations that require an exclusive lock on the table, running against a table with existing traffic.

When Postgres adds a NOT NULL column without a default, it rewrites every row. When it validates a constraint, it scans every row. When it creates a regular index, it holds a lock while the entire index builds.

In a small table with low traffic, these operations finish before anyone notices. In a large table under constant load, they block every read and write until they complete. And any long-running transactions already in the queue make the wait longer.

The staging database passed because it was small and quiet. Production failed because it’s large and busy.

PostgreSQL’s explicit locking documentation is the useful reference when reviewing a migration: a statement can be correct and still block the application if its lock mode conflicts with live traffic.

The expand-contract pattern

The safest way to make schema changes that involve renaming or removing things is to split them across multiple deployments.

The expand step adds the new thing alongside the old. Both columns exist, the application writes to both and reads from both, and nothing is disrupted.

The contract step removes the old thing after the new thing has been in use long enough to be trusted. By then there are no active reads against the old column, no rows with null in the new column, and no code paths that depend on what’s being removed.

This approach trades deployment simplicity for operational safety. The change takes longer to complete, but the benefit is that no single deployment carries all the risk.

Backfilling data without causing pain

When a new column needs to be populated from existing data, backfilling in the same migration that adds the column is dangerous.

Updating every row in a large table is slow. It acquires locks, generates write-ahead log volume, and can hold long transactions open, all of which creates pressure on a live system.

The safer approach:

• Add the column without a NOT NULL constraint and without a default
• Let new rows populate it from application code going forward
• Run the backfill as a separate, batched background process
• Update rows in small chunks with a brief pause between batches
• Add the NOT NULL constraint only after every row has a value

Postgres 11 and later made one part of this easier: adding a column with a constant default no longer rewrites every row. The default is stored at the column level and applied when rows are read. But for variable data or computed values, the batched backfill approach still applies.

Creating indexes without holding a lock

Regular index creation in Postgres takes an exclusive lock for the duration of the build. On a large table, that can take minutes or longer.

Postgres supports concurrent index creation:

create index concurrently idx_orders_customer_id on orders(customer_id);

This builds the index in stages without blocking reads or writes. It takes longer than a regular build, but it doesn’t hold an exclusive lock.

The tradeoff: concurrent builds can fail if they encounter a deadlock during the process. If that happens, the index is left in an invalid state and needs to be dropped and rebuilt. Worth running inside a monitored window rather than an unattended deployment.

One practical note: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Most migration frameworks wrap statements in transactions by default. That needs to be disabled for this statement.

The PostgreSQL CREATE INDEX reference documents the operational restrictions of concurrent index creation; check the version-specific documentation before relying on it in a production runbook.

This applies to any Postgres index, including the trigram and full-text indexes used for search. Those indexes can be large. Building them concurrently isn’t optional if traffic can’t stop.

What not to put in a migration

A migration file should contain schema changes only. It shouldn’t contain:

• Large data backfills
• Business logic that belongs in application code
• Multi-step sequences that depend on data being in a specific state
• Operations that need to be retried or monitored

Each of those creates the potential for a migration to fail partway through or leave the database in an inconsistent state. Schema changes, written carefully, are at least recoverable. Data migrations mixed into schema migrations often aren’t.

The deployment and the migration are separate concerns

These are separate concerns, so treat them that way.

The migration that adds the new column happens before the deployment that starts using it. The deployment that removes the old code happens before the migration that drops the old column. The backfill that populates the data runs as a monitored background process, not as part of any deployment.

This decomposition removes the assumption that every change needs to land in one atomic step. Most of them don’t, and the ones that do are usually the ones that hurt most when they go wrong.

The goal isn’t fearless migrations. It’s migrations where the cost of being wrong is low, the blast radius is bounded, and the path to recovery is clear. When a schema change is designed that way, the gap between staging and production stops being a trap.