Schema Evolution & Lock Analysis

Zero-downtime schema evolution engine

A DDL linter, safe-migration rewriter and lock-queue simulator for PostgreSQL, written in Go with no dependencies outside the standard library.

It answers one question that comes up on every team that has ever taken an outage from a migration: is this statement safe to run right now, and if not, what is the version of it that is?

$ evolve lint --version 16 migrations/0042_add_status.sql
migrations/0042_add_status.sql:3  REFUSE  constraint-builds-index-inline
  adding a UNIQUE or PRIMARY KEY constraint builds its index under ACCESS EXCLUSIVE
  fix: CREATE UNIQUE INDEX CONCURRENTLY first, then ADD CONSTRAINT ... USING INDEX

Everything in docs/results.md is generated by go run ./cmd/evolve. Fourteen predictions were written down before the measurements were taken; thirteen held, one did not, and the one that did not is the most interesting section in the document.

The story

The received wisdom about migrations is "avoid the statements that take ACCESS EXCLUSIVE". This is wrong twice over, and both errors are expensive.

The first error is that almost every ALTER TABLE subform takes ACCESS EXCLUSIVE, including the ones everyone considers harmless. SET DEFAULT takes it. DROP COLUMN takes it. ADD COLUMN takes it. If the rule were "avoid ACCESS EXCLUSIVE" you could never alter a table again. The lock mode is not the variable.

The second error is that the damage is not done by holding the lock, it is done by waiting for it. PostgreSQL's lock queue is ordered. A statement waiting for ACCESS EXCLUSIVE sits at the head of that queue, and every query arriving behind it waits too — including plain SELECTs that conflict with nothing currently held. One long-running read plus one short DDL is enough to stop all traffic to a table, and the DDL is not the part that was slow.

The simulator measures this directly. A three-second ALTER TABLE, behind a forty-five-second read, at eighty-five requests per second, produced 10,965× amplification: 32,896 blocked query-seconds from three seconds of held lock. The same statement with the long read removed still produced 132×. That second number contradicted the prediction, and the explanation for why is in §2 of the report.

From there the tool follows the consequences:

• If waiting is the problem, lock_timeout is the fix — so §4 measures what it costs, and §5 finds the retry budget below which the migration silently never lands.
• If the lock modes are not a ladder, then "escalate one step" is unsound — §6 proves ACCESS EXCLUSIVE is the unique mode that dominates all others, and finds two ordered pairs where the weaker mode is the one that conflicts.
• If a statement's safety depends on the server version, then a linter without a version is guessing — §7 runs the same script against PostgreSQL 10 through 16 and the verdict changes.
• If backfills have to be throttled, then someone has to pick a batch size — §9 through §12 show that no fixed size is right, and that AIMD beats both the cautious and the aggressive choice on the metric that matters.

What is in here

Running it

go test ./...          # 201 tests
go run ./cmd/evolve    # regenerates docs/results.md
.\test.ps1             # full gate: build, vet, tests, mutation sanity, report reproducibility
.\demo.ps1 -Section 2  # walk one section of the report

Go 1.23 or later. No external dependencies, no database, no network.

The part worth reading

docs/portfolio/04-bugs-the-experiment-found.md documents the twelve real defects that writing the measurements uncovered in the model itself — including a linter that refused the exact fix it recommends, a rewriter that emitted the dangerous statement labelled as the safe one, and an identifier normaliser that stripped public. from a table name and then echoed the stripped name back out as generated SQL.

None of them were found by unit tests. All of them were found by asking the model a question it had not been built to answer.