TL;DR - RegreSQL 1.0 tested that your queries return the right rows. 2.0 tests that they return them the right way, and it does the checking against production's real statistics instead of your empty dev database, which lies.

A migration cleanup dropped an index nobody thought was load-bearing. Every test passed: same rows, same order, green. Three days later the API started timing out on a query that hadn't changed a character, because the planner had quietly switched it from an index scan to a sequential scan over a table that had kept growing.

The first version of RegreSQL would have passed that change too. It tests what your queries return: run them, diff the rows against a committed expected file, go red when the output changes. That catches the query that now returns the wrong rows. It says nothing about the query that returns the right rows the wrong way, which is most of what takes a database down.

Version 2.0 tests that.

Test the plan, not just the rows

Here is that failure on a laptop. An orders table, an index on customer_id, and a query that reads one customer's orders:

-- orders-by-customer.sql
select id, total
  from orders
 where customer_id = :cid
order by id;

Baseline it and run the tests. Green:

$ regresql test
  ✓ 2 passing

Now drop the index the way that migration did, and run the same tests again:

$ regresql test
  ✓ 1 passing
  ✗ 1 failing

FAILING:
  orders-by-customer.1.buffers (3898 > 109 * 102%, +3476.1%)
  Expected buffers: 109
  Actual buffers:   3898 (+3476.1%)

  Cost (info):      7962.41 (baseline: 421.03)

  ⚠️ Table 'orders': Bitmap Heap Scan → Seq Scan

Here is the whole loop, start to failure, as it actually runs:

Animated terminal recording: regresql init, baseline --analyze, and regresql test passing with 2 passing; then a migration drops orders_customer_id_idx and the next regresql test fails with orders-by-customer.1.buffers reading 3898 vs an expected 109 (+3476.1%) and table 'orders' flipping from a Bitmap Heap Scan to a Seq Scan

The output check still passes; the rows didn't change. The plan check catches what the output check can't: the same query, returning the same result, now runs a sequential scan instead of the bitmap index scan it used before, reading thirty-five times the buffers. That failure blocks the merge. And the diff names the table and the exact change, in the same shape as the row diffs you already read in code review. Plan drift stops being something you find in production and becomes something you review in a pull request.

An empty database is a liar

That demo caught the regression because the table was big enough for the index to matter. On your dev database, it usually isn't. Run EXPLAIN there and it says every query is fine, and it isn't lying on purpose: at a few hundred rows the cheapest plan really is a sequential scan, and it picks it. Production's planner reads different statistics and picks a different plan. A green suite on small data proves your queries are correct on small data, and nothing about the plan production will run. That gap, and why code review can't see it, is a story of its own.

You can't copy production data to your laptop. You don't need to. You need what the planner reads: the row counts, the histograms, the most-common-values. PostgreSQL 18 dumps and loads those on their own (pg_dump --statistics-only), and RegreSQL 2.0 injects them so EXPLAIN, against ten rows on your laptop, sees the real table's distribution:

regresql test --stats production-stats.sql

Now the plan you baseline is the plan production would pick, and a plan regression fails on your laptop before it reaches a server. That is the difference between testing your query and testing it against reality.

Gate on what happened, not what was guessed

Cost estimates are the planner's opinion. Opinions drift, and they drift most exactly when a plan is about to go wrong, because a bad plan is usually a bad estimate first. Gating a test on estimated cost means trusting the number that breaks first to tell you when it broke.

So 2.0 gates on measured behavior instead. regresql baseline --analyze runs the query for real and records what happened: buffers actually read, whether a sort or hash spilled to disk, how many rows actually flowed through each node, and the cardinality error, the ratio between what the planner predicted and what it got (the "q-error"). A query whose estimates were off by 2x and are now off by 200x has a plan that is one data change away from collapse, and the row counts say so long before the cost does. The baseline stores actuals; the test compares actuals; the estimate is never on the witness stand.

Wall-clock time is the actual everyone wants and the one that lies most, because it moves with cache state, whatever else is on the box, and luck. 2.0 measures it and treats it as evidence, not a verdict: interleaved runs, a per-query median, and a noise threshold derived by permuting the samples, so a query counts as slower only when it beats its own measurement noise. Anything that can't clear the threshold goes in an unstable bucket instead of failing the build. Timing advises; it never gates the exit code.

Knowing when you can't tell

Here is the part that took the longest to get right, and the part I am most sure of.

Point RegreSQL at the same corpus on two builds of PostgreSQL and it will show you every query that plans differently between them. Early on, one query looked like a clean regression: the newer build read nearly three times the buffers, deterministically, run after run. It had every mark of a real find.

It was not one. Both builds had ANALYZE'd their own copy of the data, and the query sat on a knife-edge join-order decision that a few unlucky sampled rows tip one way or the other. Inject one build's exact statistics into the other and they plan identically. Re-ANALYZE and the "regression" moves to the other build. It was sampling noise, and a tool with less doubt would have reported it as a regression.

So 2.0 will not make a cross-version claim on statistics it does not trust. It injects identical statistics into both builds, so any remaining difference is code and not luck, and it re-ANALYZEs the baseline several times to throw out queries whose plan is a coin-flip to begin with. A difference is reported only when it survives both. On that investigation the honest output was zero regressions, and zero was the correct answer.

A tool that only ever says "found something" is a tool you eventually stop believing. In a field full of confident dashboards, "I can't tell you that from this data" is the more useful sentence, and it is a first-class answer here, not an apology.

Testing the planner itself

Chase these problems far enough and you end up at PostgreSQL's own test suite. pg_regress, the harness that has guarded the database for decades, checks one thing: does the query return the expected text. It says nothing about the plan, the cost, or the cardinality error, because it folds all of them into a single .out text file that either matches or does not. It is correctness-only by construction, and nothing in the standard toolchain measures whether a planner change made real queries better or worse.

Everything above, separating results from plan-shape from measured actuals from cardinality error into layers that can each be judged on their own, is what you would need to answer that question. Which is why 2.0 also has the pieces that only make sense pointed at PostgreSQL rather than at your application:

  • Cross-version scoreboards with the trust filter above, so a planner patch can be measured against a corpus without drowning in ANALYZE noise.
  • A metamorphic check: flip an optimization that is supposed to preserve results (eager aggregation, memoize, incremental sort) and confirm the rows do not move. When they move, you have found a wrong-results bug in the optimizer, with no second database to compare against.
  • Determinism admission, which keeps only the queries whose result is stable across different plans, so an ambiguous LIMIT over ties never masquerades as a regression.

This is a longer road, and an open one. But the same machinery that tells you your application query got slower can tell a PostgreSQL developer whether their patch made a thousand real queries slower, and that is a thing the project cannot currently see per-change. RegreSQL started as a way to test the queries you write. It turns out the honest way to do that is most of the way to a harness for the planner underneath them.

Leave evidence

None of this is useful if it only runs on your laptop. regresql test exits non-zero on a failure, emits JUnit, GitHub Actions, or JSON, and reads DATABASE_URL so the same project runs against a CI database without editing committed config. A plan flip fails the build the way a wrong-rows change does, and the run leaves a machine-readable record of what was checked against which snapshot. The check that advised you in the editor is the same check that blocks the merge.

You can go further and name the tables where a sequential scan is never acceptable, and have that fail the build outright. But which tables those are, and which of these signals is a release blocker versus a shrug, is a judgment the planner doesn't have and never will. That part is a longer story, and a separate one.

What "verified" means

RegreSQL 2.0 is careful about the word. It does not promise your database is safe. It verifies what it can measure and says so plainly when it can't, which on the day it mattered meant reporting nothing at all. A green RegreSQL run is not "nothing will go wrong." It is "these queries return what they returned, plan the way they planned, against the statistics that actually apply." That is a smaller promise than most tools make, and one you can keep.

It runs on your laptop, against a database you already have, no production access:

regresql init postgres://localhost/yourdb

From there the SQL passes through the harness the way your code already passes through the type checker. The getting-started guide walks the whole loop, from zero to a plan check in CI, in about ten minutes.