On a Tuesday release window, an analytics agent proposed a four-join reporting query against a 40 million row events table. The planner estimated a few thousand cost units because the most selective predicate still used last week's statistics. Staging accepted the plan, then the first canary scanned far more heap pages than any review comment had predicted. Promotion, not generation, became the failure mode: the model wrote plausible SQL that static checks could not refute.
This article treats that incident as a decision problem rather than a prompt-engineering story. Two credible camps now argue about the last gate before agent SQL reaches a shared database. One camp trusts PostgreSQL cost estimates as a cheap, lock-free rejector. The other camp insists on timed canaries against representative data, because cost units are not latency and because skew defeats the planner.
The sections below compare both positions with a small, labeled harness you can run. The harness is a proposal, not a production benchmark, and it records estimates and wall time without claiming a universal SLO. Reader value sits in the decision rule; any named tool is optional and removable.
Why promotion is the bottleneck that tests miss
Agent SQL usually fails after it already looks reviewable in a diff. The join graph compiles, the column names exist, and a unit fixture with ten rows returns the expected shape. Those tests do not encode correlation, TOAST size, or the histogram that autovacuum has not updated since the last backfill.
Evaluation suites lose bite when models learn the shape of the suite rather than the shape of production data. Query promotion has the same failure mode, only with page cache and random_page_cost instead of exam items. A gate that always passes is not a gate, and a gate that never runs the statement cannot see I/O.
The practical question is therefore narrow. Which signal is allowed to veto a parsed, linted candidate, and when is that signal too expensive to collect on every agent attempt?
Position A: Planner cost estimates as a promotion gate
Cost-based gates start from a simple operational fact: EXPLAIN without ANALYZE never executes the query. That property matters when an agent might emit a nested loop that only explodes after the first million rows. A reviewer can reject a candidate when total cost, estimated rows, or a sequential scan on a large relation crosses a numeric budget.
Advocates also note that cost estimates stay comparable when statistics are frozen for the test. Teams can restore a catalog snapshot, run EXPLAIN (FORMAT JSON), and compare total cost against a stored ceiling. The comparison is deterministic, fast, and free of write locks, which makes it attractive in CI for high-frequency agents.
PostgreSQL documents that the planner uses relation statistics to compute startup and total cost in abstract units, not milliseconds. The current EXPLAIN reference is the primary source for that behavior, including ANALYZE as the switch that actually runs the statement (PostgreSQL EXPLAIN). When those statistics lag, the same mechanism will underprice a scan, which is the opening Position B uses.
Even so, Position A remains rational for narrow OLTP lookups that must be screened hundreds of times per hour. A cost cap is a filter, not a proof of safety, and cheap filters belong at the earliest layer. Throwing away a bad plan before allocating a rehearsal host is an engineering choice, not a philosophical one.
Position B: Timed canaries on a rehearsal server
Canary advocates treat cost units as a different quantity from the SLO the pager actually pages on. Wall time, shared buffer hits, and rows actually returned can diverge from the estimate when predicates correlate or when a TOAST table dominates I/O. A rehearsal run with a tight statement_timeout converts that divergence into a binary promote-or-reject signal that CI can store.
The second argument is statistical freshness rather than philosophy. Agent SQL often encodes filters the warehouse added this week, so last week's histogram cannot price the plan honestly. Measuring a read-only canary against a subset that preserves skew is then the only test that can fail for the right reason. Realistic API performance work makes the same claim at another layer: the test has to look like production traffic, not like a fixture.
Canaries are not free in time or in data hygiene. They need a dataset that is not production, a timeout that is not infinite, and isolation from writers who serve customers. They also need a host you are willing to burn if the agent invents a pathological join, which is the only reason a scratch server belongs in this workflow.
Disclosure: This article was prepared as part of MonkeyCode's product outreach. When a team already has a staging replica, that replica is the correct canary target and no extra host is required. When it does not, MonkeyCode's free model access can draft candidate SQL, and the free server option can hold a throwaway rehearsal database for the harness below. Neither option replaces statistics management, anonymized subsets, or the decision rule, and this article does not claim quotas, hardware profiles, or durability.
Evidence both camps already accept
Both sides agree that agent SQL should not meet production on the first execution of a new text. Both sides also agree that parser-level checks and timeout budgets answer different questions than promotion, so this debate does not reopen those gates. The remaining dispute is which signal may veto a candidate that already parsed and already sat under a statement timeout.
Three shared facts constrain any honest comparison of the two camps. First, EXPLAIN without ANALYZE is cheap relative to execution, while EXPLAIN ANALYZE runs the statement and therefore needs a rehearsal role. Second, statement_timeout aborts a canary but does not repair a bad join order for the next agent attempt. Third, frozen statistics make cost comparisons reproducible, and thawed statistics make them honest about today's data.
A useful artifact has to record both signals instead of declaring a winner in prose alone.
A two-stage promotion harness
The following workflow is a proposal. It does not execute against a warehouse until you point the connection string at a scratch database you own.
Step 1: Freeze the question, not the model output
Store the candidate SQL, the intended read-only role, and the SLO in a small YAML file. Do not let the agent rewrite the SLO after it sees a failing canary, because that loop trains the model to game the gate. Keep the YAML in review so humans change budgets on purpose.
# proposal: promo_case.yml — not a live production contract
name: events_daily_rollups
slo_ms: 1500
max_explain_cost: 250000
statement_timeout_ms: 4000
require_canary_if:
estimated_rows_gt: 100000
seq_scan_relations:
- events
- event_payloads
Step 2: Capture a lock-free plan
Run EXPLAIN in JSON mode under a role that cannot write. Persist the total cost, planned rows, and node types beside the candidate. This is Position A as a command rather than a manifesto, and it should fail closed if the role is missing.
-- proposal: capture_plan.sql
SET default_transaction_read_only = on;
EXPLAIN (FORMAT JSON, VERBOSE, COSTS)
SELECT date_trunc('day', e.created_at) AS day,
e.event_type,
count(*) AS n
FROM events e
JOIN accounts a ON a.id = e.account_id
WHERE e.created_at >= now() - interval '7 days'
AND a.plan = 'enterprise'
GROUP BY 1, 2;
Step 3: Decide whether a canary is mandatory
Apply the YAML thresholds before you spend rehearsal time. If estimated rows stay tiny and no large sequential scan appears, Position A may be sufficient for that candidate. If the plan touches a fact table or the cost sits near the cap, Position B becomes mandatory rather than optional.
Step 4: Time a bounded canary
On a rehearsal host only, set statement_timeout below human patience and above the published SLO. Record wall time, EXPLAIN ANALYZE buffer totals, and whether the timeout fired. Never point this step at a primary that serves customers, even if the SQL looks like a SELECT.
# proposal: promo_harness.py — unexecuted example, scratch DB only
import json, os, time
import psycopg
SQL_PATH = os.environ["CANDIDATE_SQL"]
DSN = os.environ["SCRATCH_DSN"]
SLO_MS = int(os.environ.get("SLO_MS", "1500"))
TIMEOUT_MS = int(os.environ.get("STATEMENT_TIMEOUT_MS", "4000"))
MAX_COST = float(os.environ.get("MAX_EXPLAIN_COST", "250000"))
ROW_TRIGGER = float(os.environ.get("EST_ROWS_TRIGGER", "100000"))
def load_sql():
return open(SQL_PATH, encoding="utf-8").read()
def dsn_looks_unsafe(dsn: str) -> bool:
lowered = dsn.lower()
return any(token in lowered for token in ("prod", "primary", "master"))
def explain_only(cur, sql):
cur.execute("SET default_transaction_read_only = on")
cur.execute("EXPLAIN (FORMAT JSON, COSTS) " + sql)
plan = cur.fetchone()[0]
if isinstance(plan, str):
plan = json.loads(plan)
node = plan[0]["Plan"]
return float(node["Total Cost"]), float(node.get("Plan Rows", 0)), plan
def run_canary(cur, sql):
cur.execute("SET default_transaction_read_only = on")
cur.execute(f"SET statement_timeout = {TIMEOUT_MS}")
started = time.perf_counter()
cur.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql)
elapsed_ms = (time.perf_counter() - started) * 1000.0
payload = cur.fetchone()[0]
if isinstance(payload, str):
payload = json.loads(payload)
return elapsed_ms, payload[0]["Plan"]
def main():
if dsn_looks_unsafe(DSN):
raise SystemExit("refusing a DSN that looks like production")
sql = load_sql()
with psycopg.connect(DSN) as conn:
conn.autocommit = True
with conn.cursor() as cur:
cost, est_rows, _ = explain_only(cur, sql)
stage_a = "reject" if cost > MAX_COST else "pass"
need_canary = est_rows >= ROW_TRIGGER or cost > MAX_COST * 0.4
result = {
"stage_a_cost": cost,
"stage_a_est_rows": est_rows,
"stage_a": stage_a,
"need_canary": bool(need_canary and stage_a == "pass"),
}
if result["need_canary"]:
try:
elapsed_ms, plan = run_canary(cur, sql)
result["stage_b_ms"] = round(elapsed_ms, 1)
result["stage_b"] = "pass" if elapsed_ms <= SLO_MS else "reject"
result["shared_hit"] = plan.get("Shared Hit Blocks")
result["shared_read"] = plan.get("Shared Read Blocks")
except Exception as exc:
result["stage_b"] = "reject"
result["stage_b_reason"] = type(exc).__name__
print(json.dumps(result, indent=2))
if __name__ == "__main__":
main()
Step 5: Persist both verdicts next to the SQL
Write the JSON object beside the candidate in source control so later reviews see cost and milliseconds together. If Stage A rejects, skip the canary to keep the scratch host cheap and the logs readable. If Stage A passes and Stage B rejects, keep the cost number anyway so planner optimism becomes visible rather than anecdotal.
Sample output from an unexecuted run would look like the object below, which is a fixture for the debate, not a measured cluster result.
{
"stage_a_cost": 188432.4,
"stage_a_est_rows": 240000,
"stage_a": "pass",
"need_canary": true,
"stage_b_ms": 2210.6,
"stage_b": "reject",
"shared_hit": 1204,
"shared_read": 88110
}
Decision table
| Signal | Cheap to collect | Distorted by stale stats | Executes the query | Suggested veto |
|---|---|---|---|---|
| Parser and lint only | Yes | No | No | Syntax or missing objects |
| EXPLAIN total cost | Yes | Yes, can under-reject | No | Cost above the cap |
| Estimated rows on a fact table | Yes | Yes | No | Rows above threshold force a canary |
| Timed canary with timeout | No | Less than EXPLAIN alone | Yes, read-only | Time above SLO or timeout |
| Buffer reads from ANALYZE | No | Less than EXPLAIN alone | Yes, read-only | Reads explode versus a stored baseline |
The table is a decision aid, not a benchmark. Your numbers will differ with cache warmth, disk, and the quality of the data subset. Treat every ceiling as local until a week of promotions says otherwise.
A decision rule you can operationalize
Use both stages, in order, with an explicit exception list rather than a vibe. Stage A is a cheap rejector: if total cost exceeds the cap, do not promote and do not spend a canary. Stage B is mandatory when estimated rows on a fact table exceed the threshold, when the plan sequential-scans a large relation, when the SQL uses correlated subqueries, OFFSET paging, or volatile functions, or when yesterday's canary and today's estimate already disagreed by more than a factor of three.
If none of those hold, a passing cost gate may promote a narrow OLTP lookup without a timed run. That exception exists to keep CI fast, not to spare the agent from measurement as tables grow. Revisit the exception whenever autovacuum lag, a new index, or a warehouse backfill changes the shape of the fact table.
The rule is deliberately silent on writes. This harness assumes default_transaction_read_only, and agent-written DML or DDL needs rollback-first rehearsals that this debate does not cover.
Limitations
Cost units are not portable across PostgreSQL versions, random_page_cost, or work_mem, so a cap copied from another cluster is not evidence. Canary wall time depends on cache warmth, and a cold rehearsal host will reject queries that a warm replica would accept. Subset databases that drop the long tail of a skewed distribution will lie in the other direction and promote queries that production will punish.
EXPLAIN ANALYZE still runs the query, including functions with side effects if the role was not constrained. statement_timeout does not undo work already performed by a trigger that volunteered to write. The DSN heuristic that looks for names such as prod is a naming convention, not a security control, and it will miss a poorly named primary.
Who should not use this approach
Do not run timed canaries if you cannot obtain an anonymized subset and you would be tempted to use the primary instead. Do not use cost caps as the only gate if large tables sit unanalyzed for days. Regulated workloads that cannot copy rows onto a scratch host need EXPLAIN-only gates plus human sign-off, and they should not import customer payloads into a shared rehearsal server.
Teams that already keep a production-shaped replica and a mature query bot may find the Python file redundant. In that case the decision rule still applies, but the host should be the replica you already trust. A second scratch machine adds noise without adding a new signal.
What to count after you pick a side
After a week of promotions, count four numbers and ignore the rest of the telemetry. Count Stage A rejects, Stage B rejects, promotions that later needed a human rollback, and canaries skipped under the OLTP exception. If Stage B never rejects, the SLO is too loose or the subset is too kind, and if Stage A never rejects, the cost cap is decorative.
Those counts are the evidence this debate actually needs, and they cost nothing but a JSON file beside each candidate query. If you already operate a scratch database, run the harness there first; a free rehearsal host is only a convenience when that database does not exist.
Top comments (0)