Zero-Downtime Schema Migrations at Scale (gh-ost / pt-online-schema-change)
Alter massive tables without locking: The Expand/Contract pattern, shadow tables, binlog streaming, and preventing metadata locks.
01.Why `ALTER TABLE` Kills Production Systems
In traditional relational databases (MySQL, PostgreSQL), executing an ALTER TABLE statement (e.g., adding an index, changing a column data type, or renaming a column) requires acquiring an exclusive Metadata Lock (MDL) or an exclusive table write lock.
When executed on a table containing 100M+ rows:
- The database engine rewrites the entire physical table on disk, which can take hours or even days.
- While the table lock is held, all incoming application writes and concurrent transactional reads are blocked in a waiting queue.
- Application thread pools instantly fill up with blocked connections, connection limits are breached, and the entire system cascades into a full-scale outage.
Even in modern PostgreSQL with ADD COLUMN ... DEFAULT NULL or CREATE INDEX CONCURRENTLY, long-running background queries can block the acquisition of brief metadata locks, creating queue pileups.
gh-ost Ghost Table Online Schema Migration Pipeline 👻
gh-ost Ghost Table Online Schema Migration Pipeline 👻
Altering a 100-million row table with zero table locks using binary log streaming.
Unlock Topic #67: Zero-Downtime Schema Migrations at Scale (gh-ost / pt-online-schema-change)
You are viewing a preview. The full in-depth engineering deep dive, interactive simulators, architecture flowcharts for this topic, along with self-assessment quizzes, are available with Pro or Lifetime Access.
Failure modes, high-throughput bottlenecks, and real FAANG implementation decisions.
Interactive system topology diagrams, live parameter simulators, and downloadable SVG charts.
Staff-level multiple-choice quiz questions with instant feedback and answer explanations.
Firebase Google authentication automatically syncs your completed topics and quiz scores.
How clear and actionable was this distributed systems breakdown?