> Markdown version of https://agledger.ai/blog/postgres-queue-retention-xmin-horizon/
> Full index of this site for AI assistants: https://agledger.ai/llms.txt

[← Blog](https://agledger.ai/blog/)2026-08-28Engineering

# Retention does not protect a Postgres queue from a pinned xmin horizon

By Michael Cooper · Founder

A short retention window is the usual answer to "won’t a queue table bloat?". We measured what happens to that answer when snapshots stay open elsewhere in the same database. Same rate offered and drained: the heap more than doubled, dead tuples went from 1.1% to 54%, and both arms ran exactly the same number of autovacuums.

## The short version

Retention and the MVCC horizon are two different mechanisms. Retention issues `DELETE` statements. The horizon decides whether the space those deletes free can be reused. A retention policy is not a defence against a pinned horizon, because it operates on completed rows and the horizon operates on visibility.

The measurement that carries this is not the heap size. It is the vacuum count: **59 autovacuums in the pinned arm, 59 in the control**, over the same hour, while the pinned heap grew 129% larger.

Companion to [the post on measuring whether the table is bounded at all](https://agledger.ai/blog/postgres-queue-table-three-measurements/), which carries the rig description and the sampling method both posts use.

## 1. What decides whether deleted space comes back?

The oldest snapshot open anywhere in the database, not the age of the row.

The PostgreSQL manual puts the constraint plainly: *"the row version must not be deleted while it is still potentially visible to other transactions"*. So a queue with a one-hour deletion window has two independent things that both have to work. Retention has to fire, which is a property of the queue library. And the horizon has to advance, which is a property of every other session connected to that database, and is not under the queue’s control.

The shortest way to see it is to make `VACUUM` narrate. Below, a 400,000-row table shaped like a queue, vacuumed by hand so nothing waits on `autovacuum_naptime`. The holder in step two opens a `REPEATABLE READ` snapshot without reading the table at all, so it takes no lock on it (verified: zero rows in `pg_locks` for that pid against that relation). PostgreSQL 18.6:

```
-- 1. retention fires, nothing else running
DELETE FROM churn WHERE id % 2 = 0;
VACUUM (VERBOSE) churn;
INFO:  vacuuming "postgres.public.churn"
INFO:  finished vacuuming "postgres.public.churn": index scans: 1
  pages: 0 removed, 12500 remain, 12500 scanned (100.00% of total), 0 eagerly scanned
  tuples: 200000 removed, 200000 remain, 0 are dead but not yet removable
  ...

-- 2. elsewhere: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1;
--    (holds a snapshot, touches no table, blocks nobody)
DELETE FROM churn WHERE id % 4 = 1;
VACUUM (VERBOSE) churn;
  tuples: 0 removed, 200000 remain, 100000 are dead but not yet removable
  removable cutoff: 757, which was 1 XIDs old when operation ended
  ...

-- 3. holder terminated
VACUUM (VERBOSE) churn;
  tuples: 100000 removed, 100000 remain, 0 are dead but not yet removable
```

Same command, same table, and the only thing that changed is whether a snapshot was open somewhere else. The `removable cutoff: 757` line is the holder’s own `backend_xmin`, which is where the two mechanisms meet: PostgreSQL is reporting exactly which transaction id it is not allowed to vacuum past. That number is specific to the cluster it was captured on; what reproduces is that it equals the holder’s `backend_xmin`.

A `pg_sleep` stands in for a long report here. What pins the horizon is the snapshot rather than what the session does with it, so a connection sitting `idle in transaction` does the same thing.

## 2. What does that do to a real queue at load?

At 2,000 jobs/s it more than doubles the heap at an identical completed workload. At 8,000 jobs/s the driver stopped sustaining the offered rate, so that arm measures a collapse rather than the horizon.

Both arms run identical hardware, identical autovacuum settings and a 3,600-second retention window. The hold arms add three staggered holders keeping a `REPEATABLE READ` snapshot open for 1,800 seconds each, which is half the retention window. No single query is pathological; the horizon is simply never allowed to advance.

Each hold arm ran two hours. The controls are the same configuration at the same rate with no holders, read over the same elapsed window. Everything below is a median over the second hour (`t=3602s` to `t=7204s`), not a single sample.

| Rate | Arm | Produced/s | Consumed/s | Heap | Indexes | Dead | Autovacuums |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 2,000/s | no hold | 2000 | 1990 | 2,222.4 MB | 515.8 MB | 1.1% | 59 |
| 2,000/s | three 1800s holds | 2000 | 1999 | 5,094.8 MB | 1,118.6 MB | 54.4% | 59 |
| 8,000/s | no hold | 8000 | 8002 | 9,119.9 MB | 2,086.6 MB | 4.5% | 59 |
| 8,000/s | three 1800s holds | 2460 | 643 | 11,511.7 MB | 2,117.7 MB | 33.7% | 47 |

Read the 2,000/s pair first: it is the pair where the workload is identical across arms. Same rate offered, same rate drained. The heap goes from 2,222.4 MB to 5,094.8 MB, a 129% increase, the indexes go from 515.8 MB to 1,118.6 MB, and dead tuples go from 1.1% to 54.4%.

The 8,000/s pair is a different result. Production fell to 31% of the offered rate and consumption to 8%, so the rig flags it `INVALID(driver)`: its heap and dead-tuple figures describe a system that was already failing for a second reason. It remains a valid demonstration of the backlog mechanism in section 4, because the backlog is what the driver shortfall produced, but do not quote its bloat numbers.

## 3. Why do the autovacuum counters look healthy the whole time?

Because they count runs, and the runs happen. What does not happen is the reclaim.

The last column of that table matters more than the heap size. At 2,000 jobs/s, over the same hour, **both arms recorded exactly 59 autovacuums**. Autovacuum was scheduled, it ran, it incremented `autovacuum_count`, and the pinned arm ended the hour with 2.3 times the heap. A dashboard reading vacuum cadence, or `last_autovacuum`, or "is autovacuum keeping up with this table", shows the same picture in both.

At 8,000 jobs/s the count did drop, 59 to 47. That is a signal, but a 20% change in a cadence counter is not what a monitor is built to raise, and by that point the consumer had already collapsed.

### Finding the holder

Four places pin the horizon and only the first is in `pg_stat_activity`. Check all four:

```
-- 1. backends. Check backend_xid too: a READ COMMITTED session that wrote
--    and then went idle in transaction has a NULL backend_xmin, but its
--    assigned xid still holds the horizon back.
SELECT pid, backend_type, state,
       age(backend_xmin) AS xmin_age,
       age(backend_xid)  AS xid_age,
       now() - xact_start AS txn_age,
       left(query, 60)    AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY greatest(age(backend_xmin), age(backend_xid)) DESC NULLS LAST;

-- 2. replication slots. An inactive or abandoned slot pins the horizon
--    indefinitely and never appears in pg_stat_activity.
SELECT slot_name, active, xmin, catalog_xmin,
       age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY age(coalesce(xmin, catalog_xmin)) DESC NULLS LAST;

-- 3. orphaned prepared transactions.
SELECT gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY prepared;

-- 4. a standby with hot_standby_feedback = on holds it through its slot,
--    which shows up in query 2 on the primary.
```

An abandoned logical replication slot is a common way to hit this and the one the first query cannot see, which is why a detector built only on `pg_stat_activity` can come back empty while the table bloats.

## 4. Why does it spiral instead of just slowing down?

Because the deletion window cannot reach a backlog. In pg-boss, queued jobs are governed by a different clock whose default is fourteen days.

pg-boss’s maintenance `DELETE` has two branches, and `deleteAfterSeconds` only touches the first: it is measured from `completed_on`, so a job that has not reached a terminal state cannot match it. Rows sitting in `created` or `retry` leave on `keep_until` instead, which is set from `retentionSeconds` and defaults to fourteen days. The predicate and the enum ordering behind it are [footgun 11 in our pg-boss post](https://agledger.ai/blog/pg-boss-production-lessons/).

That is what turns a slowdown into a spiral. The pinned horizon stops vacuum reclaiming space, the table and its indexes get slower, the consumer falls behind, work accumulates in `created`, and every one of those rows is outside the reach of the window you tuned. The 8,000 jobs/s hold arm finished its two hours with **12,889,848 jobs in `created`**, against 2,768,000 completed. None of those 12.9 million rows were eligible for the one-hour deletion window; on the default `retentionSeconds` they would sit for a fortnight.

If you run a tight deletion window, lower `retentionSeconds` alongside it rather than leaving the default in place, so a backlog has some ceiling. That bounds the aftermath. It does nothing about the horizon.

## 5. Does the damage scale with how long the hold lasts?

Two doses, both consistent with proportionality. Two points cannot establish a curve.

Against the same 3,600-second window, a 120-second hold moved dead tuples by 2 to 3.5 points, close to the arithmetic for protecting roughly 3% of the live rows. An 1,800-second hold covers half the window and took dead tuples past 54%. Both land near the ratio of window covered, which is weak evidence for proportionality and stronger evidence against a cliff somewhere just above 120 seconds. The intermediate doses that would settle it were not run.

Read as a shape rather than a formula, that ratio makes the exposure estimable from things you already know: a reporting query running for a tenth of your retention window is protecting roughly a tenth of the window’s rows from vacuum. Halving the retention window without touching the query doubles that fraction, which is worth noticing before tightening retention as a bloat remedy.

It also means a first version of this experiment could not have produced a result. It held the horizon 120 seconds against a 3,600-second window, protecting about 3% of live rows, and returned a confident no-difference.

## 6. Does the queue’s own engine pin the horizon?

The generalizable rule: any endpoint that reads an unbounded amount of data inside one transaction is a horizon pinner, whatever else it is.

The exposure above comes from a customer’s other workload, but an application that reads its own large tables can create it unaided. We drove the operations most likely to hold a long snapshot against our own install (2,000,011 records): a 7.6 MB compliance export, a record audit export, an event stream read from epoch, a full audit-vault export, and a complete vault scan. Maximum transaction age observed was one second, and no backend was ever `idle in transaction`.

Two design choices did that work, and both generalize. The export **caps itself**, returning `recordCount: 10000, truncated: true` rather than streaming two million rows inside one snapshot. And the vault scan is **a job, not a request**: it returns immediately with a job id and then runs in short transactions in a loop.

One caveat on that probe, since it is the kind of number that flatters whoever ran it: the maximum `age(backend_xmin)` observed was 9 transactions, which says as much about how little else was committing on that box as about the endpoints. It shows they do not hold long snapshots. It is not a load test.

## 7. What do you do about it?

Not the deletion window. Retention operates on completed rows and the horizon operates on visibility, and per section 5 tightening the window raises the fraction of it a given long query covers.

- **Bound transaction lifetime.** `idle_in_transaction_session_timeout` and `statement_timeout` put a ceiling on how long any one snapshot can be held. Cheapest of the set, and easiest to leave at its default.
- **Limit concurrent slow queries.** PlanetScale’s published treatment of this failure uses resource budgets to *"limit how often overlapping slower queries can run and how many can run at once"*, so autovacuum gets a chance to clean between them. That specific mechanism is a feature of their platform; on vanilla PostgreSQL the nearest equivalent is a per-workload concurrency cap at the pooler. The point that carries either way is that overlapping queries, none individually pathological, hold the horizon as effectively as one long transaction.
- **Bound your own readers.** Paginate or job-ify any endpoint that reads an unbounded amount inside a single transaction (section 6). This is the one you control in your own code.
- **Separate the workloads.** A read replica or a separate database for reporting removes the interaction rather than budgeting it. Most expensive, and the only structural fix.
- **Lower `retentionSeconds`, not just `deleteAfterSeconds`.** Bounds the backlog that forms while the horizon is stuck (section 4). This one is a queue setting, and it treats the aftermath rather than the cause.

## What this does not show

**This is MVCC working correctly.** Nothing here is a PostgreSQL defect or a pg-boss defect. Not removing a row version an open transaction can still see is the guarantee, not a bug in it.

**One instance per point, no replicates.** The hold arms ran 7,200 seconds against a 3,600-second retention window, so the reported window is their second hour. The no-hold controls are the first two hours of the longer runs from the companion post, read over that same elapsed window. The companion post shows heap size at these rates still moving many cycles later, so treat these as a matched comparison at one hour rather than as steady-state absolutes.

**The 8,000/s hold arm is a collapse, not a bloat measurement.** The driver could not sustain the offered rate. Quote the 2,000/s pair for the horizon effect, and the 8,000/s arm only for the backlog mechanism.

**Dead-tuple counts are estimates** from the statistics collector rather than `pgstattuple`, reported as `n_dead_tup / (n_live_tup + n_dead_tup)` and as medians over the window. Heap and index sizes are exact.

**One queue, one workload, 4 vCPU shared** between producer, consumer and Postgres. The absolute rates are a property of this topology. The ratio between the arms is the result; the rates are context.

## Sources & further reading

- [PostgreSQL 18: Recovering Disk Space](https://www.postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY)  - why a row version cannot be removed while it is potentially visible to another transaction
- [PostgreSQL 18: Transaction Isolation](https://www.postgresql.org/docs/18/transaction-iso.html)  - what a `REPEATABLE READ` snapshot guarantees, and therefore what it pins
- [PostgreSQL 18: pg_stat_activity](https://www.postgresql.org/docs/18/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW)  - `backend_xmin`, `backend_xid` and the `idle in transaction` state
- [PostgreSQL 18: pg_replication_slots](https://www.postgresql.org/docs/18/view-pg-replication-slots.html)  - the `xmin` and `catalog_xmin` an inactive slot holds
- [PostgreSQL 18: idle_in_transaction_session_timeout](https://www.postgresql.org/docs/18/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT)  - the cheapest bound on snapshot lifetime
- [PlanetScale: Keeping a Postgres queue healthy](https://planetscale.com/blog/keeping-a-postgres-queue-healthy)  - overlapping slow queries pinning the horizon, and resource budgets as their remedy
- [PlanetScale: Every UPDATE Leaves a Ghost](https://planetscale.com/blog/postgresql-mvcc)  - background on MVCC, bloat and VACUUM
- [pg-boss on GitHub](https://github.com/timgit/pg-boss)  (MIT) - version 12.27.0 throughout

Related

[Measuring whether the table is bounded →](https://agledger.ai/blog/postgres-queue-table-three-measurements/)[pg-boss in production →](https://agledger.ai/blog/pg-boss-production-lessons/)[The advisory-lock self-deadlock →](https://agledger.ai/blog/postgres-advisory-lock-self-deadlock/)[Docs →](https://agledger.ai/docs/)