When Index Hit Stats Go Stale: Two Positions for SQL Review Agents

A staging replica marked idx_orders_created_at as unused after a statistics reset left idx_scan sitting at twelve. The month-end reconciliation job that still needs that index was only three calendar days away. The review comment read like measured evidence rather than a guess from a truncated snapshot. This article treats that incident as a labeled scenario, not as a new production war story.

Query-hit statistics and schema catalogs answer different questions, and mixing them quietly is how agents start assuming things. A dump of columns cannot tell you whether an index earned its keep last quarter. A single pg_stat_statements row cannot tell you whether the writer still holds a lock. The debate below is about feeding sampled runtime counters to a free SQL review agent, not about EXPLAIN text or live catalogs.

Why Stale Counters Are a Different Failure Mode

Schema drift usually produces missing-column errors that ordinary integration tests can catch before the merge completes. Stale counters produce fluent advice that still compiles, still reviews cleanly, and still ships to production. PostgreSQL resets pg_stat_statements when the extension is reset, and it resets index counters after crash recovery. An agent that never receives stats_reset will treat a cold counter as if it described a cold object.

Recent community writing on agent workflows keeps returning to one practical wound: models assume missing facts instead of stopping. Attaching a half-reset idx_scan value makes the prompt look complete, which invites that assumption. Hiding the counters entirely makes the prompt look incomplete, which invites abstention if the harness requires it. That contrast is the fork this article tries to make executable in a review bot.

Position A: Attach Redacted Hit Stats So the Agent Can Rank Cost

Position A argues that a review without workload shape is theater, because every extra index is still a write tax. Advocates attach a sampled extract from pg_stat_statements and pg_stat_user_indexes beside the pull request diff. They want the agent to refuse unused-index language unless idx_scan and total_exec_time are present in the JSON. They also show mean time and total time together, so a rare month-end job is not ranked like noise.

Operational catalogs support this side more strongly than slogans do, especially around reset behavior. PostgreSQL documents that statement totals restart from zero after a statistics reset or after certain instance restarts. If those clocks are omitted from the prompt, a free model will narrate a drop recommendation with unearned confidence. Position A therefore ships stats_reset and snapshot_age_hours as first-class fields, then asks the model only to rank statements, never to drop objects.

A limited export also beats screenshots pasted into a ticket, because the file is diffable and testable in CI. Redaction can keep query text as a normalized fingerprint instead of the original literal string values. Teams that already sample slow logs can reuse that pipeline rather than inventing a second warehouse. The cost of Position A is process: sample rate, retention, and a fail-closed path when the snapshot is older than policy.

Position B: Hide Runtime Counters and Fail Closed on Invented Workload Claims

Position B argues that runtime stats are a side channel and a stale oracle at the same time. Row counts, call rates, and even fingerprints can outline business volume after only light redaction. A shared model endpoint is the wrong place to send that outline when the tenant mix is unknown. Private compute does not fix staleness, because a six-week-old snapshot still teaches the agent the wrong season.

This side wants the agent to see the pull request, a frozen schema dump, and a short policy file only. If the model claims an index is unused, or that one query dominates CPU, the harness rejects the comment before Git sees it. Silence is treated as a valid completion, not as a failed review or a missing feature. Position B would rather post nothing than post a confident false drop against a cold counter.

Assumption control is the evidence for this side, not a benchmark table from a private fleet. Agents fill gaps with plausible numbers when the prompt looks finished and official. Attaching a reset idx_scan of twelve is worse than attaching nothing, because twelve looks like a real measurement. Teams under audit also dislike query text leaving the database host without a written retention rule.

What Both Sides Already Agree On

Both positions reject dropping production indexes from a chatty paragraph that has no automated tripwire. Both want numbered review states that a program can parse without reading tone: accept, abstain, and reject-for-missing-stats. Both treat free model access as a way to iterate on the harness, not as a reason to skip the harness. The remaining split is only whether sampled counters belong in the prompt at all.

Artifact: Snapshot, Policy File, and a Fail-Closed Tripwire

The SQL and Python below are a proposal you can run against a non-production database. They are labeled unexecuted examples, not customer benchmarks and not a capacity claim for any vendor. Replace names, thresholds, and endpoints before any shared environment sees the files. The harness is the article's artifact; the model is only a later optional caller.

Step 1 — Export a redacted stats snapshot

-- proposal: redacted workload snapshot for review harnesses
SELECT
  now() AS captured_at,
  stats_reset,
  extract(epoch FROM (now() - stats_reset)) / 3600.0 AS snapshot_age_hours
FROM pg_stat_statements_info;

SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_exec_time_ms,
  round(mean_exec_time::numeric, 2) AS mean_exec_time_ms,
  rows,
  regexp_replace(query, '''[^'']*''', '?', 'g') AS query_fingerprint
FROM pg_stat_statements
WHERE calls >= 50
ORDER BY total_exec_time DESC
LIMIT 200;

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

Run that bundle on a replica and store the three result sets in one JSON document with an explicit schema version. Discard rows under min_calls so the agent cannot moralize about tiny administrative probes from health checks. Keep queryid as the join key so later comments can point at a fingerprint without pasting raw SQL.

Step 2 — Encode the debate as a policy file

# proposal: review_stats_policy.yaml
max_snapshot_age_hours: 24
min_calls: 50
allow_query_fingerprints: true
allow_drop_index_comments: false
on_stale_snapshot: abstain
on_missing_idx_scan: reject-for-missing-stats
rank_by: total_exec_time_ms

The policy file should sit in the same repository as the review prompt so drift is visible in git. Changing max_snapshot_age_hours is a review-policy change, not an invisible model tweak. If allow_drop_index_comments is false, the tripwire must win even when the model sounds careful. Treat a missing stats_reset field as stale, not as zero hours.

Step 3 — Parse only three legal states

# proposal: tripwire_stats.py — unexecuted example
from datetime import datetime, timezone
from pathlib import Path
import json

ALLOWED = {'accept', 'abstain', 'reject-for-missing-stats'}
FORBIDDEN = ('unused index', 'safe to drop', 'never queried', 'dominates cpu')


def load(path):
    return json.loads(Path(path).read_text())


def age_hours(snapshot):
    reset = datetime.fromisoformat(snapshot['stats_reset'].replace('Z', '+00:00'))
    now = datetime.now(timezone.utc)
    return (now - reset).total_seconds() / 3600.0


def evaluate(snapshot, verdict, policy):
    reasons = []
    hours = age_hours(snapshot) if snapshot.get('stats_reset') else 10**9
    text = verdict.get('comment', '').lower()
    state = verdict.get('state')
    if hours > policy['max_snapshot_age_hours'] and state != 'abstain':
        reasons.append('stale_stats')
    if any(p in text for p in FORBIDDEN):
        if snapshot.get('idx_scan_present') is not True:
            reasons.append('invented_workload_claim')
        if policy['allow_drop_index_comments'] is False and 'drop' in text:
            reasons.append('drop_index_forbidden')
    if state not in ALLOWED:
        reasons.append('illegal_state')
    return reasons

Feed this function a fixture in which stats_reset is forty hours old and the comment still says unused index. The expected result is a non-empty reasons list, not a posted review. A second fixture should pass when the snapshot is fresh and the comment only ranks total_exec_time_ms. A third fixture should fail if the model invents an idx_scan that the JSON never contained.

Step 4 — Apply the decision table before any comment is posted

Snapshot age	idx_scan present	Agent claim	Required state
<= 24h	yes	rank slow queries by total time	accept or abstain
<= 24h	yes	drop an index	reject-for-missing-stats unless policy allows
> 24h	yes or no	any workload claim	abstain
missing	no	unused index	reject-for-missing-stats
missing	no	style-only SQL comment	accept

That table is the debate in executable form rather than in metaphors about trust. Position A fills the early rows by shipping a fresh extract that includes both time columns. Position B lives on the missing-stats rows and treats abstain as a successful review outcome. If your agent cannot emit abstain, you do not yet have a reviewer.

Numbered Workflow You Can Run Without a Paid Fleet

Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode offers free model access and a free server option, which can host the tripwire and the small ranking prompt without turning this section into a pricing sheet. Availability is not a hardware spec, a quota table, or a permanence promise, and it is not a substitute for replica EXPLAIN.

1. Capture the redacted snapshot on a replica, and refuse to run the export on a primary that is already in distress.
2. Record stats_reset and captured_at in the same JSON document as the query fingerprints and index counters.
3. Store the policy file next to the review prompt so the model cannot silently widen its assigned job.
4. Send the pull request diff, the schema dump, and either the snapshot or an explicit STATS_ABSENT token.
5. Require the model to emit only accept, abstain, or reject-for-missing-stats plus one short comment.
6. Run tripwire_stats.py on the free server before the comment is posted to the code review system.
7. If the tripwire returns reasons, drop the comment and file a harness failure, not a DDL change.
8. Refresh the snapshot on a schedule shorter than max_snapshot_age_hours, then delete the previous extract.

A free model is useful in step 5 because the output contract is small and cheap to retry after a rejected state. A free server is useful in step 6 because the tripwire should still run when nobody is watching the queue. If either piece is missing, keep the tripwire and skip the model rather than skipping the tripwire. That ordering is the entire safety story for this workflow.

Total Time Versus Mean Time, Once Stats Are Allowed

Position A still needs an internal ranking rule after the snapshot is declared fresh enough to read. Mean execution time highlights nasty outliers that hardly run, including some that should keep their supporting indexes. Total execution time highlights the queries that actually burned replica CPU during the sampled window. Ranking by mean time alone is how month-end jobs get described as unused when they are only rare.

A practical compromise is to rank the candidate list by total_exec_time_ms, then print mean_exec_time_ms beside each row. The agent may discuss both columns in the comment, but it may not translate a low call count into a drop. The tripwire should treat the phrase unused index as illegal unless idx_scan is present and drop comments remain forbidden. That keeps ranking as ranking, and it keeps DDL in human hands.

Limitations and Who Should Not Follow This

This workflow does not replace EXPLAIN ANALYZE on a representative replica, and it does not authorize autonomous DROP INDEX statements. Fingerprints can still leak join shape, and the calls column can still leak volume. The Python tripwire is a string gate, so it will miss a politely worded drop that avoids the forbidden phrases. Catalog names also differ across managed Postgres offerings, especially for pg_stat_statements_info.

Do not use Position A if query text cannot leave the database host, even in redacted fingerprint form. Do not use Position B if your review SLA requires ranked cost and you have no other workload source. Do not point either position at production primaries during failover, because stats_reset will move under the job. Do not publish snapshots that include literal bind values, user identifiers, or tenant keys.

Label every run as a proposal until the tripwire has failed on purpose in staging with a stale fixture. If your agent cannot abstain, you have a text generator with database adjectives, not a review bot. That limitation matters more than which free endpoint you pick for the comment draft. Keep humans on any statement that changes locks, indexes, or visibility rules.

A Decision Rule You Can Audit

Use Position A when three conditions hold at the same time: snapshot age is under the policy cap, fingerprints are approved by security, and drop-index comments remain forbidden. Use Position B when any of those conditions fail, including an unknown stats_reset timestamp on the extract. If the agent invents an idx_scan that is not in the JSON document, treat that as a harness bug, not as a database finding.

The rule is deliberately boring, because boring rules keep reconciliation jobs alive when a counter still looks like twelve. Run the tripwire against a staging replica before you invite any model into the comment path. If you want to exercise that harness with free model access and a free server, start on staging and keep DROP statements out of the comment path.