A sub-second migration, a 15-second outage

Code can be rolled back in seconds. Data can’t. A schema change is a code change that also rewrites the shape of everything already stored, while the old code is still running and reading it. That’s why changing a database under a live system has its own pattern, and its own very quiet way of taking a site down.

Expand, migrate, contract

Danilo Sato’s name for the pattern is parallel change, in three steps: expand to support both the old and the new shape, migrate everything onto the new one, then contract by removing the old. Renaming a column becomes: add the new column, write to both, backfill, move reads, stop writing the old one, drop it. Each step can be deployed and rolled back on its own.

Stripe described the same shape for moving data between stores: dual-write to both, change reads to the new one, change writes, then remove the old data, while keeping its services running throughout. Pramod Sadalage and Martin Fowler call the time when both shapes exist the transition period, and it can last months. The contract step is the one teams skip, and the reason the old column is still there years later.

The trap: waiting, not running

GoCardless added foreign keys to new, empty tables that nothing used yet. The new tables referenced busy existing ones. Run against a copy of the database, the migration took a few hundred milliseconds. In production, the API went down for about 15 seconds.

The reason is the lock queue. In PostgreSQL, ALTER TABLE takes the strongest lock, ACCESS EXCLUSIVE, unless the documentation says otherwise for that form, and a plain SELECT takes ACCESS SHARE, which conflicts only with ACCESS EXCLUSIVE. A long-running read on one of those busy tables was holding ACCESS SHARE, so the ALTER had to wait. (Since PostgreSQL 9.5, adding a foreign key takes a weaker lock than it used to; the queueing works the same way for any statement that needs ACCESS EXCLUSIVE.) And while it waited at the front of the queue, every ordinary query that arrived after it had to wait behind it. In GoCardless’s words: “having one sat in the queue blocks all other operations”. By default a lock request waits indefinitely.

Try it

One table, an app sending a query every 100 ms, and a report that keeps a transaction open for 12 seconds. The locking rules are PostgreSQL’s; the workload is invented. Each bar is one second, showing how many app queries are stuck at that moment.

Versions change the answer

Whether adding a column rewrites the table depends on the default and on the version:

• Since PostgreSQL 11, adding a column with a constant default no longer rewrites the table.
• A volatile default, like clock_timestamp(), still rewrites every row while holding the lock. lock_timeout doesn’t help here: the lock is granted at once and then held for the whole copy.
• CREATE INDEX CONCURRENTLY avoids blocking writes, but if it fails it leaves an invalid index behind. Queries ignore it, writes still maintain it, and if the failure came in its second scan, an invalid unique index still enforces uniqueness: the index exists, does nothing for reads, and still costs on every write.

MySQL teams hit the same wall from a different side. GitHub built gh-ost because trigger-based tools for online schema changes caused, in its words, “near or complete lock downs in production”.