database-evolution

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:

1. Expand: Add the new column/table. The application code starts dual-writing to both the old and new columns.
2. Backfill: Run a background batch script to migrate existing legacy data from the old column to the new column.
3. Transition: Update application readers to point to the new column. Remove writes to the old column.
4. 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.