Database Migrations in Production

Overview

Overview

Database migrations are among the highest-risk operations in production. A naive ALTER TABLE on a large table can take a lock for minutes or hours, block reads and writes, and cause a full outage. Knowing how the database executes DDL, and which operations are safe at what scale, is what lets you migrate without downtime.

Why a naive migration causes an outage

PostgreSQL controls DDL with lock modes. The dangerous ones need a full table scan or rewrite while holding a strong lock.

• ALTER TABLE ... SET NOT NULL — AccessExclusiveLock, conflicts with everything including SELECT — Scans every row to validate; no reads or writes meanwhile
• ADD COLUMN with a default that must be written to every row — AccessExclusiveLock — Rewrites the table
• Plain CREATE INDEX — ShareLock — Reads continue, but every write blocks until the build finishes
• ADD CONSTRAINT ... NOT VALID — Brief lock, catalog only — Instant, does not scan existing rows
• VALIDATE CONSTRAINT — ShareUpdateExclusiveLock — Scans the table while reads and writes continue

On a 100-million-row table, a migration that scans the full table (backfilling a default, validating NOT NULL, building an index) can hold its lock for 10-30 minutes.

> [!NOTE] Corrected: a plain CREATE INDEX takes a ShareLock, which blocks writes but not reads; it is not an AccessExclusiveLock.

Expand-contract: adding a NOT NULL column safely

• 1 — Add the column as nullable with no default — Instant: only the catalog changes, not the rows
• 2 — Deploy code that writes the new column (and the old one) — Existing rows stay valid while the column is nullable
• 3 — Backfill existing rows in batches, with sleeps between batches — Avoids I/O saturation
• 4 — ALTER TABLE table ADD CONSTRAINT col_not_null CHECK (col IS NOT NULL) NOT VALID, then ALTER TABLE table VALIDATE CONSTRAINT col_not_null — Validation takes only a ShareUpdateExclusiveLock. In PostgreSQL 12+, a later SET NOT NULL uses the validated check and skips the full scan
• 5 — Deploy code reading the new column — The data is complete and enforced
• 6 — Drop the old column — Nothing reads it any more

CREATE INDEX CONCURRENTLY

CREATE INDEX CONCURRENTLY builds an index without blocking reads or writes. It registers the index, scans the table to build it, makes a second pass for rows changed meanwhile, and finally marks the index valid.

• Duration — Takes longer, about 2-3x a normal index build
• Failure mode — Can fail partway and leave an INVALID index that must be dropped and rebuilt
• Verification — After the build, confirm pg_index.indisvalid is true for the index (\d table also marks it INVALID)

> [!WARNING] pg_indexes.indexdef does not show validity; check pg_index.indisvalid after every concurrent build.

MySQL: online schema change tools

gh-ost and pt-online-schema-change make virtually any MySQL schema change safe regardless of table size.

Rollback plan before you run

• Reversible, such as adding a column the code uses optionally — Verify the application runs correctly without the migration; rolling back is straightforward
• Irreversible, such as dropping a column or removing a unique constraint — Point-in-time backup immediately before the migration, plus a tested restore procedure

> [!IMPORTANT] Every migration needs its rollback plan written down before it runs.

Key concepts

AccessExclusiveLock

PostgreSQL lock mode required by most DDL operations that conflicts with all other lock types, blocking reads and writes on the target table for the migration duration.

Expand-Contract Pattern

A zero-downtime migration strategy that adds new schema elements (expand), migrates data and code, then removes old schema elements (contract) across multiple coordinated deployments.

CREATE INDEX CONCURRENTLY

A PostgreSQL DDL command that builds an index without holding a table lock, allowing reads and writes to continue — at the cost of longer build time and the possibility of leaving an INVALID index.

NOT VALID Constraint

A PostgreSQL constraint option that adds a constraint to the catalog without scanning existing rows, followed by VALIDATE CONSTRAINT to check existing rows under a lower-impact lock.

Backfill

The process of populating a new column with computed values for existing rows, done in batches with sleep intervals to avoid I/O saturation and replication lag.

When to use it

• Planning a schema change on a large production table without taking downtime
• Explaining the expand-contract migration pattern for zero-downtime schema evolution
• Choosing between pg_repack, pt-osc, and gh-ost for online schema changes
• Diagnosing why an ALTER TABLE is blocking production reads and writes
• Designing the backfill phase of a multi-phase migration safely

Interview questions

• Walk through the safe zero-downtime migration sequence for adding a NOT NULL column with a default to a 500-million-row PostgreSQL table.
• What happens when CREATE INDEX CONCURRENTLY fails partway through, and how do you recover?
• How do you safely backfill a large column in PostgreSQL without causing replication lag or I/O saturation?

More Troubleshooting topics

• Canary Deployment Problems
• Connection Pool Exhausted
• CoreDNS Failures in Kubernetes
• CPU Runaway Process Investigation
• CrashLoopBackOff
• Database Deadlock Diagnosis
• Deployment Rollback Strategies
• Disk IOPS Saturation
• DNS Failures
• EC2 Instance Unresponsive
• ECS Task Failures and Restarts
• ELB 5xx Errors