Schema Migrations in Production
Schema migrations change database structure (add columns, create indexes, modify types) on live production databases. The challenge is making changes without causing downtime. Tools like Flyway, Liquibase, Alembic (Python), and Rails ActiveRecord Migrations track and apply changes in order. Every migration should be forward-compatible — old application code should still work with the new schema during deployment.
Schema migrations are surgery on a patient who must stay awake (zero downtime). Expand-migrate-contract: first attach the new organ alongside the old one, then reroute blood flow, then remove the old organ. CREATE INDEX CONCURRENTLY is using local anesthesia; without CONCURRENTLY, you knock the whole patient out (table lock).
Safe migration patterns for PostgreSQL: adding a column is safe (with DEFAULT, instant in PG 11+), adding an index must use CONCURRENTLY (otherwise it locks the table for the entire build time), renaming a column is dangerous (old code breaks — use aliases or column addition + backfill + code deploy + old column drop), changing a column type may rewrite the table (avoid on large tables — add a new column instead). NOT NULL constraints with DEFAULT are safe in PG 11+ (stored in catalog, not backfilled to every row). Backfill in batches (UPDATE ... WHERE id BETWEEN x AND y with pg_sleep between batches) to avoid long-running transactions and lock contention. Always test migrations on a copy of production data before running on production.
Zero-downtime migration strategy: expand-migrate-contract. Expand: add new columns/tables, deploy code that writes to both old and new, backfill historical data. Migrate: deploy code that reads from new. Contract: drop old columns/tables. This takes three deployments but guarantees no downtime. For large table restructuring, use pg_logical or Debezium to replicate data to a new table with the desired schema, then swap with a rename (requires brief lock). Ghost (GitHub open-source for MySQL) and pgroll (for PostgreSQL) automate online schema changes by creating a shadow table, copying data, capturing ongoing changes, and swapping. In MongoDB, schema migrations are technically optional (schemaless), but in practice you need a migration strategy for schema evolution — usually versioned documents with an application-level migration on read (lazy migration) or batch migration.
My approach to production schema migrations follows the expand-migrate-contract pattern. First, I expand the schema (add new columns, create new indexes concurrently, deploy code that writes to both old and new). Then, I migrate (backfill historical data in batches, deploy code that reads from new). Finally, I contract (drop old columns once all code uses the new schema). For PostgreSQL, critical rules: always CREATE INDEX CONCURRENTLY (normal index creation locks the table), add columns with DEFAULT (instant in PG 11+), never rename columns in-place (add new, backfill, switch code, drop old), and backfill in small batches with pauses to avoid overwhelming the database. I test every migration on a production-sized copy before running it in production.
Running CREATE INDEX without CONCURRENTLY on a large production PostgreSQL table. This acquires a write lock on the entire table for the duration of the index build — which on a 100M row table could be minutes of complete write downtime. Always use CREATE INDEX CONCURRENTLY, even though it is slower and requires more disk space during construction.