migrate-safe

Find out what a PostgreSQL migration will do to a live database before it ships. It runs the migration on a copy while application traffic keeps going, and records the locks it held, whether it rewrote or scanned a table, and how long reads and writes were locked out.

Most migration outages come from statements that look harmless. CREATE INDEX is one line. A default of gen_random_uuid() looks like a default of 0, but one rewrites the table under its strongest lock and the other only touches the catalog. A linter predicts the same facts in milliseconds without a database, and CI checks every prediction against a real server.

View on GitHub Six migrations, measured

Plays the seven stages below in order, about 60 seconds. Scroll or press Esc to stop.

Language: Go 1.24 or newer, one dependency: pgx
Tests: 40 passing against PostgreSQL 16 and 17 (28 tests plus 12 subtests)
Commands: lint, measure, seed and examples
Checked: 67 statements whose predicted locks, rewrites and scans must match the server exactly
Examples: 6 migrations, as usually written and rewritten not to block
License: MIT

migrate-safe lint examples/migrations/03-set-not-null/unsafe.sql examples/migrations/03-set-not-null/unsafe.sql (PostgreSQL 17)

Real runs of migrate-safe at 88abaec, output verbatim, against PostgreSQL 17.11 in Docker with 5 million orders. $DSN stands for that server's URL. The measure runs print their progress first and their report after.

1Predict every statement, without a database

migrate-safe lint reads a migration and tracks the schema and the session's lock_timeout as the migration changes them. For every statement it predicts the lock on each table, and whether the statement rewrites or scans the table while holding it.

DANGER: it holds a lock that blocks reads or writes while doing work that grows with the table: a rewrite, a scan, or row locks on every row.
RISK: it asks for a lock that blocks reads or writes with no lock_timeout, so a long transaction can make it wait while traffic queues behind it.
Inside BEGIN ... COMMIT locks accumulate: an ACCESS EXCLUSIVE taken by a cheap statement is still held by the slow one after it.
It is not a SQL parser, and it says so. A statement it doesn't understand is reported as not analysed, never as safe. Exit code 1 on any DANGER or RISK.

Statements that look alike, with and without a schema Illustrative input

The migration was written for this page, against the repository's demo schema. Each block is real migrate-safe lint -v output for that statement, verbatim; the summary line is the run's last line. Without --schema the linter doesn't know note is varchar(100), so it assumes the type change rewrites.

2Know which lock blocks what

PostgreSQL has eight table lock modes. A plain SELECT takes ACCESS SHARE and an INSERT, UPDATE or DELETE takes ROW EXCLUSIVE, so the question for any migration is which of its locks conflicts with those two.

Only ACCESS EXCLUSIVE blocks reads. Most ALTER TABLE forms take it.
Four modes block writes. CREATE INDEX and ADD FOREIGN KEY are among them, which is easy to miss in a test environment where nothing is writing.
The linter's conflict table is the one in the PostgreSQL manual. All but one of the modes below were reported by the server in this page's runs.

Eight modes, against a reader and a writer

Modes and conflicts are from internal/lint/modes.go. The migration statements listed are ones pg_locks reported in this page's 5 million row run; SELECT, UPDATE and INSERT are the workload's own queries. None of the examples takes EXCLUSIVE.

3Copy the database, then replay its traffic

migrate-safe measure never runs on the database you point it at. It copies it with CREATE DATABASE ... TEMPLATE, starts a workload against the copy, waits two seconds for a baseline, then runs the migration one statement at a time.

Point it at a restored copy of production, or a staging database with production-sized tables. --in-place runs on the database itself, for one you can throw away.
The workload is a file of query shapes with random keys: a class per kind of query, each with its own workers, and a pause between one worker's queries.
Every workload query's start and end is recorded, so the report can say how long each kind of query was stuck during each statement.

The repository's workload, and a run starting

examples/workload.sql

Workers on the copy

The workload file is the repository's, without its opening comment. The terminal is the start of the measure run in the hero: its progress lines, then the first lines of its report, where the baseline p99 for each class was taken before the migration began.

4Measure what the server actually did

Each statement runs inside a transaction, and pg_locks is read for the migration's backend before COMMIT, so every lock it held is still there. A lone statement holds its locks until it commits either way.

Rewrite: the table's relfilenode before and after. A rewrite writes the table to a new file.
Scan: seq_scan in the transaction's own counters, before and after.
Locked out: every 20 ms another session tries to take each table's read lock and write lock with NOWAIT, and rolls back. It counts only time lost to table locks.
Statements that refuse to run in a transaction, such as CONCURRENTLY, are sampled from another session every 5 ms instead.

CREATE INDEX on 5 million orders, as written and CONCURRENTLY

Both runs are the hero's measure runs on PostgreSQL 17.11 with 5,000,060 orders and the workload above; the tracks are drawn from each report's "took" and "locked out" figures. The concurrent build took longer and never stopped a write.

5Catch the statement that waits in line

Adding a column with a constant default touches no rows on PostgreSQL 11 and later. It still needs ACCESS EXCLUSIVE for a moment. If a report has the table open, the ALTER waits for it, and every query that arrives after the ALTER queues behind it.

A directive, -- migrate-safe: long-reader orders 8s, opens a transaction that holds ACCESS SHARE on orders for 8 s, starting 1 s before the statement.
A NOWAIT probe fails when a conflicting lock is held and also when one is queued ahead of it, so the probes see the pile-up, not just the lock.
With lock_timeout = '200ms' the ALTER gives up with SQLSTATE 55P03 instead, and expect-error 55P03 lets the run continue. Retrying it later is the deploy tool's job.

A metadata-only ALTER behind an 8 s report

The hero's two lock-queue runs, 5 million orders, PostgreSQL 17.11. The tracks are drawn from the reports' wait, took and locked-out figures; the terminal is the rewritten run's statement block, verbatim.

6Hold the linter to the server

Every measurement is compared with the linter's prediction for the same statement: the strongest lock on each table, which tables were rewritten, which were scanned, whether DML touched every row, and, behind a long transaction, whether the statement waited.

CI runs a corpus of 67 statements of every kind the linter knows, one after another on PostgreSQL 16 and 17. Each prediction must match pg_locks, relfilenode and seq_scan exactly.
It also runs every example as written and rewritten under traffic, and checks the measurement itself: a transaction holding ACCESS EXCLUSIVE for one second must show about one second locked out.
The corpus caught two of the linter's own mistakes while it was being written, one of them that DROP TABLE on a table with a foreign key also locks the table it references.

The rewritten column-type change, statement by statement

Predictions are migrate-safe lint --json for 05-change-column-type/safe.sql; measurements are the same file in this page's 5 million row examples run. The statements inside BEGIN ... COMMIT show the lock held by then. The 1.07 s wait is autovacuum, below.

ShippedAbove this line everything runs on a copy or on paper, and nothing reaches production. Below it, a person ships the rewritten migration, and CI keeps the unsafe version out.

7Ship the rewrite, and let CI hold the line

Each example is a change as it is usually written and the same change rewritten so it doesn't block traffic. The linter's verdict for every rewritten version is "nothing found", and the measurements show why.

A rewrite holds ACCESS EXCLUSIVE for as long as it takes to copy the table. The rewritten versions add the column empty, fill it in batches of 20,000 rows that each commit, and swap columns in one short transaction.
The safe versions often take longer in all. What they don't do is stop traffic for long.
lint exits 1 on DANGER or RISK, so it can fail a pull request. --fail-on danger lets a RISK through when a team has decided to accept it.

Longest lock-out on a table, as written and rewritten

As usually writtenRewrittenLog scale, 10 ms to 1 min

From this page's migrate-safe examples --rows 5000000 --check run: the larger of reads and writes locked out, and the whole run's duration. The terminal is a real run of --fail-on danger on the lock-queue example, whose only finding is a RISK.

Measured, not assumed

The README's 5 million row run, repeated on the same model of CPU, an i5-12400F, while other jobs shared the machine. PostgreSQL 17.11 in Docker, ten workers the whole time.

The same locks, different seconds

Locked out, as written, in the README's run and in this one.

Every verdict and every lock, rewrite and scan came out the same, and the linter agreed with all of it both times. The durations did not: they scale with hardware and load, as the README says. Treat them as orders of magnitude.

Autovacuum holds locks too

After the column-type backfill wrote 5 million rows, autovacuum started on orders. The next statement, a metadata-only ADD CONSTRAINT ... NOT VALID, waited for its lock:

README: 1.00 sHere: 1.07 s

That is deadlock_timeout, 1 s on this server, after which PostgreSQL cancelled the autovacuum. Its log said so:

ERROR:  canceling autovacuum task
CONTEXT:  while vacuuming index "orders_pkey" of relation "public.orders"

What it costs

Wall time for examples, all twelve runs with --check, on a loaded machine.

200,000 rows93 s

1 million141 s

5 million685 s

The README estimates about 3 minutes at 1 million rows and 15 at 5 million. At 5 million rows the slow parts are the two batched backfills, 251 batches each.

40 tests, most of them against a real server

Counted with go test -race -count=1 -json ./... and MIGRATE_SAFE_TEST_DSN pointing at PostgreSQL 17.11, then again at 16.15: 28 tests plus 12 subtests, all passing on both. The subtests are the examples, one per file. Without a database, go test -short ./... runs 25 and skips 3, in about 3 seconds.

Weighted toward agreement with the server

Corpus: 67 statements on a real database, each prediction matched against pg_locks, relfilenode and seq_scan. Both servers reported "67 statements agree".
Examples: all twelve files under traffic, one subtest each, including the lock-queue wait and its pile-up.
The measurement itself: a one-second ACCESS EXCLUSIVE must show about one second locked out, in the probes and in the workload.
Linter: statement effects, version rules for PostgreSQL 10, 11 and 12, severities, locks accumulating in a transaction, lock_timeout tracking, what is not analysed.
Parsing and CLI: quotes, dollar quoting, comments and directives; exit codes, flags after files, JSON output.

CI runs gofmt, vet and the race-enabled tests against PostgreSQL 16 and 17 services, then examples --check at 200,000 rows, plus a job on the newest Go without a database. The run on 88abaec passed. Two earlier runs failed on PostgreSQL 17 only, one on a commit that changed nothing but the README; the last commit tightened the lock sampler.

Deliberately missing

Production's behaviour: A restored copy has production's data, not its cache, hardware or traffic. The locks, rewrites and scans carry over; the durations don't.
Captured traffic: The workload is query shapes with random keys that you describe, not a recording.
Row locks in the probes: A row lock held by a batch shows up in workload latency, not in "locked out".
Retries: After a 55P03 the right move is to retry later. That is the deploy tool's job.
Everything SQL: Partitions, ALTER TYPE, materialized views, GRANT and DO blocks are reported as not analysed. WAL volume, replication lag and disk space aren't measured. Servers older than 16 weren't run.

Run it

git clone https://github.com/ahmed-hashim-pro/migrate-safe
cd migrate-safe
make build

# No database needed:
./bin/migrate-safe lint examples/migrations/*/unsafe.sql

# A throwaway PostgreSQL 17 on port 55432:
make pg
./bin/migrate-safe examples --rows 1000000 \
  --dsn postgres://postgres:pg@localhost:55432/postgres
make pg-stop

Needs Docker for the database. Go 1.24 or newer is used if it is installed; otherwise the Makefile builds in the golang:1.25 image.