| name | database-evolution |
| description | Playbook for writing safe database migrations, managing schema evolutions, executing reversible rollbacks, and avoiding table lock contention in enterprise environments. |
Database Evolution & Schema Migration Playbook
This playbook establishes the engineering rules for migrating and evolving relational database schemas in enterprise applications with zero downtime and minimal table lock contention.
1. Zero-Downtime Migration Patterns
Relational database migrations must be designed so that the application can run continuously while the database is updated.
A. The Expand and Contract Pattern
Never perform destructive changes (renaming columns, deleting columns, changing types) in a single step. Use a multi-stage rollout:
- Expand: Add the new column/table. The application code starts dual-writing to both the old and new columns.
- Backfill: Run a background batch script to migrate existing legacy data from the old column to the new column.
- Transition: Update application readers to point to the new column. Remove writes to the old column.
- Contract: Safely drop the old column/table in a separate release.
2. Reversible Migrations & Rollback Scripts
Every database migration MUST be fully reversible.
- Up & Down Scripts: Every migration file must contain both an
Up script (applying changes) and a Down script (rolling back changes).
- No Data Loss on Down: Rollback scripts must never lead to untracked data loss. If a column is dropped in the
Up step, ensure it was backed up or that the rollback script restores the schema structure safely.
- Dry-run Verification: Before applying migrations on target databases, run a local migration dry-run and verify rollback:
- For Laravel/PHP:
php artisan migrate:fresh followed by tests.
- For Django/Python:
python manage.py migrate and verify rollback using test suites.
3. Avoiding Table Lock Contention
On large enterprise databases (tables with millions of rows), adding columns or indexes can lock the entire table, causing application outages.
- Adding Columns with Defaults: Never add a column with a default value directly on a large table in PostgreSQL or MySQL, as it forces a full-table rewrite. Add the column as nullable first, then set the default, and backfill existing rows in batches.
- Safe Index Creation:
- In PostgreSQL, always use
CREATE INDEX CONCURRENTLY to avoid locking writes on the table.
- In MySQL, leverage online DDL algorithms:
ALGORITHM=INPLACE, LOCK=NONE.
- Foreign Key Constraints: Adding a foreign key constraint can lock both target tables. Validate foreign keys using
NOT VALID in PostgreSQL, then validate them in the background via VALIDATE CONSTRAINT.
