Zero-Downtime Schema Changes

Overview

Overview

Schema migrations that lock tables cause downtime. On a high-traffic database even a millisecond-level lock on a large table builds a connection queue that looks like an outage, so zero-downtime migrations need a disciplined approach.

Diagnosis flow: is this migration safe?

The expand-contract pattern

• Expand — Add the new structure (new column, table or index) alongside the old — Old and new code both keep working
• Contract — After all code is deployed and no longer uses the old structure, remove it — Separates the deploy timeline from the migration timeline

Operations and their lock behavior

• NOT NULL column with a DEFAULT, PostgreSQL before 11 — Full table rewrite under a table lock — Upgrade, or add nullable and backfill
• NOT NULL column with a constant default, PostgreSQL 11+ — No rewrite — Safe as is
• NOT NULL column with a computed default — Needs a backfill — Add as NULLABLE, backfill in batches, then add a CHECK ... NOT VALID constraint and VALIDATE it; the scan does not block writes
• Index creation, PostgreSQL — CREATE INDEX blocks writes — CREATE INDEX CONCURRENTLY: slower, but reads and writes continue
• Index or supported alters, MySQL — Full table copy — ALTER TABLE ... ALGORITHM=INPLACE
• Column rename — Table lock — Expand-contract only

> [!IMPORTANT] Always use CONCURRENTLY for index creation in production.

Online schema change tools

• pt-online-schema-change (Percona) — MySQL — Triggers
• gh-ost (GitHub) — MySQL — Binary log streaming instead of triggers
• pgroll — PostgreSQL — Creates the new structure and cuts over

> [!WARNING] Column rename is one of the most dangerous migrations: without a table lock the only way is add new column, copy data, switch the application, drop the old column, across multiple deployments.

Key concepts

Expand-contract

Add new structure alongside old (expand), deploy code using new structure, remove old structure (contract). Decouples the migration from the deploy. Requires multiple deploy phases.

CREATE INDEX CONCURRENTLY

PostgreSQL command to create an index without locking the table. Allows concurrent reads/writes. Takes longer. Can fail if a concurrent write conflicts — drop the partial index and retry.

Backfill in batches

UPDATE in small batches (1000-10000 rows) with sleep between batches to avoid locking the table. UPDATE large_table SET new_col = compute(old_col) WHERE id BETWEEN 1 AND 1000.

gh-ost

GitHub online schema change for MySQL. Streams binary log (no triggers), creates shadow table, applies changes, does atomic cut-over. Zero writes to the original table during migration.

pgroll

PostgreSQL online schema change tool. Uses multi-version schema approach — both old and new schema are active simultaneously, allowing multiple application versions during rollout.

When to use it

• Planning a schema change for a table with millions of rows in production
• Explaining why renaming a column requires multiple deploys
• Using the expand-contract pattern to add a NOT NULL column safely
• Choosing between pt-osc and gh-ost for a MySQL migration

Interview questions

• How do you safely rename a column in a production PostgreSQL table with 500 million rows without downtime?

More Troubleshooting topics

• RDS Read Replica Replication Lag
• S3 High Latency Spikes
• Slow Query Diagnosis
• Staging Passes, Prod Fails
• TLS Certificate Expiry Incidents
• VPC Connectivity Failures