Zero-Downtime Schema Migrations at Scale (gh-ost / pt-online-schema-change)

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.