Timeout Budgets or Work-Mem Caps: A Debate for Agent-Written SQL

A staging replica accepted an agent-written reporting query that never finished its hash join. The statement held a share lock on a large fact table while nested loops spilled to disk. Review comments had approved the SQL because the join keys looked correct and the filters used indexed columns. The missing control was not syntax; it was a resource envelope the session never received.

This article treats that rehearsal case as a design debate, not a war story with invented outage metrics. Two credible camps now argue about how SQL review agents should bound work. One camp pins a clock on every statement. The other camp pins memory, temp files, and planner ceilings before the first row is touched.

Why resource envelopes belong in SQL review

Agent-written SQL often looks locally reasonable and still saturates a replica. Join order, predicate shape, and index names can pass a linter while the planner chooses a hash that spills. Human reviewers read intent. Database engines spend CPU, work_mem, and lock time. Those two views diverge under agent volume.

The last week of developer discussion around AI coding quality keeps returning to the same gap. Generating a query is cheap. Owning its runtime envelope is still engineering. For SQL review agents, the practical question is which envelope to encode as policy, and which envelope to leave as an incident runbook.

The rest of this piece compares timeout budgets with memory caps, then offers a labeled rehearsal harness. No production timings are claimed. The harness is a method you can run against a disposable Postgres instance you already operate.

Position A: timeout budgets as the primary gate

Timeout advocates treat wall-clock time as the only signal operators will actually enforce. A statement that exceeds a budget is cancelled, the transaction ends, and the agent receives a typed failure. Review then becomes a question of whether the proposed SQL can finish inside a published ceiling.

The evidence for this camp is operational, not aesthetic. On-call teams already know statement_timeout and lock_timeout. Orchestrators already retry on cancellation. Product owners already understand “this report may run for thirty seconds.” A clock is easy to explain in a pull request and easy to test with pg_sleep.

A typical session envelope looks like the following labeled example. Treat it as a contract template, not a benchmark.

-- labeled example: statement envelope, not a measured SLA
SET application_name = 'sql_review_agent';
SET statement_timeout = '15s';
SET lock_timeout = '3s';
SET idle_in_transaction_session_timeout = '10s';

-- agent SQL lands only after the GUCs above are applied
SELECT o.id, sum(i.amount)
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= DATE '2026-09-01'
GROUP BY o.id;

Timeout budgets fail in a predictable way. A query can be cheap for ten seconds and then explode on the eleventh because a filter was not selective. Cancellation also leaves partial work if the agent used autocommit DML. The clock answers “how long,” not “how heavy.”

Position B: work-mem caps as the primary gate

Memory-cap advocates treat planner resource use as the real hazard. Hash joins, sorts, and materialize nodes consume work_mem per node, not per session, and temp files can fill a disk long before a timeout fires. A fifteen-second query that spills a few gigabytes is still an incident on a shared replica.

This camp wants the review agent to emit session GUCs and planner constraints together with the SQL. The SQL is not “approved” until the envelope would keep sorts in a bounded arena. Operators then fail the review when EXPLAIN shows sorts or hashes that would exceed the published cap at the stated row estimates.

-- labeled example: memory and temp-file envelope
SET work_mem = '16MB';
SET temp_file_limit = '256MB';
SET max_parallel_workers_per_gather = 0;
SET enable_nestloop = on;

EXPLAIN (FORMAT JSON)
SELECT o.id, sum(i.amount)
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= DATE '2026-09-01'
GROUP BY o.id;

The evidence here is structural. Postgres documentation is explicit that work_mem is per operation, so a query with several hash nodes multiplies the ceiling. Disk spill is visible in EXPLAIN (ANALYZE, BUFFERS) after a rehearsal run. Memory caps catch “small time, huge footprint” queries that timeout policy would bless.

The weakness is estimation. If statistics are stale, the plan JSON understates memory, and the cap becomes theater. Memory GUCs also change plans, so a review that rewrites work_mem may approve a shape that production, with a higher default, will never use. Caps without a frozen statistics snapshot are incomplete.

Artifact: a decision table and rehearsal loop

The original artifact is a decision table plus a small rehearsal script. The table is meant for review bots that must choose an envelope before they emit “approve.” Rows are query classes, not product claims.

Query class	Timeout budget first?	Work-mem cap first?	Why
Point lookup by primary key	Yes	No	Clock detects lock waits; memory is already tiny
Aggregates on filtered facts	Split	Yes	Hash aggregates spill before wall-clock alerts
Agent DML with joins	Yes	Yes	Need lock_timeout and a tight work_mem
Window functions over dates	No	Yes	Sorts dominate; a long timeout hides spill
Cross-database reporting	Yes	Split	Remote waits look like CPU; cap alone misleads
Unknown cardinality	Yes	Yes	Dual envelope, then refuse if either trips

Use the table as a routing rule inside the reviewer, not as a production SLA. The following labeled Python shows one rehearsal loop. It applies both envelopes, runs EXPLAIN only, and prints which gate would have fired. It does not execute DML.

# labeled rehearsal: unexecuted against your instance until you fill DSN
import json
import os
import psycopg

TIMEOUT = os.environ.get("REVIEW_STATEMENT_TIMEOUT", "15s")
WORK_MEM = os.environ.get("REVIEW_WORK_MEM", "16MB")
SQL = os.environ["CANDIDATE_SQL"]  # SELECT-only for this harness

ENVELOPE = f"""
SET statement_timeout = '{TIMEOUT}';
SET lock_timeout = '3s';
SET work_mem = '{WORK_MEM}';
SET temp_file_limit = '256MB';
SET max_parallel_workers_per_gather = 0;
"""

EXPLAIN = "EXPLAIN (FORMAT JSON, VERBOSE) " + SQL

def node_needs_memory(node: dict) -> bool:
    plan_type = node.get("Node Type", "")
    return plan_type in {"Hash", "Hash Join", "Sort", "Aggregate", "WindowAgg"}

def walk(node: dict, hits: list) -> None:
    if node_needs_memory(node):
        hits.append({
            "type": node.get("Node Type"),
            "plan_rows": node.get("Plan Rows"),
            "sort_space": node.get("Sort Space Used"),
        })
    for child in node.get("Plans", []):
        walk(child, hits)

with psycopg.connect(os.environ["REVIEW_DSN"], autocommit=True) as conn:
    with conn.cursor() as cur:
        cur.execute(ENVELOPE)
        cur.execute(EXPLAIN)
        plan = cur.fetchone()[0][0]["Plan"]
        memory_nodes = []
        walk(plan, memory_nodes)
        print(json.dumps({
            "timeout_budget": TIMEOUT,
            "work_mem": WORK_MEM,
            "memory_nodes": memory_nodes,
            "would_cap_memory": any(memory_nodes),
        }, indent=2))

Numbered rehearsal steps keep the debate testable.

1. Restore a sanitized schema snapshot into a disposable database you control, never a shared warehouse endpoint.
2. Freeze statistics with ANALYZE on the tables the candidate SQL touches, then store pg_stats row counts beside the review artifact.
3. Apply the timeout budget alone, run EXPLAIN (not ANALYZE) on SELECT-only SQL, and record estimated cost.
4. Reset the session, apply the work-mem cap alone, and run the same EXPLAIN so plan shape changes are visible.
5. Apply both envelopes together and refuse the candidate if the plan still contains unbounded sorts or missing join filters.
6. For DML, stop after the dual envelope; do not auto-apply, and require a human runbook for rollback.

A decision rule that does not pick a mascot

Pick the timeout budget as the outer gate when the dominant failure mode is waiting: lock queues, remote scans, or agent loops that forget a predicate. Pick the work-mem cap as the outer gate when the dominant failure mode is shape: hashes, sorts, and window functions on wide fact tables. If the reviewer cannot classify the query, apply both envelopes and fail closed.

Concretely, encode three booleans in the review result. clock_ok means the estimated cost sits under the published timeout heuristic you already use for humans. memory_ok means no memory node in the plan JSON would obviously exceed work_mem at the frozen row counts. write_ok means the SQL is SELECT-only, or the DML is wrapped in an explicit transaction with lock_timeout. Approve only when all three are true.

That rule is stricter than either camp alone. It will reject some queries that would have finished. That is the point of a rehearsal-first reviewer. False rejects are cheaper than a replica that spills, provided the agent can rewrite and resubmit inside the same envelope.

If a team already reviews SQL with a constrained editor, MonkeyCode's free model access can draft envelope wrappers, and its free server option can run the EXPLAIN-only loop off the warehouse. Disclosure: This article was prepared as part of MonkeyCode's product outreach. Remove that runtime and the decision table still stands; the envelopes are session GUCs and a plan walk, not a vendor feature.

Limitations and who should skip this approach

This method assumes Postgres-style session GUCs and JSON EXPLAIN. Engines without per-session memory caps need a different artifact. It also assumes you can freeze statistics; autovacuum drift will make memory_ok lie. The harness must not run EXPLAIN ANALYZE on writes, and it must not point at production connection strings.

Do not use dual envelopes as a substitute for bind parameters, row-level security, or a migration rehearsal. Do not use them on nested-loop accidental cross joins and then raise timeouts until the query “passes.” Do not publish timeout numbers as SLAs without measuring them on your own hardware. This article does not claim model quality, token quotas, or server capacity beyond the two availability notes above.

Teams with a dedicated query-gateway team and admission control may already have a better outer gate. Teams that only generate one-off analyst SQL, with a human watching pg_stat_activity, will find the table heavy. The debate is for review agents that emit SQL faster than humans can watch dashboards.

The useful close is a checklist, not a slogan. Publish one timeout, one work_mem, and one refuse-closed rule beside every agent-written query. If you want a disposable place to rehearse that checklist, the free server path is optional; the envelopes remain the engineering work.