Re: [PATCH] Periodically refresh database frozen IDs during autovacuum

Hi Jingtang,

Thank you for the patch. I tried v2 and have a few notes.

What I tested
- master at 9f191dce, configured with --enable-cassert --enable-debug
--enable-tap-tests.
- v2 applies cleanly and builds without warnings.
- make check: 243/243 pass, with and without the patch.
- The xid_wraparound TAP tests pass with the patch (4 files, 23 tests).
- A manual VACUUM over 150 tables with old relfrozenxid, with
VAC_DATFROZENXID_UPDATE_INTERVAL changed to 10 seconds (a separate build,
only for testing): age(datfrozenxid) of the database went
750025 -> 450009 -> 150003 -> 0 in steps. Without the patch it stayed at
750025 until the very end and then dropped to 0. So the idea works there.
- Cost of one refresh (VACUUM (ONLY_DATABASE_STATS)) with about 30000 tables
was around 4 ms (3.8 to 4.1 ms in five runs), so the cost looks small.

Points
1. The interval is fixed at 10 minutes (pgstat.h,
VAC_DATFROZENXID_UPDATE_INTERVAL).
Would a GUC, or at least a note in the docs, make sense? The docs in
maintenance.sgml do not say when datfrozenxid is advanced, and the patch
does not touch them. I also did not see tests added.

2. The gate in ForceTransactionIdLimitUpdate() only looks at XIDs
(nextXid >= xidVacLimit). I could not find an equivalent for multixacts, so
a database near multixact wraparound would not get the periodic refresh.
This one is from reading the code. I tried a multixact scenario, but it did
not give a clear result, so I cannot say how it behaves in practice.

3. vacuum_datfrozenxid_update_due() is checked before
ConditionalLockDatabaseFrozenIds() and not checked again after the lock is
taken. Two workers could both pass the check and one could repeat the scan
right after the other. This is also from reading the code. I did not
observe it.

What I did not test
- Autovacuum anti-wraparound runs with the real 10 minute interval. My
autovacuum runs did not show a difference between the builds, because the
table order was not controlled (the catalogs were processed last), so I do
not claim anything about autovacuum from them.
- vacuumdb: datfrozenxid dropped only at the end in both builds in my run, so
I have no difference to report.
- Several workers competing for the lock, and the stats file format change.

Regards,
João Rito

Em qui., 12 de nov. de 2026 às 01:15, Jingtang Zhang
<mrdrivingduck(at)gmail(dot)com> escreveu:
>
> Hi,
>
> Thanks for looking.
>
> > - Just updating the datfrozenxid at the end of an autovacuum and a
> > multi-relation vacuum is problematic and makes it more likely that
> > anti-wraparound measures are hit
> >
> > I think the same update-more-often logic needs to be done for multi-relation
> > vacuums.
>
> Agreed. In v2, I moved the conditional update logic into the common
> vacuum() path, covering both autovacuum and manual multi-relation VACUUM.
>
> I also tested manual multi-relation VACUUM with the tables ordered by
> age, oldest first. It showed the same behavior as the earlier autovacuum
> test: the database's frozen-XID age decreased during the run, rather
> than only at the end.
>
> > - Calling vac_update_datfrozenxid() unconditionally in do_autovacuum(), even
> > though relfrozenxid wasn't updated, is wasteful and makes more frequent
> > calls problematic
>
> Yes. v2 uses a shared per-database counter to track advances of
> relfrozenxid or relminmxid. Periodic updates require pending progress,
> ForceTransactionIdLimitUpdate() to return true, and the refresh interval
> to have elapsed. Keeping an interval lets us batch progress and reduce
> repeated pg_class scans in databases with many tables.
>
> > The justification for calling vac_update_datfrozenxid() even if we didn't
> > vacuum anything doesn't mean we can't push the responsibility for other
> > calls to vac_update_datfrozenxid() down. If we do, we can make those
> > updates conditional.
>
> I would keep the existing final calls in vacuum() and do_autovacuum()
> as fallbacks, while making the periodic calls conditional. I considered
> moving the fallback responsibility to DROP TABLE or DROP DATABASE,
> but that would add latency to DDL and require care around rollback.
> VACUUM still seems the right place for this maintenance.
>
> > - We shouldn't block waiting for LockDatabaseFrozenIds in most cases, because
> > that means somebody else is already doing the work.
>
> Yes. Periodic calls to vac_update_datfrozenxid() use conditional locking.
> If the lock is already held, the worker skips the update and continues
> processing tables.
>
> Overall, I think more frequent updates to the database frozen-ID
> horizons are beneficial, but we need to balance that against their cost.
> Does anyone have suggestions on how best to strike that balance?
>
> Attached is v2.
>
> ---
> Regards,
> Jingtang