Autovacuum ran 443,863 times and cleaned nothing: orphan queries on a read replica
Payments were credited 85 minutes late. The outbox relay was alive, traffic was normal and vacuum never stopped working. Nine forgotten queries on a replica were holding the xmin horizon of the primary.
At night, deposits in a payments service started getting stuck in pending. The money had already been taken by the payment provider, the webhook had been accepted, and the balance was not credited. The delay kept growing until it peaked at 85 minutes. Nothing was lost in the end. But that was a long night for support and for players.
The pipeline, and the first lie
The service uses a transactional outbox. The webhook handler writes an event to an outbox table in the same transaction as the business change. A relay worker reads the table in order, publishes to Kafka and deletes the rows it has sent. The wallet consumes the event and credits the balance.
The relay’s health metric said alive = 1 the whole night, while the queue grew to 560,000 events. Alive is not the same as keeping up. The signals that mattered were the age of the oldest pending event (normal is 1–2 seconds, it reached 5,100) and the publish rate compared with the same hour on previous nights. The publish rate was half of normal, and incoming traffic was flat. So this was not a flood: the relay had slowed down.
The relay’s logs showed its own queries to the outbox table timing out.
A table that vacuum could not clean
On the primary, the table statistics told the story:
select n_live_tup, n_dead_tup, autovacuum_count,
pg_size_pretty(pg_total_relation_size(relid))
from pg_stat_user_tables where relname = 'outbox_message';
About 0.5 million live rows, 10.8 million dead ones and 9.7 GB on disk for a table that is normally a few hundred megabytes. autovacuum_count was 443,863 and went up every time I looked. Vacuum was working constantly, and it still could not remove anything.
When vacuum runs and removes nothing, something is holding the xmin horizon: an old snapshot that might still need those row versions. A table that inserts and deletes every second turns into a field of dead tuples that every ordered SELECT has to walk through.
Three places to look, in order
- Long or
idle in transactionsessions on the primary (pg_stat_activity,backend_xmin). - Replication slots (
pg_replication_slots,age(xmin)). - Forgotten two-phase transactions (
pg_prepared_xacts).
The primary was clean. The slots were not: both physical slots, one per read replica, had age(xmin) around 9.3 million. The database ran with hot_standby_feedback on. Each replica reports the oldest snapshot it still needs back to the primary, so a long query on a replica stops vacuum on the primary.
On the replicas I found nine SELECT count(*) queries that had been running for 17 hours. Their application_name pointed to a ReplicaSet that no longer existed. They came from an admin segment builder: an operator had built a filter that matched almost every user. The HTTP request returned a 500 after 60 seconds, the operator retried, and each retry started another query. The application gave up but never cancelled the query in the database. Restarting or replacing the pod does not stop a query that is already running on the server either.
The fix took seconds
select pid, pg_cancel_backend(pid) from pg_stat_activity
where application_name like '<dead-replicaset>-%'
and xact_start < now() - interval '1 hour';
I ran this on each replica, with a narrow filter, after writing down the pids and the query texts. The slots’ age(xmin) dropped from 9.3 million to double digits. The next vacuum pass finished, and the backlog went from 510,000 to 11 in about two minutes. All delayed deposits were published.
What I deliberately did not do: restart the relay (the backlog lives in the table, and an interrupted batch can duplicate messages), run VACUUM FULL (an exclusive lock that stops the relay completely), or delete rows from the outbox (they are undelivered payments).
Why monitoring missed it
The Postgres exporter scraped only the primary. Its longest-transaction metric showed 30–57 seconds while the replicas held 17-hour queries. The first alert that fired was the outbox age alert, hours after the horizon was pinned.
Takeaways
- Vacuum that runs and removes nothing is a pinned horizon, not a slow vacuum. Check sessions, slots and prepared transactions.
- With
hot_standby_feedback, a replica can block vacuum on the primary. Monitor the replicas, not only the writer, and alert on slotage(xmin). - A client timeout does not cancel a server query. Set
statement_timeouton read-only roles. A query should not outlive the request that started it. - “Alive” is not throughput. For queues, alert on the age of the oldest item and compare rates with yesterday, not with zero.