Every production migration now runs twice. The first time, we roll it back.

MCP Checkup is a one-person company with a single production database, and every schema change lands on it directly. There is no staging cluster holding a copy of production, and a mistaken create view on a database that serves a public directory is not something you quietly undo. So each migration is now executed twice.

The shape of a dry run

Each migration comes with the exact assertions it must satisfy, written in a comment block or, lately, in a companion file. Before applying it, we run a single SQL call shaped like this:

begin;

-- the migration's DDL, pasted verbatim

set local role anon;   -- become the public, unauthenticated reader

do $$
begin
  if current_user <> 'anon' then
    raise exception '§0 FAIL: not running as anon';
  end if;

  -- §1: the view exists and exposes exactly the columns we promised
  -- §2: as anon, the view shows exactly the rows the live policies admit
  -- §3: the anon query plan and wall time are within budget
  -- ... one section per property, each raising '§n FAIL: …' on violation

  raise exception 'DRYRUN OK: anon_rows=%, exec_ms=%', ...;
end $$;

The final raise exception is not a bug. It is how the transaction is made to roll back: the only way the call can succeed is by failing, and its error message carries the measurements we wanted. If any section fails first, we see §2 FAIL instead of DRYRUN OK, and nothing has changed. Read-only queries then confirm nothing was left behind, before the real apply runs the same DDL for keeps.

Why one call rather than a script of many? Because our database tooling does not hold a transaction across calls. A dry run split over several statements would be several small dry runs with real commits in between, which defeats the point.

What the dry run is for

It is not a syntax check; the database does that for free. It exists to make three kinds of claims measurable before they are true in production.

Permission claims. “The anonymous role sees only public rows” is easy to write in a comment and easy to get wrong in a policy. Switching to anon inside the transaction and comparing what it sees with what the live policies admit, then counting, as the owner, rows that should not be there, turns the sentence into two numbers that must match and one that must be zero.

Performance claims. A view can be correct and still unusable. The dry run captures explain analyze for the anon query, and can be made to fail when it exceeds a budget.

Shape claims. Column names and their order are part of the contract with the application code that reads the view. The dry run compares the catalogue against the list the migration promises.

Two things it caught

The first was a permission-function cost. A directory view was written with security_invoker, so each row went through the row-level-security helper functions the underlying tables already had. Correct, and 467 ms for 15 targets as the anonymous role, against 17 ms for a privileged reader of the same rows. The dry run’s timing section turned “feels fine locally” into a hard number. The view was redone as a definer view with security_barrier and the same predicates written out explicitly, and measured again, as the anonymous role, at 19.3 ms.

The second was smaller and more embarrassing. The dry run’s section for “no non-public row is visible” had been predicted to be vacuous, because production supposedly had no non-public targets. It exercised against two. They were leftovers from a local development smoke test a few weeks earlier that had pointed at the production database. Nothing in the view was wrong, but a “this never happens” comment in the migration was, and it got rewritten before the file was frozen.

Frozen means frozen

Once a migration is applied, its file is byte-stable forever. We compare the committed blob’s hash, not the working-tree file, because Windows checkouts turn line endings into false differences. When a comment in a migration that had been dry-run but not yet applied needed rewording this month, the change was verified two independent ways: the comment-stripped SQL hashed identically, and a plain diff of the two blobs showed every changed line beginning with --, with the line count matching git diff --numstat. Two checks, because either one alone can be fooled by a filter that silently eats lines. We know because the first version of the second check did exactly that.

None of this is clever. It is the minimum ceremony that lets one person change a live database and sleep afterwards.