database-migrations

To add a required column without database locks, use the expand-contract pattern: add the column as nullable first, backfill data in batches, then apply the NOT NULL constraint. This ensures zero-downtime schema changes for large production tables.

The expand-contract pattern is a zero-downtime migration strategy that separates schema changes into expand phases, adding new structures without breaking old code, and contract phases, removing deprecated structures after application deployment. It prevents full table locks.

Yes, this zero-downtime migration approach works with PostgreSQL and MySQL environments using Prisma, Drizzle, Django, TypeORM, and golang-migrate. It supports reversible, forward-only migration patterns for schema changes and data backfills.

Batch data backfilling during production database migrations involves updating rows in incremental chunks rather than a single transaction. This avoids long-running locks and allows zero-downtime data operations alongside expand-contract schema workflows.

The best way to create indexes without application downtime is using concurrent index creation methods within a forward-only migration plan. This avoids full table locks and maintains service availability during production schema changes.

Database migrations cause production outages when schema changes acquire full table locks, block reads and writes, or introduce incompatible application code. Prevent these issues using tested, reversible, zero-downtime expand-contract patterns and batch operations.