The release added one nullable column and an index to the orders table. It had been reviewed, it ran in under a second in staging, and it was bundled into the deploy the way every migration had been for three years: the container starts, runs migrations on boot, then serves traffic. At 10:40 on a Wednesday it took the write path down for eleven minutes.
Two things collided. The CREATE INDEX was not concurrent, so it took an ACCESS EXCLUSIVE lock on a table with forty million rows. And Postgres queues locks: once that statement was waiting behind a long-running analytics query that had already been reading the table for two minutes, every subsequent query, including simple reads, queued behind the waiting writer. The table was not locked by us for eleven minutes. It was locked for two by someone else, and then unavailable for nine while our statement did its work with everything else piled up behind it.
The deploy made it unrecoverable in the obvious way. Migrations ran in the application's startup path, so the pods were not serving. Rolling back the deploy would not undo a partially built index, and stopping the migration meant killing the pod that was running it. We had no safe move except waiting.
What changed afterwards is mostly about decoupling. Migrations are now a separate pipeline stage with its own approval, run before the application deploy rather than inside it, so a schema change and a code change are two decisions with two rollback stories. Every migration sets lock_timeout to three seconds and statement_timeout to a bounded value, so a blocked statement fails fast and retries instead of holding a queue open. Index creation uses CONCURRENTLY, which cannot run inside the transaction our migration tool wrapped everything in, and that constraint alone forced us to split the tool's behaviour per migration.
The rule we adopted is expand and contract: schema changes are always backwards compatible with the currently deployed code, deployed separately, and the cleanup migration ships a release later. It is slower. It means a column removal takes two weeks. It also means no deploy has ever again been unable to go backwards.
– Sergey Shinder
Top comments (0)