Executive Summary: How to perform non-blocking column additions, table rewrites, and index creations without locking tables or dropping active API requests.
Altering database schema on live production tables containing millions of rows can lock tables, cause HTTP 500 errors, and bring down web applications.
To achieve zero downtime during breaking database schema changes, deploy changes in multi-phase releases:
1. Expand (Add Phase): Add the new column/table as nullable without dropping the old column.
2. Dual-Writing: Update backend code to write to both old and new columns simultaneously.
3. Backfill Data: Run background migration scripts in small chunks (e.g. 1,000 rows per batch) to populate old data into the new structure.
4. Switch Reads: Update application code to read exclusively from the new column.
5. Contract (Cleanup Phase): Drop old columns/tables in a subsequent deployment.
In PostgreSQL, always create indexes using CREATE INDEX CONCURRENTLY to avoid taking write locks on the table. In MySQL 8, use ALGORITHM=INPLACE, LOCK=NONE for online DDL operations.
1. The Expand and Contract Pattern
To achieve zero downtime during breaking database schema changes, deploy changes in multi-phase releases:
1. Expand (Add Phase): Add the new column/table as nullable without dropping the old column.
2. Dual-Writing: Update backend code to write to both old and new columns simultaneously.
3. Backfill Data: Run background migration scripts in small chunks (e.g. 1,000 rows per batch) to populate old data into the new structure.
4. Switch Reads: Update application code to read exclusively from the new column.
5. Contract (Cleanup Phase): Drop old columns/tables in a subsequent deployment.
2. Non-Blocking Index Creation
In PostgreSQL, always create indexes using CREATE INDEX CONCURRENTLY to avoid taking write locks on the table. In MySQL 8, use ALGORITHM=INPLACE, LOCK=NONE for online DDL operations.