Tuple retention under an old snapshot

An old snapshot can prevent reclamation of obsolete versions. PostgreSQL 18's vacuum contract preserves versions that might still be visible to another transaction. This schedule holds a Repeatable Read snapshot over three updates to one jobs row.

VACUUM leaves the inspected tuple fields unchanged

A opens a long report transaction — one query, then it sits there idle.

A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

A> SELECT xmin, id, status FROM jobs;
 xmin | id | status 
------+----+--------
 1001 |  1 | new    
(1 row)

Meanwhile B churns through the row, leaving dead versions behind.

B> UPDATE jobs SET status = 'running' WHERE id = 1 RETURNING xmin, status;
 xmin | status  
------+---------
 1002 | running 
(1 row)

B> UPDATE jobs SET status = 'done' WHERE id = 1 RETURNING xmin, status;
 xmin | status 
------+--------
 1003 | done   
(1 row)

B> UPDATE jobs SET status = 'archived' WHERE id = 1 RETURNING xmin, status;
 xmin |  status  
------+----------
 1004 | archived 
(1 row)

B> SELECT lp, t_xmin, t_xmax, t_ctid
   FROM heap_page_items(get_raw_page('jobs', 0)) ORDER BY lp;
 lp | t_xmin | t_xmax | t_ctid 
----+--------+--------+--------
  1 |   1001 |   1002 | (0,2)  
  2 |   1002 |   1003 | (0,3)  
  3 |   1003 |   1004 | (0,4)  
  4 |   1004 |      0 | (0,4)  
(4 rows)

B> VACUUM jobs;
VACUUM

VACUUM completed. The following assertion checks unchanged tuple-chain fields while A retains its old snapshot.

B> SELECT lp, t_xmin, t_xmax, t_ctid
   FROM heap_page_items(get_raw_page('jobs', 0)) ORDER BY lp;
 lp | t_xmin | t_xmax | t_ctid 
----+--------+--------+--------
  1 |   1001 |   1002 | (0,2)  
  2 |   1002 |   1003 | (0,3)  
  3 |   1003 |   1004 | (0,4)  
  4 |   1004 |      0 | (0,4)  
(4 rows)

And indeed: A still reads the version from before all three updates.

A> SELECT id, status FROM jobs;
 id | status 
----+--------
  1 | new    
(1 row)

A> COMMIT;
COMMIT

B> VACUUM jobs;
VACUUM

Same command, a moment after A commits — now the three dead versions are gone.

B> SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid
   FROM heap_page_items(get_raw_page('jobs', 0)) ORDER BY lp;
 lp | lp_flags | t_xmin | t_xmax | t_ctid 
----+----------+--------+--------+--------
  1 |        2 |        |        |        
  2 |        0 |        |        |        
  3 |        0 |        |        |        
  4 |        1 |   1004 |      0 | (0,4)  
(4 rows)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The first VACUUM completes but the inspected lp, t_xmin, t_xmax and t_ctid fields remain unchanged. This is not a byte-for-byte comparison of the whole page, nor proof that VACUUM did no other work. A still reads only the original status new; its snapshot does not see each intermediate version. After A commits, the second VACUUM's asserted page fields show one redirect, two unused slots and the current tuple.

† A retained snapshot's removal horizon can constrain reclamation in other tables of the same database, even tables not read by that transaction. This inference follows PostgreSQL 18's backend_xmin horizon and vacuum's visibility requirement. The single-table transcript does not execute this cross-table guarantee. It does not imply all VACUUM or autovacuum work stops, or that every long transaction holds a transaction-wide old snapshot.

Spotting the offender

This diagnostic query is illustrative; its columns are Documented contracts, not a new Scenario result:

SELECT pid, state, application_name,
       age(backend_xmin) AS snapshot_age_xids,
       now() - xact_start AS tx_duration
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Inspect application state as well as age. An idle Read Committed transaction does not necessarily retain a Repeatable Read snapshot. Replication slots and prepared transactions can also constrain maintenance. Monitoring other sessions' details requires appropriate privileges.

The PostgreSQL 18 client-setting contracts document idle_in_transaction_session_timeout for idle open transactions, transaction_timeout for session transaction duration (introduced in 17, excluding prepared transactions), and statement_timeout for individual statement duration. Defaults are disabled for these timeouts; their interactions and suitable limits need application decisions. No timeout execution occurs in this chapter. Chapter 8 supplies the operational follow-up.

Further reading

• PostgreSQL 18: Recovering Disk Space
• PostgreSQL 18: pg_stat_activity
• The same lesson on MySQL