Online Schema Change
Online schema change alters table structure while concurrent reads and writes proceed, by building a shadow copy and replaying changes, instead of locking the table for the full rebuild.
Definition
Online DDL and change tools like gh-ost or pt-online-schema-change rebuild the table incrementally: create a new structure, copy rows in chunks, capture ongoing modifications via triggers or binlog, and atomically swap names. Applications see brief metadata locks rather than hours of exclusive locks.
Context and scope
Required for large production tables where maintenance windows do not exist. Also where replica lag, disk headroom, and trigger overhead must be budgeted.
Common misconceptions
- Online means zero impact — concurrent DML is allowed, but throughput drops, replicas lag, and disk nearly doubles during the copy
- The final swap is instant — the rename takes a metadata lock that queues behind long-running queries and can stall everything
- Any migration can run online — multi-step changes with data backfills and constraint validation need staged approaches
Anti-patterns
- Starting a large online change during peak traffic and saturating disk or replication bandwidth
- Letting an hours-long transaction hold the metadata lock queue and freezing the table at the final rename
- Adding a constraint as enforced immediately on a huge table instead of validating it incrementally after creation
Related concepts
Related examples
Authoritative source
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html
What it is
Rebuilding a table to change its schema once meant locking it for the whole duration. Online approaches decouple: build the new shape out of band, stream live changes into it, and cut over in a moment. The table serves traffic while the rebuild grinds.
How it works
MySQL InnoDB native online DDL copies rows with row logs replayed; gh-ost reads the binlog instead of triggers, throttling itself against replica lag. PostgreSQL takes MVCC-level locks only at commit for many operations and offers CONCURRENTLY index builds that avoid exclusive locks. The final rename or commit still needs a brief exclusive window.
When to plan
Any table too large for a lock-window rebuild. Budget disk for the shadow copy, replica lag headroom, throttling thresholds, and a rollback story — the swap point is the moment of no easy return.
Pitfalls
The metadata lock at cutover queues behind any long transaction; kill or reschedule those first. Chunked copies that ignore primary-key hotspots can saturate a single page. Instant algorithms for some MySQL changes changed the calculus — check which operations are still full rebuilds in your version.