Report oldest xmin source when autovacuum cannot remove tuples (Japan PostgreSQL Developer Meetup - PAIKAJI) English version

September 18, 2026
Shinya Kato

Transcript

1. Report oldest xmin source when autovacuum cannot remove tuples Shinya Kato Japan PostgreSQL Developer Meetup · September 18, 2026
2. 02 About me Shinya Kato @ShinyaKato_ I develop and support PostgreSQL I am now a Significant Contributor I will speak at PGConf.Asia Japanese version speakerdeck.com/shinyakato_
3. 03 How Vacuum operates Vacuum calculates the oldest xmin Vacuum starts Calculate oldest xmin ① Scanning heap 1 Vacuuming indexes Vacuuming heap ② Truncating heap Write log Vacuum removes dead tuples with this oldest xmin 3 Vacuum writes the result to the log 2 Cleaning up indexes ③ Vacuum scans ProcArray. It finds the minimum xid or xmin of all backends. Vacuum reads the xmin of the replication slots from ProcArray. This value is already aggregated. Only the minimum value stays. The reason for this value does not stay.
4. The Vacuum log does not give the reason The log gives only the number of dead tuples and the oldest xmin. =# VACUUM (VERBOSE) t; tuples: 0 removed, 20000 remain, 10000 are dead but not yet removable removable cutoff: 696, which was 2 XIDs old when operation ended The DBA must find the cause from the value 696. 04
5. Four causes prevent the removal of dead tuples Cause View to examine A long transaction pg_stat_activity.backend_xid pg_stat_activity.backend_xmin An uncommitted prepared transaction A read query on a standby, with hot_standby_feedback on A delay in logical replication pg_prepared_xacts pg_stat_replication.backend_xmin (if you do not use a slot) pg_replication_slots.xmin (if you use a slot) pg_replication_slots.catalog_xmin You must examine a different view for each cause. These views show only the current state. Thus you cannot examine the cause after it stops. Therefore I want to write the reason to the Vacuum log. 05
6. 06 My first proposal Vacuum starts Vacuum scans ProcArray and the replication slots again, but only if these conditions are true Calculate oldest xmin Scanning heap You run VACUUM (VERBOSE), or autovacuum runs longer than log_autovacuum_min_duration Vacuum could not remove some dead tuples Vacuuming indexes Vacuuming heap tuples: 0 removed, 20000 remain, 10000 are dead but not yet removable oldest xmin blocker: idle in transaction (pid = 12345) removable cutoff: 696, which was 2 XIDs old when operation ended Cleaning up indexes Truncating heap Write log scan again
7. The problem with my first proposal Vacuum scans again with the oldest xmin from the start of the run. If the cause stops before the log, Vacuum cannot report it. Example: a long transaction exists when Vacuum starts, but it completes before Vacuum ends tuples: 1 removed, 200000 remain, 100000 are dead but not yet removable removable cutoff: 1026, which was 35981 XIDs old when operation ended (Vacuum writes no oldest xmin blocker line) 07
8. 08 The alternative proposal Several reviewers asked why Vacuum does not find the cause while it calculates the oldest xmin. Vacuum starts Calculate oldest xmin ① keep while calculating Scanning heap Vacuuming indexes Vacuuming heap Cleaning up indexes Vacuum does not scan a second time. Truncating heap Write log Vacuum keeps the candidate xid and xmin separately while it calculates 2 Vacuum does not scan again. It writes the data from step ① 1 ② write only
9. Problems with the alternative proposal Vacuum reports incorrect data The session ends, but Vacuum reports its pid You delete the replication slot, but Vacuum reports its name procArray->replication_slot_xmin does not show if a standby is connected To know this, Vacuum must scan the replication slots again While Vacuum holds ProcArrayLock, it cannot know if the transaction is active or idle While Vacuum holds ProcArrayLock, it cannot read a slot name. ReplicationSlotControlLock is necessary. 09
10. 10 The combined proposal Vacuum starts Calculate oldest xmin ① scan after calculating Scanning heap Vacuuming indexes Vacuuming heap Cleaning up indexes Truncating heap Write log ② scan again Vacuum scans after it calculates the 1 oldest xmin. It finds the cause and keeps it Vacuum scans again at the log. If the 2 cause is still there, Vacuum writes it. If not, Vacuum uses the data from step ① If Vacuum uses the data from step ①, it adds the text at cutoff time. This shows that the data is old.
11. Output of the combined proposal Vacuum finds the cause tuples: 0 removed, 20000 remain, 10000 are dead but not yet removable oldest xmin blocker: idle in transaction (pid = 12345) removable cutoff: 696, which was 2 XIDs old when operation ended Vacuum does not find the cause tuples: 1 removed, 200000 remain, 100000 are dead but not yet removable oldest xmin blocker at cutoff time: idle in transaction (pid = 12345) removable cutoff: 1026, which was 35981 XIDs old when operation ended 11
12. Future work — a summary line Vacuum keeps all the candidates with the same xid or xmin, and writes a summary. tuples: 0 removed, 20000 remain, 10000 are dead but not yet removable oldest xmin blocker: idle in transaction (pid = 12345) logical replication slot "sub_slot" also holds this xmin 11 other processes hold snapshots at this xmin removable cutoff: 696, which was 2 XIDs old when operation ended Vacuum already collects all the candidates. It does not scan more. The DBA does not stop only one cause and see no effect Other diagnostic logs do the same (for example log_lock_waits) Proposed by Scott Ray · 2026-08-08 12