Schema Changes Are Operational Events, Not Code Changes
“A schema migration that acquires a table lock on a 100M-row table is a production incident waiting to happen: its execution plan, duration, and blast radius must be treated with the same rigor as a major infrastructure change. ”
ALTER TABLE ... ADD COLUMN NOT NULL on PostgreSQL requires a full table rewrite and holds an AccessExclusiveLock that blocks all reads and writes for the duration. On a 100GB table, this takes minutes. The safe path is the expand-contract migration pattern: add nullable, backfill in batches, add constraint. Engineers who treat schema changes as code changes learn otherwise during their first 3am page about a locked table.
Why It Matters
Schema migrations occupy a dangerous middle ground: they look like development tasks in code review, but they execute as infrastructure operations in production. A developer adding a NOT NULL column to a frequently-written table may not realize they are scheduling a multi-minute AccessExclusiveLock on every query in that table's read and write path. By the time the migration starts running in production, the pager has already fired.
The cascade risk is subtler and worse. DDL locks in some databases propagate through foreign key relationships. A lock on a parent table can block operations on child tables that have no pending migrations at all. In a database with a shared large reference table, a single careless migration can render unrelated application functionality unavailable for the duration of the lock window.
The replication dimension is equally treacherous. DDL statements replicate to replicas, and a slow migration on the primary generates replication lag on every replica for its entire duration. If the migration takes 8 minutes, every replica falls 8 minutes behind the primary: affecting read availability, replica failover readiness, and any consumer that reads from the replica under the assumption of near-current data.
Failure Modes
- ·AccessExclusiveLock on a large table blocking all reads and writes for the migration duration
- ·DDL lock cascade through foreign key relationships blocking unrelated application functionality
- ·Replication lag spike on all replicas during migration, degrading read availability and failover readiness
- ·Migration timeout leaving the table in an inconsistent partially-migrated state
- ·Lock wait queue accumulating behind the DDL lock, causing connection pool exhaustion when the lock releases
Amplification Risks
- ⚡A DDL lock holding for 5 minutes on a 10k req/s table accumulates 3M blocked requests in the lock wait queue
- ⚡Replication lag from migration creates a window where primary failover to a lagged replica causes data loss risk
- ⚡Connection pool exhaustion from lock wait accumulation cascades to services that share the connection pool
Temporal Behavior
- ⟳Migration duration is proportional to table size: a table that grows 10x requires migration strategy rethinking
- ⟳Replication lag from a migration persists until replicas fully catch up, which may take as long as the migration itself
- ⟳Lock wait accumulation begins immediately when the DDL statement acquires its lock and drains only after the lock releases
Boundary Implications
- ◈The schema migration is an operational boundary event: it divides the deployment timeline into pre-migration and post-migration states
- ◈The ownership boundary for migration safety must include both the application team writing the migration and the platform team managing the database
- ◈DDL lock blast radius crosses team boundaries when foreign key relationships span domain boundaries
Topology
- ·Primary datastore nodes become a hard dependency bottleneck during schema migrations: all topology paths through the primary are blocked
- ·Replica topology degrades during migration due to replication lag accumulation
- ·Foreign key relationships in the topology graph define the blast radius of a DDL lock cascade
Scaling
- ·Migration duration scales with table row count and storage size: a migration safe at 10M rows may take 30 minutes at 1B rows
- ·Lock wait queue depth scales with concurrent request rate: higher throughput systems suffer more during a DDL lock
- ·Online schema change tools (gh-ost, pg_repack) add operational complexity proportional to table write throughput
Resilience
- ·Databases that undergo frequent unsafe migrations accumulate risk of accidental production impact
- ·Expand-contract migrations increase short-term complexity but preserve system availability throughout
- ·Having a tested rollback procedure for each migration is a resilience requirement, not an optional best practice
Governance Implications
- ·Schema migrations to tables exceeding a size or write-rate threshold must be reviewed as infrastructure changes
- ·Expand-contract migration pattern must be enforced by policy for all NOT NULL column additions
- ·Migration execution plans, estimated duration, and rollback procedures must be documented before production execution
- ·DDL changes must be tested against production-scale data volumes in a staging environment before deployment
Evolution Implications
- ·As table sizes grow, migrations that were safe at launch require online schema change tooling at scale
- ·Accumulation of nullable columns and legacy constraints requires periodic table cleanup migrations, each with their own lock risk
- ·Moving to event sourcing or append-only models eliminates schema migration risk by making the schema immutable
Mitigation Patterns
- →Use expand-contract pattern: add column nullable, backfill in batches, add NOT NULL constraint separately
- →Use gh-ost for MySQL or pg_repack for PostgreSQL for large table schema changes
- →Set lock_timeout on migration sessions to fail fast rather than hold locks indefinitely under contention
- →Test migration duration against a production-size dataset in staging before scheduling the production run
- →Schedule migrations during the lowest-traffic window and have rollback ready before starting
Cross-References