On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:Does VACUUM FREEZE do something extra or special than to defer autovacuum for an extra 50,000,000 transactions?
It effectively resets the pseudo-counter(s) that autovacuum uses to determine when next it should perform an aggressive scan. Or, put differently, it does exactly what autovacuum would do when the pseudo-counter(s) hit their thresholds. The act of doing that thing effectively resets said counters to zero at that moment (absent concurrent activity).
That seems to be what I said. Or am I still missing something?
Well, it would defer autovacuum freeze for 60,000,000 if no new rows were inserted into your table in the subsequent 10,000,000 transactions…and autovacuum would run (but not aggressively) if you performed a bunch of deletes or updates…
Even on that just-frozen, never-modified table, age(relfrozenxid) grows towards autovacuum_freeze_max_age.
(dba.v_child_xid_age is a view into pg_class that full joins itself to also show relfrozenxid_age of the toasts associated with tables. That's not relevant now, so only showing these two columns.)
I did VACUUM FREEZE on css.document_comment_annotation_rp11_y2015m03 and cds.cdsdocument_rp11_y2015m03
but plain VACUUM on cds.cdsdocument_rp11_y2015m03.
Eventually, relfrozenxid_age of those tables will hit vacuum_freeze_min_age, and autovacuum will run on them. It'll just happen sooner on cds.cdsdocument_rp11_y2015m03.
TAPb=# select table_name, relfrozenxid_age
from dba.v_child_xid_age
where table_name like '%_y2015%'
order by 2
limit 5;
table_name | relfrozenxid_age
-----------------------------------------------+------------------
css.document_comment_annotation_rp11_y2015m03 | 74300
cds.cdsdocument_rp11_y2015m03 | 75237
cds.cdsdocument_rp11_y2015m09 | 50074419
cds.cdssubbatch_rp11_y2015m03 | 145069780
cds.cdssubbatch_rp11_y2015m09 | 145069780
(5 rows)
-- Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
