Zero-Downtime Database Schema Migrations

Overview

Decouple schema changes from code changes

Breaking schema changes such as column renames, type changes and NOT NULL additions are among the highest-risk database operations. The expand-contract pattern decouples schema changes from code changes, removing the window where incompatible versions coexist.

> [!IMPORTANT] Every intermediate state must work with both the old and the new app version, because both run during a rolling or blue-green deploy.

What makes a change breaking

• Column rename — Old pods read the old name while new pods read the new one
• Type change — Old code writes values the new type rejects
• NOT NULL addition — Old code inserts rows without the column

Supporting techniques

• CREATE INDEX CONCURRENTLY — Non-locking index builds in PostgreSQL
• pg_repack — Table rewrites without an access-exclusive lock
• Separate migration pipeline — Migrate, verify, then deploy the app
• Production-sized replica — Test every migration before prod

Interview questions

• You need to deploy a breaking schema change to a production database with zero downtime. How do you do it?
• What are the risks of running database migrations inside a deployment pipeline, and how do you mitigate them?

More SRE topics

• The 50% Toil Cap
• Three Pillars of Observability
• Timeouts & Deadlines
• Toil
• What is SRE?
• Zero Trust and Defense in Depth