Zero-Downtime Database Migrations: A Practical Guide

This guide outlines a practical approach to performing zero-downtime database migrations, crucial for growing products with ever-expanding schemas. The key principle is never to introduce changes that both old and new application versions cannot handle simultaneously. By breaking the migration into three phases - expand, backfill, and cutover - each step can be safely deployed and rolled back independently.

In the first phase, the schema is expanded without altering existing structures. New columns are added as nullable first, followed by backfilling data into them. Indexes should also be added concurrently to avoid blocking hot tables. Renaming structures should be postponed until the final contract phase.

The backfill phase involves incrementally updating large tables, row by row, to incorporate new columns or tables. This process generates minimal locks and replication lag, ensuring uninterrupted production traffic. It's essential to validate the backfill results before proceeding to avoid data integrity issues.

The cutover phase is where the application starts using the newly expanded schema. This is done through dual writes behind a feature flag, allowing simultaneous writes to both old and new structures while reads continue from the old one. Once the dual-write path is stable, reads are gradually switched to the new structure in a controlled manner, similar to canary deployments. The old structures are then removed after a full deprecation window, providing a safety net in case any issues arise during the transition.