Hi hackers, I would like to propose a patch that adds a new GUC, autovacuum_warning (off by default), which logs a message when an autovacuum worker tries to process a table that is already being vacuumed by another worker.
Motivation: The goal is to give DBAs an early signal that autovacuum is no longer keeping up, while there is still time to tune the configuration and avoid the worst outcomes (severe bloat, transaction ID wraparound). Today, by the time the existing wraparound warnings or anti-wraparound vacuums appear, the system is already in trouble; there is no earlier hint that autovacuum is becoming saturated. This is modeled after checkpoint_warning, which logs when checkpoints are happening too frequently so that DBAs know to raise max_wal_size. In the same spirit, autovacuum_warning gives DBAs an early, actionable signal that autovacuum is struggling, so they can react and tune the relevant parameters promptly. Design: When autovacuum_warning is enabled and a worker skips a table because another worker is already vacuuming it, the following is written to the server log: LOG: autovacuum: table "public.foo" is already being vacuumed by another worker HINT: Consider tuning the autovacuum configuration parameters. The hint is intentionally generic: a collision has no single root cause, and pointing at one specific parameter could be misleading. Like checkpoint_warning, the message is emitted at LOG level and the feature is off by default, so it adds nothing for users who don't opt in. The patch is attached. Thoughts? -- Best regards, Shinya Kato NTT OSS Center
From 8e7133f6ed928047f4befc58535e59a8931064bb Mon Sep 17 00:00:00 2001 From: Shinya Kato <[email protected]> Date: Sat, 23 May 2026 14:24:41 +0900 Subject: [PATCH v1] Add autovacuum_warning to report concurrent vacuum collisions Introduce a new GUC, autovacuum_warning (off by default), that logs a message when an autovacuum worker attempts to process a table that is already being vacuumed by another worker. Such collisions are a sign that autovacuum may not be keeping up, so surfacing them helps operators diagnose the situation. This is modeled after checkpoint_warning, which similarly logs when checkpoints occur too frequently. The message carries a generic hint to tune the autovacuum configuration, since a collision has no single root cause and naming one parameter could be misleading. Author: Shinya Kato <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/ --- src/backend/postmaster/autovacuum.c | 7 +++++++ src/backend/utils/misc/guc_parameters.dat | 7 +++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/postmaster/autovacuum.h | 1 + 4 files changed, 16 insertions(+) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index a5a8db2ff88..6f3bf1e498f 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -141,6 +141,7 @@ double autovacuum_vacuum_insert_score_weight = 1.0; double autovacuum_analyze_score_weight = 1.0; double autovacuum_vac_cost_delay; int autovacuum_vac_cost_limit; +bool autovacuum_warning; int Log_autovacuum_min_duration = 600000; int Log_autoanalyze_min_duration = 600000; @@ -2424,6 +2425,12 @@ do_autovacuum(void) LWLockRelease(AutovacuumLock); if (skipit) { + if (autovacuum_warning) + ereport(LOG, + (errmsg("autovacuum: table \"%s.%s\" is already being vacuumed by another worker", + get_namespace_name(get_rel_namespace(relid)), + get_rel_name(relid)), + errhint("Consider tuning the autovacuum configuration parameters."))); LWLockRelease(AutovacuumScheduleLock); continue; } diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index afaa058b046..bd35dfab839 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -289,6 +289,13 @@ max => 'INT_MAX', }, +{ name => 'autovacuum_warning', type => 'bool', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM', + short_desc => 'Enables warnings when autovacuum cannot keep up.', + long_desc => 'Write a message to the server log if autovacuum attempts to process a table that is already being vacuumed by another worker.', + variable => 'autovacuum_warning', + boot_val => 'false', +}, + { name => 'autovacuum_work_mem', type => 'int', context => 'PGC_SIGHUP', group => 'RESOURCES_MEM', short_desc => 'Sets the maximum memory to be used by each autovacuum worker process.', long_desc => '-1 means use "maintenance_work_mem".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index ac38cddaaf9..44375fd429d 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -756,6 +756,7 @@ #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit +#autovacuum_warning = off # warn when autovacuum can't keep up # - Cost-Based Vacuum Delay - diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h index 8954f6b28ee..fa2ec5ef97d 100644 --- a/src/include/postmaster/autovacuum.h +++ b/src/include/postmaster/autovacuum.h @@ -48,6 +48,7 @@ extern PGDLLIMPORT double autovacuum_multixact_freeze_score_weight; extern PGDLLIMPORT double autovacuum_vacuum_score_weight; extern PGDLLIMPORT double autovacuum_vacuum_insert_score_weight; extern PGDLLIMPORT double autovacuum_analyze_score_weight; +extern PGDLLIMPORT bool autovacuum_warning; extern PGDLLIMPORT int Log_autovacuum_min_duration; extern PGDLLIMPORT int Log_autoanalyze_min_duration; -- 2.47.3
