Database Migrations at Scale

Prerequisites: Indexing and Query Optimization; Replication and High Availability.

Most ALTER TABLE operations — including ones that feel harmless, like adding a column — acquire an ACCESS EXCLUSIVE lock on the table for at least a brief moment. While that lock is held, every other query against the table, including plain SELECTs, queues up and waits. On a small table this is invisible; on a table with a few million rows and constant traffic, an operation that requires scanning the whole table while holding that lock (adding a column with a volatile default, changing a column's type, adding a NOT NULL constraint the old way) can hold it for seconds to minutes — long enough for connection pools to saturate and the whole application to appear down, even though nothing actually crashed. This project is about the specific, well-established techniques that avoid that failure mode entirely.