Case Study 1: The Read-Only Query That Took Down Checkout

"It was a SELECT. I still do not entirely accept that it was my fault."

Executive Summary

On a Tuesday in 2025, Kestrel's checkout latency rose from a p99 of 180 ms to over 4 seconds over about three hours, and roughly 1,100 customers abandoned carts before the cause was found.

The cause was a SELECT statement. It held no locks. It was read-only. It ran on a read replica — and it still degraded the primary, because of a configuration setting nobody on the data team knew existed.

This case study is about MVCC bloat and hot_standby_feedback, which together produce the least intuitive failure in this book. It is also about a subtler thing: the engineer had done four of the five right things, and the fifth was invisible.

Skills applied: MVCC and long transactions (§7.3); reading a replica (§7.1, §7.8); chunked extraction (§7.7); the five defenses (§7.3).

Background

The change. The data team was adding a new source: order_events, an audit table the application had been writing for two years and that nobody had extracted. 340 million rows, 62 GB.

The first full extract was scheduled for 02:00 on a Tuesday, off-peak, against the read replica. The engineer wrote it carefully:

# extract_order_events.py  -- initial full load
with psycopg.connect(REPLICA_DSN) as conn:
    with conn.cursor(name="order_events_cursor") as cur:   # server-side cursor
        cur.itersize = 50_000
        cur.execute("SELECT * FROM order_events ORDER BY event_id")
        for batch in iter_batches(cur, 50_000):
            write_parquet(batch)

Four things this code does right, and it is worth listing them because the engineer was not careless:

  1. It runs against the replica, not the primary.
  2. It uses a server-side cursor, so 340 million rows are not materialized in client memory.
  3. It batches the writes, so Parquet files are a sensible size.
  4. It runs off-peak, at 02:00.

The one thing it does wrong is invisible in the code: cur.execute(...) inside a with psycopg.connect(...) block opens a transaction, and a server-side cursor requires that transaction to stay open for the entire scan. The scan took five hours and forty minutes.

The Problem

02:00. The extract begins. A single transaction opens on the replica. A snapshot is taken.

05:30. The extract is still running — the estimate had been two hours, based on a row count and an assumed throughput that turned out to be optimistic by nearly 3×.

06:00. Morning traffic begins. Kestrel's checkout starts updating rows.

07:15. The first alert: checkout p99 latency at 640 ms, up from 180.

08:40. p99 at 4.2 seconds. Cart abandonment climbing. The application team is in an incident channel and looking at their own recent deploys, of which there were two.

09:20. Someone runs pg_stat_activity on the primary and finds nothing unusual — no long queries, no locks, no blocked transactions.

09:35. Someone thinks to look at pg_stat_user_tables:

 relname       | n_live_tup  | n_dead_tup  | last_autovacuum
---------------+-------------+-------------+-----------------
 orders        |   9,412,883 |  38,204,116 | 2025-06-09 23:14
 order_items   |  25,109,442 |  71,882,003 | 2025-06-09 22:51
 payments      |   9,388,201 |  29,441,882 | 2025-06-09 23:02

Four times as many dead rows as live rows on orders, and autovacuum's last successful run was the previous night — before the extract started. Autovacuum had been running repeatedly and removing nothing.

09:41. The extract is cancelled. Autovacuum completes within eleven minutes. Checkout latency returns to normal by 10:05.

The Analysis

Why the primary bloated when the query ran on the replica

This is the part that took the team three days to fully understand, and it is worth walking through carefully.

A read replica applies the primary's write-ahead log. When the primary vacuums a dead row, that removal is itself a WAL record, which the replica applies. If a query is running on the replica that still needs the removed row version, the replica has a conflict, and PostgreSQL resolves it one of two ways depending on a setting.

With hot_standby_feedback = off (the default), the replica resolves the conflict by cancelling the query. Your extract dies with "canceling statement due to conflict with recovery." Annoying, and the primary is unaffected.

With hot_standby_feedback = on, the replica reports its oldest snapshot back to the primary, and the primary declines to vacuum rows the replica still needs. Your query survives. The primary bloats.

Kestrel's replica had hot_standby_feedback = on. It had been set two years earlier by a database administrator who no longer worked there, for an entirely sensible reason: analytical queries were being cancelled mid-run and it was making the replica useless for reporting.

That setting converted "the replica cancels your query" into "your query bloats the primary." It traded a loud failure for a silent one, and nobody who inherited it knew it was there.

⚠️ Failure Mode — hot_standby_feedback trades a loud failure for a silent one

This is the specific trap §7.3's defense 3 warns about, and it deserves stating plainly because the setting is widely enabled and widely not understood.

Setting Your long query The primary
off (default) cancelled — "conflict with recovery" unaffected
on survives bloats, in proportion to your query's duration × the write rate

Neither is wrong. on is the right setting for a replica whose purpose is analytical reporting, which is why it gets enabled. But enabling it means the replica no longer isolates the primary from your query duration, and that is exactly the property most people believe they are buying by reading a replica.

What to do:

  • Know which setting your replica has. SHOW hot_standby_feedback; — ten seconds, and most data engineers have never run it against a replica they extract from daily.
  • If on: your statement timeout is protecting the primary, not just your own query. Set it accordingly, and treat it as a production safety control rather than a convenience.
  • If off: your long queries will be cancelled, and you must chunk. Also look at max_standby_streaming_delay, which sets how long the replica waits before cancelling.
  • Consider a dedicated replica for analytics with on and a low max_slot_wal_keep_size, isolated from the failover path.

Why 02:00 did not save them

The extract was scheduled off-peak, which is correct practice and which did not help, for a reason worth generalizing.

Off-peak scheduling protects you from resource contention. It does not protect you from duration. A query that starts at 02:00 and runs for five hours and forty minutes is running at 07:40, which is peak. The scheduling assumed a two-hour runtime; the estimate was wrong by 2.8×; and nothing in the system noticed that the assumption had been violated.

A statement timeout would have converted this into a failed job at 04:00 — a loud, cheap failure with an obvious cause, at a time when nobody was shopping. There was no statement timeout.

The estimate

Why was the estimate wrong by 2.8×? The engineer had benchmarked on a table of 12 million rows and extrapolated linearly to 340 million. Two things broke the linearity:

The larger table did not fit in the buffer cache, so the scan moved from mostly-cached reads to mostly-disk reads partway through.

Parquet writing slowed as files accumulated, because the write target was the local disk that was also serving the read.

Neither is exotic. Linear extrapolation from a small benchmark is unreliable across a cache boundary, and a cache boundary is exactly what you cross when you go from 12 million rows to 340 million.

The Decision

Five changes, and the ordering reflects what each protects.

1. statement_timeout on every analytical connection, set in the connection string. Not in the application code, where someone can forget it — in the DSN used by every data pipeline:

postgresql://reader@replica/kestrel_app?options=-c%20statement_timeout%3D1800000

Thirty minutes. Any query that legitimately needs longer must be chunked, which is a design constraint rather than an inconvenience.

2. Chunked full loads, always. The rewritten extract ranges over event_id in chunks of one million, with a new connection per chunk:

# Each chunk is its own transaction. The snapshot is released between them,
# so autovacuum can work in the gaps -- which is the entire point.
lo = 0
while True:
    with psycopg.connect(REPLICA_DSN) as conn:          # new connection per chunk
        with conn.cursor() as cur:
            cur.execute("SET statement_timeout = 300000")   # 5 min per chunk
            cur.execute(
                "SELECT * FROM order_events "
                " WHERE event_id > %s ORDER BY event_id LIMIT %s",
                (lo, CHUNK))
            rows = cur.fetchall()
    if not rows:
        break
    write_parquet(rows)
    lo = rows[-1][0]

Runtime went from 5h40m in one transaction to 6h10m across 340 transactions — slightly slower overall, and the longest snapshot held is under a minute.

That trade is the heart of the case study. The chunked version is worse on the metric anyone would have measured and better on the one that mattered.

3. Alerting on the oldest transaction, on both primary and replica.

SELECT pid, usename, application_name,
       now() - xact_start AS xact_age,
       left(query, 120) AS query
  FROM pg_stat_activity
 WHERE state <> 'idle' AND xact_start < now() - interval '10 minutes'
 ORDER BY xact_start;

Warn at 10 minutes, page at 30.

4. Alerting on dead tuple ratio. n_dead_tup / GREATEST(n_live_tup, 1) > 0.5 on any table over a million rows. This is the check that would have shortened the incident from three and a half hours to minutes — it points directly at the mechanism.

5. hot_standby_feedback documented, and a dedicated analytics replica. The setting is now recorded in platform/docs/source-systems.md alongside every other assumption about the source, and Kestrel provisioned a second replica used only by the data platform, outside the failover path.

What Happened

The five changes went in over two weeks. In the eighteen months since:

  • The oldest-transaction alert has fired nine times, seven of them for a stuck extract that a statement timeout later killed anyway. Two were genuine problems caught early — a developer's psql session left open in a transaction over a weekend, and a BI tool with a connection leak.
  • The dead-tuple alert has fired twice, both times for reasons unrelated to the data team: a long-running application migration, and a vacuum that had been disabled on one table by a autovacuum_enabled = false storage parameter set in 2023 and forgotten.
  • No extract has affected the primary.

Two findings from the retrospective are worth carrying.

The engineer had done four of five things right. This was not carelessness, and treating it as carelessness would have produced worse changes — a rule saying "be careful with extracts" instead of a statement timeout in the DSN. When a competent person following good practice causes an incident, the missing control is systemic, not personal.

The three-hour diagnosis was because nobody suspected the replica. The primary looked healthy in pg_stat_activity, which is where everyone looked, and the query causing the problem was on a different machine. The dead-tuple alert exists specifically to short-circuit that: it points at the symptom's mechanism rather than at its origin, so it does not matter where the query is.

Lessons

  1. A read-only query on a replica can bloat the primary, if hot_standby_feedback = on. Know which setting your replica has. Most data engineers have never checked.

  2. hot_standby_feedback trades a loud failure for a silent one. off cancels your query; on bloats the primary. Neither is wrong; not knowing which you have is.

  3. Off-peak scheduling protects against contention, not duration. A job starting at 02:00 and running for six hours runs at peak. A statement timeout is what actually bounds this.

  4. Set statement_timeout in the connection string, not in application code where it can be forgotten. A query needing longer must be chunked — treat that as a design constraint.

  5. Chunking made the job slightly slower overall and enormously safer. Worse on the metric anyone would measure, better on the one that mattered.

  6. Linear extrapolation from a small benchmark fails across a cache boundary. 12 million rows to 340 million crosses one.

  7. Alert on the mechanism, not just the origin. The dead-tuple ratio points at the problem regardless of which machine the offending query is on.

  8. When a careful person following good practice causes an incident, the missing control is systemic. Four of five things right means the fifth needed to be automatic.

Questions for Discussion

  1. The engineer used a server-side cursor, which is correct practice for a large result set and which is also what held the transaction open. How would you teach this without teaching people to avoid server-side cursors?

  2. hot_standby_feedback had been set two years earlier for a good reason by someone who had left. How should a team track settings like this? Is documentation sufficient, and if not what is?

  3. The three-hour diagnosis happened because everyone looked at the primary. Design the first four checks in a runbook for "application database is slow and we do not know why." Where does the replica appear?

  4. The chunked extract is 30 minutes slower overall. Argue for the unchunked version to someone optimizing for pipeline runtime. What evidence would settle it?

  5. The dead-tuple alert has fired twice in eighteen months, neither time because of the data team. Is that a well-calibrated alert? What would make you remove it?

  6. One of the two dead-tuple alerts found autovacuum_enabled = false on a table, set in 2023 and forgotten. How many settings like this do you think a five-year-old production database carries? How would you find them?

  7. The retrospective concluded the failure was systemic rather than personal. Write the two-paragraph incident summary you would send to the application team, who lost 1,100 carts. How do you take responsibility without accepting a framing that produces the wrong fix?