Hi, Commit d7965d65, "Add rudimentary table prioritization to autovacuum" [1], enables autovacuum to prioritize tables in urgent need of freezing, 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
v1-0001-Periodically-refresh-database-frozen-IDs-during-a.patch
Description: Binary data
