Re: VACUUM FREEZE vs plain VACUUM

On Thu, Jul 17, 2025 at 9:23 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>> On Thu, Jul 17, 2025 at 6:26 PM David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>>> On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr(at)gmail(dot)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.
<Redacted> lobster!