Database migrations without downtime

In short: Schema changes in live operation succeed without downtime if you split them into small, backward-compatible steps (“expand and contract”).

In more detail: During rollout the old and new program versions briefly run side by side on the same database. The schema must understand both.

In Depth

• Expand: Add new columns or tables without removing anything old. Create new columns nullable or with a default.
• Migrate: The new program version writes to both places (old and new). Copy existing data over in small batches in the background.
• Contract: Only when everything is switched and checked, remove the old in a later release.
• Example renaming a column: Not RENAME, but create a new column, write twice, copy, switch reads, delete the old column later.
• Dangerous commands: Depending on the database, ADD COLUMN with a default, index builds (use CREATE INDEX CONCURRENTLY) or type changes lock the whole table.
• Way back: Each step should be reversible on its own. Version migrations and run them in the pipeline.
• Tools: Flyway, Liquibase, Prisma Migrate, gh-ost (MySQL).

See also: transactions and ACID