Hi all.
After reading the manual's (v9.2) Chapter 23 and in particular sections 23.1.5 i believe i've got some confusion with respect to VACUUM's details.
Assume we're at t0 and we just VACUUM'ed (simple, not FREEZE) a specific table and txid_current()=1500 and the pg_class entry for this table shows relfrozenxid=700.
Q: The 'relfrozenxid' is the freeze cutoff XID for this table which translates to: for this table all rows that show to have been modified by this XID, i.e.700, will always appear to be "in the past" to all normal transactions regardless of wraparound issues. Correct?
Docs: "vacuum_freeze_min_age controls how old an XID value has to be before it’s replaced with FrozenXID."
Q: So on a full table scan, a row's XID is updated to FrozenXID only if it's more than "vacuum_freeze_min_age" Xons old i.e. at least vacuum_freeze_min_age Xons have occured since this XID. Otherwise it's left at its current value. Correct?
Q: FrozenXID is the curren't VACUUM operation's XID?
Docs: "VACUUM normally skips pages that don’t have any dead row versions, but those pages might still have row
versions with old XID values. To ensure all old XIDs have been replaced by FrozenXID, a scan of
the whole table is needed. vacuum_freeze_table_age controls when VACUUM does that: a whole table
sweep is forced if the table hasn’t been fully scanned for vacuum_freeze_table_age minus
vacuum_freeze_min_age transactions."
Q: A full table scan with the intention of updating old row XIDs to FrozenXID is forced after (vacuum_freeze_table_age -
vacuum_freeze_min_age) Xons have occured since when? The last VACUUM or the last full table scan i.e. for example VACUUM FREEZE?
Q: In our example if e.g. vacuum_freeze_min_age=20000 and vacuum_freeze_table_age=50000 and no VACUUM occurs during 50000-20000=30000 Xons, then i'll get a forced full table scan where active row XIDs will be set to the new FrozenXID i.e. the current xid which is 31500 since I was at xid=1500 when i did the last VACUUM. But this will only affect the rows that are more than vacuum_freeze_min_age i.e. 20000 Xon's old. This means only rows with XID from 1500 to 11500. Is that anywhere near the truth?
Docs: "When VACUUM scans the whole table, after it’s finished
age(relfrozenxid) should be a little more than the vacuum_freeze_min_age
setting that was used (more by the number of transactions started since
the VACUUM started)."
Q: At a non-production db I do:
# SHOW vacuum_freeze_min_age ;
vacuum_freeze_min_age
-----------------------
50000000
(1 row)
# VACUUM FREEZE foo;
# SELECT relname,relfrozenxid,age(relfrozenxid) FROM pg_class WHERE relkind='r' and relname='foo';
relname | relfrozenxid | age
---------+--------------+-----
foo | 2880 | 0
(1 row)
I believe there is an error here. The age comes out 0 and not 50000000. Missing something or is it an error?
So, to conclude, VACUUM has 2 distinct tasks:
(i) to reclaim space by dead rows i.e. row versions
that can no longer be seen by any active or future Xon and
(ii)
avoid the wraparound issue by doing a full table scan which updates the
row XIDs to FrozenXID before 2 billion Xons have passed. Correct?
Q:
The first task updates the free space maps and happens when we do a
simple VACUUM; this does not give the free space back to the OS (this is
done only by VACUUM FULL). Correct?
Q: The second task, a full table scan,
only happens when:
(i) we do a manual VACUUM FREEZE or
(ii) all pages of this table happen to have at least 1 dead row, thus will be examined by a simple VACUUM or
(iii) more than vacuum_freeze_table_age Xons have occured since the pg_class.relfrozenxid of this table
Correct?
TIA,
--thalis k.
