A Schema Diff That Warns Which Migration Will Lock a 40-Million-Row Table or Lose Data
The diff in the pull request is one line: ALTER TABLE orders ALTER COLUMN total TYPE bigint;. It reads like a rounding fix, so the reviewer approves it, and the SQL is correct. On a table with 41 million rows it’s also a full rewrite under a lock that stops every read and write until it finishes. A diff shows what changes, never what the change costs.
A schema diff worth using would print consequences instead of SQL, in sentences like “this rewrites a 41-million-row table and blocks every read and write” or “the rollback is impossible without a backup”. Those sentences depend on three things: the statement, the engine and its version, and the size of the table it lands on. With all three you can post a risk report on every migration pull request. With only the first you have a linter.
The linters are real and good. Squawk lints Postgres migrations, Atlas lints them as part of schema-as-code, strong_migrations does it for Rails, and pgroll runs Postgres schema changes as reversible, zero-downtime steps. On MySQL, gh-ost and pt-online-schema-change exist for the changes too heavy to run directly. Most of these judge the statement alone, so a SET NOT NULL on a 400-row table gets the same verdict as one on a 400-million-row table. The gap is a report that joins the rule knowledge to real table statistics and states the consequence in plain language.
From Statement to Consequence
Start with the migration files, parsed by a real SQL parser. For Postgres that can be libpg_query, the server’s own parser packaged as a library; a regex on ALTER TABLE holds up until the first quoted identifier. The output is an operation list: kind, table, column, old and new types, options. Diffing two schema dumps is the tempting alternative, and it has a flaw you can’t fix. A diff can’t tell a rename from a drop plus an add, and one is an instant metadata change while the other destroys a column of data. Migration files carry the intent, so they’re the primary input. Diffs stay as a fallback, with the intent marked unknown. ORMs that generate SQL at deploy time need an extra step: render the SQL first (Django’s sqlmigrate and Alembic’s offline mode do this) and feed that in.
Each operation goes through a rule table keyed by engine, version range, operation kind and a predicate on the details. A rule returns effects: lock level, rewrite or scan, what’s blocked, reversibility, compatibility with the previous release, and a suggested alternative. Since version 11, adding a column with a non-volatile default doesn’t rewrite the table, while a volatile default does. A type change rewrites unless it’s binary-compatible, like raising a varchar limit or going from varchar to text. CREATE INDEX blocks writes unless it’s CONCURRENTLY. SET NOT NULL scans the table, unless a valid CHECK (col IS NOT NULL) constraint exists and the server is Postgres 12 or later. A foreign key can go in as NOT VALID and get checked afterwards with VALIDATE CONSTRAINT. MySQL’s table is organized around the algorithm: INSTANT, INPLACE or COPY, with instant ADD COLUMN since 8.0.12 for the last position and 8.0.29 for any position. Spelling it out (ALGORITHM=INSTANT) makes MySQL refuse the statement instead of quietly copying the table, which is the suggestion to make.
Then the size. The report needs row estimates, table and index bytes, and the server version. pg_class.reltuples and the size functions give that on Postgres, information_schema on MySQL. Production credentials don’t belong in CI, so a scheduled job inside the production network exports a small JSON snapshot of table names, estimates, sizes and index lists, and the check reads that file. No row data leaves the database. The estimates go stale after a bulk load until the next analyze, which is fine, because the report says “about 41M”. Taking the version and sizes from the snapshot also fixes the classic failure where staging holds 10,000 rows and everything looks instant. Snapshots kept over time also show how fast orders grows, which suits a store built around change history.
Rule tables go stale, so every rule needs a test that runs the statement against real engine versions in containers. Postgres helps here: an event trigger on table_rewrite fires just before an ALTER TABLE rewrites a table, so a scratch run of the migration can confirm the rewrite verdict from the engine itself.
Here’s the shape of the comment, with made-up sizes:
### Migration risk: 2 blocking, 1 warning
PostgreSQL 15, stats snapshot from 2026-10-04, runner wraps each migration in a transaction
**BLOCKING** `0042_orders.sql` line 3
`ALTER TABLE orders ALTER COLUMN total TYPE bigint;`
- Full rewrite of `orders` (about 41M rows, 18 GB) plus a rebuild of its 4 indexes (7 GB).
- Takes ACCESS EXCLUSIVE for the whole rewrite: reads and writes to `orders` wait.
- WAL volume is proportional to the table, so replicas will lag while they replay it.
- Rollback is another full rewrite, and fails once any value exceeds the integer range.
- Suggest: add `total_big`, backfill in batches, switch the app over, drop `total` in a later release.
**BLOCKING** `0042_orders.sql` line 7
`CREATE INDEX idx_orders_created ON orders (created_at);`
- Blocks writes to `orders` for the whole build (about 41M rows).
- Suggest: `CREATE INDEX CONCURRENTLY`, which cannot run in a transaction. Configure this file to run outside one.
**WARNING** `0043_customers.sql` line 2
`ALTER TABLE customers DROP COLUMN fax;`
- Metadata-only change, but it still takes ACCESS EXCLUSIVE. No `lock_timeout` is set, so it can queue behind a long transaction and stall `customers`.
- Data loss: the values in `fax` are unrecoverable without a backup. The down migration restores an empty column.
- Breaks the previous release if it still reads `fax`. No reference found in this repo (214 files searched).
Notice what’s missing: minutes. Duration depends on disk speed, concurrent load, replica lag and vacuum state, and the tool knows none of them. It can report rows, bytes rewritten and indexes rebuilt, and the reviewer, who knows the hardware, can tell a coffee break from a maintenance window.
The Lock Queue Does the Damage
An ALTER TABLE that needs ACCESS EXCLUSIVE has to wait for every transaction already touching the table. While it waits, every new query on that table queues behind it. So a metadata-only change that would finish in milliseconds can freeze the table for as long as the oldest open transaction lives, such as a forgotten report query or a session stuck idle in transaction. The report should flag every statement that takes ACCESS EXCLUSIVE and check whether SET lock_timeout comes first, so the migration gives up and retries instead of holding up the line.
Transactions make it worse. Locks last until commit, so a migration that adds a column and then backfills 41 million rows in the same transaction holds the exclusive lock through the whole backfill. Each step is safe alone. Together they aren’t. The tool has to read statements in order, and it has to know whether the runner wraps migrations in a transaction, because CREATE INDEX CONCURRENTLY fails inside one. That’s a runner setting the SQL says nothing about, so it goes in the tool’s config.
Data Loss Hides in Casts and Down Migrations
Narrowing a type can fail or change values, depending on the engine and, on MySQL, the SQL mode. Postgres refuses to shrink varchar(255) to varchar(50) when any value is longer. Dropping numeric(12,2) to numeric(12,0) is worse: it rounds every price to a whole number without a complaint. MySQL’s answer to an over-long value depends on strict mode, an error in one configuration and a truncation with a warning in another. So the rule table needs separate entries for “fails loudly” and “changes values quietly”.
Drops are the other class. DROP COLUMN in Postgres is quick, because it only hides the column, but the values aren’t coming back without a backup. Before approving one, somebody should know whether anything still reads the column, and a grep won’t settle that: it takes code and traffic together. Renames are a compatibility problem. The change is instant, but during a rolling deploy the previous release is still running and still uses the old name. The report should say “breaks the previous release” for renames and drops, which is the database version of shipping a breaking change in an API.
Down migrations deserve suspicion too. One that re-adds a dropped column restores the structure and none of the data, and then the file looks reversible. The report should label it “rollback restores schema only”.
Version 0.1 and Who Pays
Version 0.1 is a CLI for Postgres only. It reads migration SQL files, applies about twenty rules, reads a stats snapshot, and prints Markdown for a pull request comment, which a thin GitHub Action posts. MySQL comes next. The tool refuses to run migrations, generate them, auto-fix them or promise durations; it suggests, and a person edits. When a table has no snapshot entry, the tool says so and assumes the worst.
The commercial shape is the usual one for pull request tooling: a GitHub App, free for public repositories, paid for private ones and for connectors that fetch statistics without a hand-rolled export job. Query budgets in tests are the read-side sibling: they fail the build on slow queries the way this fails it on risky migrations.
A false alarm costs a minute. A missed rewrite costs an outage.