Find the transaction preventing PostgreSQL cleanup

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

A table can accumulate obsolete versions even while vacuum runs successfully. The limiting factor may be a transaction that still needs an older view of the data. In that situation, “run vacuum again” repeats work without changing the visibility horizon that constrains cleanup.

Finding a suspicious session is only the first step. The oldest connection is not automatically the cause, and a long-running business operation may be legitimate. The diagnosis must connect a retained horizon to the affected workload before anyone decides to cancel or terminate it.

Start with read-only session evidence

Use PostgreSQL 18. Full visibility into other sessions' details requires an appropriate monitoring role; ordinary users may see restricted fields. These queries inspect state and do not terminate anything.

SELECT pid, usename, application_name, datname, state,
       xact_start, query_start,
       clock_timestamp() - xact_start AS transaction_elapsed,
       backend_xid, backend_xmin,
       age(backend_xmin) AS xmin_age,
       wait_event_type, wait_event,
       left(query, 160) AS query_excerpt
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND (xact_start IS NOT NULL OR backend_xmin IS NOT NULL)
ORDER BY age(backend_xmin) DESC NULLS LAST,
         xact_start ASC NULLS LAST;

Query text may contain sensitive information. Limit its collection and retention according to the organization's monitoring policy. The truncated excerpt is for diagnosis, not a recommendation to export every query to an unrestricted log.

backend_xmin exposes a session's snapshot horizon when present. backend_xid identifies an assigned transaction ID when present. Transaction age in wall-clock time and XID age measure different things: one is elapsed time, the other is transaction-ID distance. Preserve both rather than sorting only by connection start time.

Reproduce a legitimate retained snapshot

In a disposable lab, session A can deliberately retain a snapshot:

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM public.cleanup_demo;
-- Leave this transaction open briefly while session B observes it.

This is an application-table template: create a suitable cleanup_demo fixture before running it. Session B can execute the monitoring query and compare the session's state while other committed updates create old versions. End session A with COMMIT after the short demonstration. Do not leave a teaching transaction open unattended.

A plain idle connection outside a transaction is different from an idle-in-transaction session. A read committed transaction between statements can also differ from a repeatable-read transaction that retains a snapshot. Inspect the actual horizon and transaction state rather than treating every idle backend as equivalent.

Reference investigation. A session, prepared transaction, or replication mechanism can retain cleanup-relevant state; ownership and business purpose must be established before intervention.

Inspect non-session retention sources

A prepared transaction can outlive its client connection. If two-phase commit is in use, inspect it through the supported view:

SELECT gid, prepared, owner, database, transaction,
       age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;

Do not resolve a prepared transaction based only on its age. The transaction coordinator may depend on its outcome. Committing or rolling it back without the coordinator's decision can violate the surrounding distributed protocol.

Replication slots can retain row or catalog horizons, as well as WAL:

SELECT slot_name, slot_type, active,
       xmin, age(xmin) AS xmin_age,
       catalog_xmin, age(catalog_xmin) AS catalog_xmin_age,
       restart_lsn
FROM pg_replication_slots
ORDER BY age(xmin) DESC NULLS LAST,
         age(catalog_xmin) DESC NULLS LAST;

Separate these fields. restart_lsn concerns WAL retention; xmin and catalog_xmin concern tuple horizons. A slot retaining WAL does not automatically explain ordinary user-table tuple retention. Similarly, a catalog horizon is not identical to a user-table horizon. Name the mechanism precisely.

Standby feedback can also affect cleanup on a primary. Inspect replication state and the configured feedback policy if sessions and prepared transactions do not explain the observation. The remedy involves a tradeoff between replica query continuity and primary cleanup, not simply removing an arbitrary old process.

Correlate rather than guess

Record the vacuum's evidence, the affected relation, the candidate horizon, and the application responsible. If the workload allows a controlled test, observe cleanup after the owning application naturally ends the transaction. A subsequent improvement is useful evidence, but concurrent changes can still complicate attribution.

Use pg_blocking_pids for lock-blocking questions, not as a universal detector of cleanup retention. A transaction can retain a snapshot without appearing as the lock blocker of the vacuum operation you are inspecting. Lock waits and visibility horizons are related operational concerns but different mechanisms.

Also inspect statistics freshness. An estimated dead-tuple count may not immediately reflect the latest cleanup. Do not terminate a session just because one monitoring number remained high in a cached or delayed statistics snapshot.

Make intervention a separate decision

Canceling a statement and terminating a backend have different consequences. A cancellation can leave a transaction open or aborted until the application responds. Termination breaks the connection and rolls back its active transaction, potentially affecting user work and retry behavior. Neither should be automated from a simplistic “older than ten minutes” query without an approved operational policy.

The first response is often to contact the owning service, identify why it holds the transaction, and let it complete or roll back cleanly. Long-term remedies include bounded transaction scopes, appropriate idle-in-transaction timeouts, and moving external network waits outside database transactions. Test timeout behavior with the application's connection pool and error handling before rollout.

A good incident note states what was retained, by whom, why it affected cleanup, and what safe action released it. That turns a one-off database rescue into an application lifecycle improvement without teaching the team to kill the oldest PID whenever storage grows.

Sources and review scope

• Activity and replication statistics
• Prepared transactions view
• Replication slots view
• Client connection timeouts