From 1dcfa7cf68746ef908ed4bef58e2d7a8a8d26d3f Mon Sep 17 00:00:00 2001 From: Jingtang Zhang Date: Tue, 3 Nov 2026 00:28:15 +0800 Subject: [PATCH v1] Periodically refresh database frozen IDs during autovacuum Autovacuum normally publishes database-wide freezing progress only after finishing its table list. With many tables, the database horizon can stay stale long after the oldest tables have been processed, delaying updates to the transaction ID allocation limits despite table prioritization. Attempt a refresh between tables at a fixed ten-minute interval, using a shared per-database completion time to throttle periodic attempts across workers. Periodic attempts acquire the refresh lock conditionally so that a busy updater does not stall table processing. Retain the existing blocking refresh at worker completion as a final fallback. Record completion even when neither horizon advances, so that a pinned horizon does not defeat throttling. --- src/backend/commands/vacuum.c | 18 ++++++++-- src/backend/postmaster/autovacuum.c | 38 +++++++++++++++++--- src/backend/storage/lmgr/lmgr.c | 13 +++++++ src/backend/utils/activity/pgstat_database.c | 21 ++++++++++- src/include/commands/vacuum.h | 2 +- src/include/pgstat.h | 4 ++- src/include/storage/lmgr.h | 1 + 7 files changed, 87 insertions(+), 10 deletions(-) diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index a257dd8d21e..96263c3a734 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -707,7 +707,7 @@ vacuum(List *relations, const VacuumParams *params, BufferAccessStrategy bstrate /* * Update pg_database.datfrozenxid, and truncate pg_xact if possible. */ - vac_update_datfrozenxid(); + vac_update_datfrozenxid(false); } } @@ -1624,6 +1624,10 @@ vac_update_relstats(Relation relation, * If we are able to advance either pg_database value, also try to * truncate pg_xact and pg_multixact. * + * If nowait is true, skip the update when the database frozen-ID + * lock cannot be acquired immediately. Once acquired, the lock is + * held until transaction end. + * * We violate transaction semantics here by overwriting the database's * existing pg_database tuple with the new values. This is reasonably * safe since the new values are correct whether or not this transaction @@ -1631,7 +1635,7 @@ vac_update_relstats(Relation relation, * behind after a VACUUM. */ void -vac_update_datfrozenxid(void) +vac_update_datfrozenxid(bool nowait) { HeapTuple tuple; Form_pg_database dbform; @@ -1653,7 +1657,13 @@ vac_update_datfrozenxid(void) * avoids calling vac_truncate_clog() with a datfrozenxid preceding a * datfrozenxid passed to an earlier vac_truncate_clog() call. */ - LockDatabaseFrozenIds(ExclusiveLock); + if (nowait) + { + if (!ConditionalLockDatabaseFrozenIds(ExclusiveLock)) + return; + } + else + LockDatabaseFrozenIds(ExclusiveLock); /* * Initialize the "min" calculation with @@ -1831,6 +1841,8 @@ vac_update_datfrozenxid(void) if (dirty || ForceTransactionIdLimitUpdate()) vac_truncate_clog(newFrozenXid, newMinMulti, lastSaneFrozenXid, lastSaneMinMulti); + + pgstat_report_frozenxid_update(); } diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 0ec01c5da6f..36b9a079ee1 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -149,6 +149,9 @@ int Log_autoanalyze_min_duration = 600000; #define MIN_AUTOVAC_SLEEPTIME 100.0 /* milliseconds */ #define MAX_AUTOVAC_SLEEPTIME 300 /* seconds */ +/* Interval for periodic database frozen-ID updates, in seconds. */ +#define AUTOVAC_DATFROZENXID_UPDATE_INTERVAL (10 * 60) + /* * Variables to save the cost-related storage parameters for the current * relation being vacuumed by this autovacuum worker. Using these, we can @@ -371,6 +374,7 @@ static int db_comparator(const void *a, const void *b); static void autovac_recalculate_workers_for_balance(void); static void do_autovacuum(void); +static bool autovacuum_datfrozenxid_update_due(void); static void FreeWorkerInfo(int code, Datum arg); static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map, @@ -2566,6 +2570,14 @@ do_autovacuum(void) * cancel at this point.) */ QueryCancelPending = false; + + /* + * Periodically try to publish freezing progress before finishing + * the whole list. Use the database's shared completion time for + * throttling, and skip the update if its frozen-ID lock is busy. + */ + if (autovacuum_datfrozenxid_update_due()) + vac_update_datfrozenxid(true); } PG_CATCH(); { @@ -2685,8 +2697,9 @@ deleted: MemoryContextSwitchTo(TopTransactionContext); /* - * Update pg_database.datfrozenxid, and truncate pg_xact if possible. We - * only need to do this once, not after each table. + * Update pg_database.datfrozenxid, and truncate pg_xact if possible. + * We periodically attempt this between tables and force one final update + * at the end. * * Even if we didn't vacuum anything, it may still be important to do * this, because one indirect effect of vac_update_datfrozenxid() is to @@ -2705,12 +2718,28 @@ deleted: * worker has already done it, or will do so when it finishes. */ if (did_vacuum || !found_concurrent_worker) - vac_update_datfrozenxid(); + vac_update_datfrozenxid(false); /* Finally close out the last transaction. */ CommitTransactionCommand(); } +/* + * Check the shared completion time so that all workers in a database use the + * same refresh interval. A clock adjustment must not delay updates forever. + */ +static bool +autovacuum_datfrozenxid_update_due(void) +{ + PgStat_StatDBEntry *dbentry = pgstat_fetch_stat_dbentry(MyDatabaseId); + TimestampTz last_update = dbentry ? dbentry->last_frozenxid_update : 0; + TimestampTz now = GetCurrentTimestamp(); + + return last_update == 0 || last_update > now || + TimestampDifferenceExceedsSeconds(last_update, now, + AUTOVAC_DATFROZENXID_UPDATE_INTERVAL); +} + /* * Execute a previously registered work item. */ @@ -2914,7 +2943,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, /* * Select VACUUM options. Note we don't say VACOPT_PROCESS_TOAST, so * that vacuum() skips toast relations. Also note we tell vacuum() to - * skip vac_update_datfrozenxid(); we'll do that separately. + * skip vac_update_datfrozenxid(); we'll try it periodically between + * tables and force a final update at the end. */ tab->at_params.options = (dovacuum ? (VACOPT_VACUUM | diff --git a/src/backend/storage/lmgr/lmgr.c b/src/backend/storage/lmgr/lmgr.c index 2ccf7237fee..bc79d14e768 100644 --- a/src/backend/storage/lmgr/lmgr.c +++ b/src/backend/storage/lmgr/lmgr.c @@ -497,6 +497,19 @@ LockDatabaseFrozenIds(LOCKMODE lockmode) (void) LockAcquire(&tag, lockmode, false, false); } +/* + * Acquire the database frozen-ID lock without waiting for another updater. + */ +bool +ConditionalLockDatabaseFrozenIds(LOCKMODE lockmode) +{ + LOCKTAG tag; + + SET_LOCKTAG_DATABASE_FROZEN_IDS(tag, MyDatabaseId); + + return (LockAcquire(&tag, lockmode, false, true) != LOCKACQUIRE_NOT_AVAIL); +} + /* * LockPage * diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c index 7f3bc016593..43b5de877f1 100644 --- a/src/backend/utils/activity/pgstat_database.c +++ b/src/backend/utils/activity/pgstat_database.c @@ -74,6 +74,24 @@ pgstat_report_autovac(Oid dboid) pgstat_unlock_entry(entry_ref); } +/* + * Record completion even when the database's frozen IDs did not advance, + * so that autovacuum workers can throttle their periodic update attempts. + * The caller must still hold the database frozen-ID lock. + */ +void +pgstat_report_frozenxid_update(void) +{ + PgStat_EntryRef *entry_ref; + PgStatShared_Database *dbentry; + + entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_DATABASE, + MyDatabaseId, InvalidOid, false); + dbentry = (PgStatShared_Database *) entry_ref->shared_stats; + dbentry->stats.last_frozenxid_update = GetCurrentTimestamp(); + pgstat_unlock_entry(entry_ref); +} + /* * Report a Hot Standby recovery conflict. */ @@ -460,8 +478,9 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) PGSTAT_ACCUM_DBCOUNT(tuples_updated); PGSTAT_ACCUM_DBCOUNT(tuples_deleted); - /* last_autovac_time is reported immediately */ + /* last_autovac_time and last_frozenxid_update are reported immediately */ Assert(pendingent->last_autovac_time == 0); + Assert(pendingent->last_frozenxid_update == 0); PGSTAT_ACCUM_DBCOUNT(conflict_tablespace); PGSTAT_ACCUM_DBCOUNT(conflict_lock); diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 89fa1fcd261..ecb1ad47403 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -394,7 +394,7 @@ extern void vac_update_relstats(Relation relation, extern bool vacuum_get_cutoffs(Relation rel, const VacuumParams *params, struct VacuumCutoffs *cutoffs); extern bool vacuum_xid_failsafe_check(const struct VacuumCutoffs *cutoffs); -extern void vac_update_datfrozenxid(void); +extern void vac_update_datfrozenxid(bool nowait); extern void vacuum_delay_point(bool is_analyze); extern bool vacuum_is_permitted_for_relation(Oid relid, Form_pg_class reltuple, uint32 options, bool missing_ok); diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 187d82c96fe..62966a88610 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -275,7 +275,7 @@ typedef struct PgStat_TableXactStatus * ------------------------------------------------------------ */ -#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBD +#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBE typedef struct PgStat_ArchiverStats { @@ -433,6 +433,7 @@ typedef struct PgStat_StatDBEntry PgStat_Counter tuples_updated; PgStat_Counter tuples_deleted; TimestampTz last_autovac_time; + TimestampTz last_frozenxid_update; PgStat_Counter conflict_tablespace; PgStat_Counter conflict_lock; PgStat_Counter conflict_snapshot; @@ -731,6 +732,7 @@ extern PgStat_Lock *pgstat_fetch_stat_lock(void); extern void pgstat_drop_database(Oid databaseid); extern void pgstat_report_autovac(Oid dboid); +extern void pgstat_report_frozenxid_update(void); extern void pgstat_report_recovery_conflict(int reason); extern void pgstat_report_deadlock(void); extern void pgstat_prepare_report_checksum_failure(Oid dboid); diff --git a/src/include/storage/lmgr.h b/src/include/storage/lmgr.h index 2a985ce5e15..dcd4ad6a105 100644 --- a/src/include/storage/lmgr.h +++ b/src/include/storage/lmgr.h @@ -64,6 +64,7 @@ extern int RelationExtensionLockWaiterCount(Relation relation); /* Lock to recompute pg_database.datfrozenxid in the current database */ extern void LockDatabaseFrozenIds(LOCKMODE lockmode); +extern bool ConditionalLockDatabaseFrozenIds(LOCKMODE lockmode); /* Lock a page (currently only used within indexes) */ extern void LockPage(Relation relation, BlockNumber blkno, LOCKMODE lockmode); base-commit: 36a56229765c405b7e3655c3cf3a4fec2c0bc2b0 -- 2.43.7