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
v2-0001-Periodically-refresh-database-frozen-IDs-during-VACUUM.patch
Description: Binary data
