Ask for the method, not the fix

Over about six months I shipped a few fixes for the same production incident. Every one of them looked like it worked.

The shape was always the same. A shared Postgres instance behind one of our services would go I/O-bound on a weekend evening, latency would spike across every surface backed by it, and an hour or two later it would recede on its own. I’d gather the Datadog charts, the Performance Insights screenshots and the logs, drop the lot into Claude alongside the codebase, and ask it to find the problem. It would reason for a while, tell me with complete confidence what the cause was, and raise a PR. The reasoning was sound. I’d ship it, nothing would break for three weeks, and I’d quietly file the incident as closed.

That last step is the bug. Not in the database — in me.

You can’t grep for an index that isn’t there

The root cause turned out to be a missing composite index behind the default discover feed. Nothing about that is visible from the codebase. A missing index is an absence, and absences don’t show up in a file read. The query itself looked fine — it had a LIMIT 9 on it, which reads as cheap right up until you learn the planner had no way to honour it early.

It was also conditional. The sort ran happily while the corpus fit in work_mem, and started spilling to temporary files once it didn’t. The defect was invisible at every data volume I could reproduce outside production, and it only bit under the concurrency of the platform’s most-viewed surface.

Then there’s the part that actively sent us the wrong way. The alert that fired named an endpoint that aggregates a histogram, and that endpoint was a victim of shared I/O contention rather than the cause of it. When an entire instance is queueing, everything on it is slow, and what surfaces loudest in endpoint latency is whatever was already heaviest. Handed that evidence, Claude went after the histogram query, because that is what the evidence said. We shipped a cache and a batch-size cap. Both were real improvements. Neither had anything to do with the fire.

The model wasn’t reasoning badly. It was reasoning correctly over evidence that didn’t contain the answer.

Asking for the method instead

So on the next flare-up I stopped asking for a fix.

I framed it the other way around: you’re a senior specialist in Postgres production incidents, I’m the one with production access, walk me through how you’d actually work this. Not what’s wrong. How would I find out what’s wrong. Where do I look first, what do I run, what am I looking for in the output, how do I confirm the fix, and why does the fix work.

The asymmetry is the whole point. It had the expertise and no hands. I had hands and no particular expertise in this corner of Postgres. Every previous attempt had asked it to do the half it couldn’t do.

What came back was a sequence, and the first instruction was to stop looking at endpoints entirely. Rank causes by resource waits instead. Average active sessions were dominated by IO:BufFileWrite — temp-file spill, the database writing sort data to disk because it wouldn’t fit in memory. That’s a different question from “which endpoint is slow”, and it has a different answer.

pg_stat_statements wasn’t enabled on that instance, and a failover earlier in the incident had reset pg_stat_activity anyway, so the usual route was closed. The workaround was to catch statements live:

SELECT pid, wait_event, query
FROM pg_stat_activity
WHERE wait_event = 'BufFileWrite';

Poll that during a flare-up and the offending statement names itself. Which it did — the default sort on the discover feed, running constantly, ordering the entire discoverable corpus by (type, rank, id) to return nine rows.

Then EXPLAIN (ANALYZE, BUFFERS) on it:

Limit  (actual time=49.900..54.458 rows=9 loops=1)
  Buffers: shared hit=21338, temp read=904 written=2552
  -> Nested Loop Left Join ...
       -> Gather Merge (Workers Planned: 1) ...
            -> Sort  (actual rows=43 loops=2)
                 Sort Key: item.type DESC, item.rank DESC, item.id DESC
                 Sort Method: external merge  Disk: 10208kB
                 Worker 0:  Sort Method: external merge  Disk: 10144kB
                 -> Parallel Index Scan using item_is_discoverable_index on item
                      (actual rows=10116 loops=2)
Execution Time: 56.491 ms

Twenty thousand rows scanned and sorted, at roughly 2.3KB per row because the query selected all 37 columns, to return nine of them. About 20MB of temporary files written per execution. One execution is 56ms and nobody notices. Several hundred a minute against a shared instance saturates temp I/O, and then every other query on the box is slow too.

The fix is unglamorous:

CREATE INDEX CONCURRENTLY item_discoverable_type_rank_id_idx
  ON item (type DESC, rank DESC, id DESC)
  WHERE is_discoverable = true AND deleted_at IS NULL;

Given an index that supplies the requested order, the planner can walk it and stop after nine rows. No sort, no spill. CONCURRENTLY takes a SHARE UPDATE EXCLUSIVE lock rather than blocking writes, so it can go on a live table.

The step I’d been skipping

Here’s the part that actually ended six months of this.

“How do I verify the fix” was a step in the prompt, and it’s the step every previous attempt had left to chance. The honest answer to did the last fix work? had always been: nothing has gone wrong since. On a bug that fires every few weeks, that sentence is worth nothing. It’s precisely the signal that a fix doing nothing at all also produces.

Verification meant re-running the same plan on the same query and comparing mechanisms rather than outcomes:

Limit  (actual time=0.042..0.177 rows=9 loops=1)
  Buffers: shared hit=119
  -> Nested Loop Left Join ...
       -> Index Scan using item_discoverable_type_rank_id_idx on item
            (actual time=0.016..0.031 rows=9 loops=1)
Execution Time: 0.218 ms

Metric	Before	After
Execution time	56.5 ms	0.218 ms
Temp written	~20 MB (external merge)	0
Buffers (shared hit)	21,338	119
Rows processed	sort 20,000, take 9	read 9

Temp writes gone entirely is the line that matters, because temp writes were the mechanism. The execution time is almost beside the point — you can imagine a change that made the query faster while still spilling, and that change would have bought another three quiet weeks and nothing else. Then the system-level confirmation on top of the plan: IO:BufFileWrite back to baseline in Performance Insights, and staying there.

The difference is between it stopped happening and it can no longer happen. The first is a story about the last three weeks. The second is a claim about a mechanism, and you can only make it if you measured the mechanism.

This doesn’t generalise to everything. Plenty of bugs never hand you a clean mechanism to point at, and sometimes watching and waiting is the only instrument available. But when there is one — a wait event, a temp file, an allocation, a plan node — then “no alerts since Tuesday” isn’t weak evidence. It isn’t evidence.

The seat swap

None of this method is new. It’s how you bring a junior engineer along. You don’t hand them the diff; you ask what they’ve already observed, point them at where to look next, let them run the command themselves, and then make them explain why the fix worked before you’ll agree that it did. Light touch, hands off the keyboard. I’ve been doing that for years.

What was new was doing it from the other chair. The prompt that keeps failing — here are the logs, here’s the repo, fix it — casts the model as a contractor delivering a patch, and asks it for the one thing it can’t do: observe a running system. The prompt that worked cast it as the senior engineer, and me as the one who has to go and look.

I already knew the method. I’d just never been on the receiving end of it.