Give Every Database Query a Budget and Fail the Build When It Scans Too Much
A developer changes a login lookup to WHERE lower(email) = lower($1) so that sign-in stops being case-sensitive. Every test passes. The btree index on email can’t serve a query that wraps the column in a function, so Postgres reads the whole users table, and on a test database with forty rows that’s the cheapest plan anyway. The change ships. A week later someone notices login latency tracking the signup count. The fix is a one-line expression index that would have taken a minute if anything had objected during review.
Correctness tests can’t see this, because the results are identical. What catches it is a budget: a declared limit on what a query may cost, checked during the test run, failing the build when exceeded. The idea is right. The obvious version, a time budget like 20 ms per query, is wrong for CI, and most of this post is about what to budget instead. The tool described here is a proposal.
Milliseconds Are the Wrong Unit
Wall-clock time on a shared CI runner depends on the neighbor’s workload, the state of the page cache and how much data the fixture happens to hold. A 20 ms budget on that hardware either flakes, so people retry the job until it goes green, or gets set loose enough that it catches nothing. Either way it stops meaning anything within a month.
Budget what comes out the same on every run: queries per request, repeats of one query shape, estimated rows and node types in the plan, pages touched, bytes returned. A deterministic check fails exactly when something changed. Keep wall-clock budgets for a dedicated performance job on stable hardware, where they measure something real. Most request time goes to the database and network rather than your own code, which is where profiling keeps pointing.
Counting Is Well Served, Plan Checks Aren’t
The prior art covers counting well. Django’s assertNumQueries fails a test when a block issues more queries than declared. In Rails, Bullet and Prosopite detect N+1 patterns, and strict_loading (since Rails 6.1) raises when an association loads lazily. The GraphQL N+1 problem is the same shape seen from the API side: one query per item in a list.
On the server side, pg_stat_statements aggregates cost per statement, auto_explain logs plans for slow statements, and MySQL’s slow log reports rows examined. SQLite’s EXPLAIN QUERY PLAN says whether a step is a SCAN or a SEARCH using an index. sqlcommenter tags each statement with a comment naming the code that issued it. Those are all useful, and none of them is a budget in CI. Some teams hand-roll the missing piece, a test helper that runs EXPLAIN and asserts there’s no sequential scan, and then copy it between repos. The gap is that piece as a shared tool with declared, reviewable budgets. It’s real, and it’s small enough to start small.
How It Would Work
The tool wraps the database driver inside the test process. In Django that’s connection.execute_wrapper; in SQLAlchemy, the before_cursor_execute event. Every statement passes through with its parameters. The wrapper fingerprints it by replacing literals with placeholders, collapsing IN (...) lists to one form and stripping comments, so id IN (1,2,3) and id IN (4,5) land in the same bucket. Then it counts executions per test and per request. N+1 shows up without any ORM knowledge: one fingerprint executing forty times inside a single request.
Once per fingerprint, it runs EXPLAIN (FORMAT JSON) with the parameters from the first execution. Plain EXPLAIN doesn’t execute the statement, so it’s safe for writes. The JSON carries node types and estimated rows, which is what the plan-shape rules read. For pages touched and sort spills, run EXPLAIN (ANALYZE, BUFFERS) inside a transaction the test rolls back. Shared buffer hits plus reads count the pages a query touched however warm the cache was, and at the default 8 kB page a 5 MB budget is about 640 pages. Pages are a better stand-in for “how much work” than anything measured in seconds.
Where do budgets live? A comment tag on the query, a decorator on the function, or a file keyed by fingerprint are the three candidates. Comments fight with ORMs, which rarely let you attach one per query. Fingerprints multiply when an ORM builds optional filters, because one endpoint can produce a dozen shapes. So key budgets by origin, meaning the route or function name that a sqlcommenter-style tag can carry, and let one budget cover every shape that origin produces. A file like this holds them (the format is illustrative):
[defaults]
max_queries_per_request = 15
max_repeats_per_fingerprint = 3
[tables]
users = "large"
orders = "large"
["POST /login"]
max_queries_per_request = 4
["users.by_email"]
allow_seq_scan = false
max_pages = 20
A failure should print what a reviewer needs and nothing else:
FAIL tests/test_login.py::test_login (POST /login)
budget exceeded: users.by_email (fingerprint 7f3a9c)
Seq Scan on users, table declared large in budgets.toml
Filter: (lower((email)::text) = lower('[email protected]'::text))
planner was told to avoid sequential scans and still chose one,
so no existing index can serve this predicate
issued at app/auth/queries.py:48
A Forty-Row Database Can’t Fail
The planner chooses plans from table size and column statistics, and a CI database holds a few dozen rows, so it picks a sequential scan, the scan is fast, and every budget passes. The check is worthless unless the planner can be made to behave as if the table were large. There are three ways to get there.
The first is realistic data. Generate rows at production scale in the CI database (cache the volume between runs) and the planner sees what it would see in production.
The second is restoring statistics. Postgres 18 added functions that restore planner statistics (pg_restore_relation_stats and pg_restore_attribute_stats), and its dump and upgrade tools can carry statistics along, so a small CI database can borrow production’s column histograms and most-common values. Selectivity guesses then match production’s. The planner also looks at how many pages the table occupies on disk, though, so treat the resulting plans as a close sketch and compare a few against production before trusting them. On older versions, use the first option or the third.
The third makes the planner honest instead of making the database big. Run the check session with SET enable_seqscan = off. That setting doesn’t forbid sequential scans; it makes them expensive enough that the planner uses any index that can serve the query, even on a forty-row table. If the plan still shows a Seq Scan on a table you’ve declared large, no usable index exists. That’s exactly the lower(email) case, and it works against an empty database. It can’t catch an index scan that returns two million rows because the predicate is barely selective. That needs real statistics or real data, which is why row and page budgets are only as good as the test data behind them.
Parameter-sensitive plans are the next trap. The same fingerprint can plan differently for different values, because one customer owns two million orders and another owns three. Give the fixtures one oversized tenant and budget the query for that parameter, not whichever one happened to run first.
Budgets also drift. Someone raises a limit to get a build green and nobody revisits it. Treat budgets like snapshot files: a record subcommand rewrites observed values into the file, and a pull request that raises a budget shows up as a diff a reviewer has to accept. A change that makes a query more expensive on purpose is fine. One nobody noticed is the thing being caught. Tightening can be automatic; loosening can’t.
What Version 0.1 Does and Refuses
Version 0.1 supports Postgres and SQLite, Python and one other language (Node is the likelier pick). It counts queries and repeats per test and per request, checks plan shape with the enable_seqscan trick, enforces page budgets on Postgres, reads a budgets file and prints failures a CI log can show, plus a JSON file for annotations. SQLite support is thinner because EXPLAIN QUERY PLAN reports SCAN or SEARCH with no row estimates, so it gets the shape checks and nothing more.
It refuses production monitoring. pg_stat_statements and auto_explain already watch a live server, and a CI tool that grows a daemon has stopped being small. When a budget fails and the fix isn’t an index, because the query is legitimately expensive, the better answer may be to stop running it live, and precomputed answers turn an expensive dashboard query into a lookup. When the slow query is already in production, break one request into DNS, connect, TLS, server wait and transfer before guessing. The statistics snapshot that feeds these budgets could also feed a migration risk report, since both need to know how big the tables really are.
Let the build complain before your users do.