[PATCH] Periodically refresh database frozen IDs during autovacuum
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254529psql -h localhost -U postgresBuilt from patchset v1 (message #1), November 02, 2026 at 04:55 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254529_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254529_1 && git checkout t254529_1Patchset v1 (message #1) is on t254529_1
Hi,
Commit d7965d65, "Add rudimentary table prioritization to autovacuum"
[1]: https://github.com/postgres/postgres/commit/d7965d65fc5bb2139bc51c051c11428414c65160
so their per-table frozen-XID horizons can advance earlier. However, the
XID stop limit depends on the oldest database datfrozenxid, which
autovacuum normally refreshes only after a worker finishes its table
list. We have encountered a database with over 600,000 tables. Even
after the oldest tables are frozen, the database horizon can remain
stale for a long time, leaving the instance at risk of rejecting
transactions that need new XIDs.
This patch periodically refreshes the database's frozen-ID horizons
between tables. A shared per-database completion timestamp controls
periodic attempts at a fixed interval, currently set to ten minutes.
This is a tentative value for discussion, since more frequent updates
incur additional scanning overhead. I have not yet decided whether to
make the interval configurable via a GUC.
To prevent multiple workers from refreshing the same database
concurrently, periodic updates acquire the database frozen-ID lock
conditionally. If the lock is already held, another backend is handling
the database-level refresh, so the worker skips its attempt and
continues processing tables. The existing final blocking refresh is
retained as a fallback.
For testing, I constructed 1,000 tables with different frozen-XID ages,
all requiring aggressive vacuum, and enabled three autovacuum workers.
The interval was shortened to ten seconds for this test. As shown in the
attached figure, without the patch, the maximum table age fell steadily
while the database age remained flat until all tables finished at about
150 seconds. With the patch, the database age followed the declining
table age, updating roughly every ten seconds.
Feedback on the approach and interval would be welcome.
---
Regards,
Jingtang
[1]: https://github.com/postgres/postgres/commit/d7965d65fc5bb2139bc51c051c11428414c65160
On Tue, Nov 03, 2026 at 12:40:15AM +0800, Jingtang Zhang wrote:
This patch periodically refreshes the database's frozen-ID horizons
between tables.
My first question is why would we only refresh one score component and not
all of them?
--
nathan
Oh, forget about the graph. Attached.
For testing, I constructed 1,000 tables with different frozen-XID ages,
all requiring aggressive vacuum, and enabled three autovacuum workers.
The interval was shortened to ten seconds for this test. As shown in the
attached figure, without the patch, the maximum table age fell steadily
while the database age remained flat until all tables finished at about
150 seconds. With the patch, the database age followed the declining
table age, updating roughly every ten seconds.
---
Regards,
Jingtang
Attachments:
age-comparison.pngimage/png; name=age-comparison.pngDownload+2-3
Hi,
On 2026-11-03 00:40:15 +0800, Jingtang Zhang wrote:
This patch periodically refreshes the database's frozen-ID horizons
between tables. A shared per-database completion timestamp controls
periodic attempts at a fixed interval, currently set to ten minutes.
This is a tentative value for discussion, since more frequent updates
incur additional scanning overhead. I have not yet decided whether to
make the interval configurable via a GUC.To prevent multiple workers from refreshing the same database
concurrently, periodic updates acquire the database frozen-ID lock
conditionally. If the lock is already held, another backend is handling
the database-level refresh, so the worker skips its attempt and
continues processing tables. The existing final blocking refresh is
retained as a fallback.
I think theres some responsibilities that aren't quite right here (even before
your change). A few comments (both about the current state and the patch):
- 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.
- Calling vac_update_datfrozenxid() unconditionally in do_autovacuum(), even
though relfrozenxid wasn't updated, is wasteful and makes more frequent
calls problematic
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.
- We shouldn't block waiting for LockDatabaseFrozenIds in most cases, because
that means somebody else is already doing the work.
I suspect we should use something like the ForceTransactionIdLimitUpdate()
check at the end of vac_update_datfrozenxid() to determine whether to block
or not.
That seems like something independent of more frequently updating
datfrozenxid. So it probably should be a separate patch.
- The timing logic the patch introduces makes calls to
vac_update_datfrozenxid() independent of relfrozenxid having been updated,
that
Greetings,
Andres Freund
Hi,
On 2026-11-02 10:48:22 -0600, Nathan Bossart wrote:
On Tue, Nov 03, 2026 at 12:40:15AM +0800, Jingtang Zhang wrote:
This patch periodically refreshes the database's frozen-ID horizons
between tables.My first question is why would we only refresh one score component and not
all of them?
I think this isn't really about the scoring, it's about updating datfrozenxid
more often (i.e. basically *writing* progress more often).
I started looking at the patch because I got worried it was introducing
dynamic rescoring or such, which has potential of introducing situations where
we'll never get to some relations or such. But I don't think that is in this
patch.
Greetings,
Andres
On Mon, Nov 02, 2026 at 12:42:43PM -0500, Andres Freund wrote:
On 2026-11-02 10:48:22 -0600, Nathan Bossart wrote:
On Tue, Nov 03, 2026 at 12:40:15AM +0800, Jingtang Zhang wrote:
This patch periodically refreshes the database's frozen-ID horizons
between tables.My first question is why would we only refresh one score component and not
all of them?I think this isn't really about the scoring, it's about updating datfrozenxid
more often (i.e. basically *writing* progress more often).
My bad, I clearly misunderstood the proposal.
--
nathan