On Wed, Mar 18, 2026 at 2:23 AM Daniil Davydov <[email protected]> wrote:
>
> Hi,
>
> On Tue, Mar 17, 2026 at 11:51 PM Masahiko Sawada <[email protected]>
> wrote:
> >
> > I find the current behavior of the autovacuum_parallel_workers storage
> > parameter somewhat unintuitive for users. The documentation currently
> > states:
> >
> > + <para>
> > + Sets the maximum number of parallel autovacuum workers that can
> > process
> > + indexes of this table.
> > + The default value is -1, which means no parallel index vacuuming for
> > + this table. If value is 0 then parallel degree will computed based on
> > + number of indexes.
> > + Note that the computed number of workers may not actually be
> > available at
> > + run time. If this occurs, the autovacuum will run with fewer workers
> > + than expected.
> > + </para>
> >
> > It is quite confusing that setting the value to 0 does not actually
> > disable the parallel vacuum. In many other PostgreSQL parameters, 0
> > typically means "off" or "no workers." I think that this parameter
> > should behave as follows:
> >
> > -1: Use the value of autovacuum_max_parallel_workers (GUC) as the
> > limit (fallback).
> > >=0: Use the specified value as the limit, capped by
> > >autovacuum_max_parallel_workers. (Specifically, setting this to 0 would
> > >disable parallel vacuum for the table).
> >
>
> Actually we have several places in the code where "-1" means disabled and "0"
> means choosing a parallel degree based on the number of indexes. Since this
> is an inner logic, I agree that we should make our parameter more intuitive
> to the user. But this will make the code a bit confusing.
Yes, we already have such a code for PARALLEL option for the VACUUM command:
/*
* Disable parallel vacuum, if user has specified parallel degree
* as zero.
*/
if (nworkers == 0)
params.nworkers = -1;
else
params.nworkers = nworkers;
I guess it's better that autovacuum codes also somewhat follow this
code for better consistency.
>
> > Currently, the patch implements parallel autovacuum as an "opt-in"
> > style. That is, even after setting the GUC to >0, users must manually
> > set the storage parameter for each table. This assumes that users
> > already know exactly which tables need parallel vacuum.
> >
> > However, I believe it would be more intuitive to let the system decide
> > which tables are eligible for parallel vacuum based on index size and
> > count (via min_parallel_index_scan_size, etc.), rather than forcing
> > manual per-table configuration. Therefore, I'm thinking we might want
> > to make it "opt-out" style by default instead:
> >
> > - Set the default value of the storage parameter to -1 (i.e., fallback to
> > GUC).
> > - the default value of the GUC autovacuum_max_parallel_workers at 0.
> >
> > With this configuration:
> >
> > - Parallel autovacuum is disabled by default.
> > - Users can enable it globally by simply setting the GUC to >0.
> > - Users can still disable it for specific tables by setting the
> > storage parameter to 0.
> >
> > What do you think?
>
> I'm afraid that I can't agree with you here. As I wrote above [1], the
> parallel a/v feature will be useful when a user has a few huge tables with
> a big amount of indexes. Only these tables require parallel processing and a
> user knows about it.
Isn't it a case where users need to increase
min_parallel_index_scan_size? Suppose that there are two tables that
are big enough and have enough indexes, it's more natural to me to use
parallel vacuum for both tables without user manual settings.
> If we implement the feature as you suggested, then after setting the
> av_max_parallel_workers to N > 0, the user will have to manually disable
> processing for all tables except the largest ones. This will need to be done
> to ensure that parallel workers are launched specifically to process the
> largest tables and not wasting on the processing of little ones.
>
> I.e. I'm proposing a design that will require manual actions to *enable*
> parallel a/v for several large tables rather than *disable* it for all of
> the rest tables in the cluster. I'm sure that's what users want.
>
> Allowing the system to decide which tables to process in parallel is a good
> way from a design perspective. But I'm thinking of the following example :
> Imagine that we have a threshold, when exceeded, parallel a/v is used.
> Several a/v workers encounter tables which exceed this threshold by 1_000 and
> each of these workers decides to launch a few parallel workers. Another a/v
> worker encounters a table which is beyond this threshold by 1_000_000 and
> tries to launch N parallel workers, but facing the max_parallel_workers
> shortage. Thus, processing of this table will take a very long time to
> complete due to lack of resources. The only way for users to avoid it is to
> disable parallel a/v for all tables, which exceeds the threshold and are not
> of particular interest.
I think the same thing happens even with the current design as long as
users misconfigure max_parallel_workers, no? Setting
autovacuum_max_parallel_workers to >0 would mean that users want to
give additional resources for autovacuums in general, I think it makes
sense to use parallel vacuum even for tables which exceed the
threshold by 1000.
Users who want to use parallel autovacuum would have to set
max_parallel_workers (and max_worker_processes) high enough so that
each autovacuum worker can use parallel workers. If resource
contention occurs, it's a sign that the limits are not configured
properly.
> >
> > +{ name => 'autovacuum_max_parallel_workers', type => 'int', context
> > => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',
> > + short_desc => 'Maximum number of parallel workers that a single
> > autovacuum worker can take from bgworkers pool.',
> > + variable => 'autovacuum_max_parallel_workers',
> > + boot_val => '2',
> > + min => '0',
> > + max => 'MAX_BACKENDS',
> > +},
> >
> > How about rephrasing the short description to "Maximum number of
> > parallel processes per autovacuum operation."?
>
> I'm not sure if this phrase will be understandable to the user.
> I don't see any places where we would define the "autovacuum operation"
> concept, so I suppose it could be ambiguous. What about "Maximum number of
> parallel processes per autovacuuming of one table"?
"autovacuuming of one table" sounds unnatural to me. How about
"Maximum number of parallel workers that can be used by a single
autovacuum worker."?
>
> > We check only the server logs throughout the new tap tests. I think we
> > should also confirm that the autovacuum successfully completes. I've
> > attached the proposed change to the tap tests.
> >
>
> I agree with proposed changes. BTW, don't we need to reduce the strings
> length to 80 characters in the tests? In some tests, this rule is followed,
> and in some it is not.
Yeah, pgperltidy should be run for new tests.
> Thank you very much for the review and proposed patches!
> Please, see an updated set of patches. Note that the "logging for autovacuum"
> is considered as the first patch now.
Thank you for updating the patches!
The 0001 patch looks good to me. I've updated the commit message and
attached it. I'm going to push the patch, barring any objections.
While we need more discussion on the above points (opt-in vs.
opt-out), I think that the rest of the patches are getting close.
Regarding the documentation changes, I find that the current patch
needs more explanation at appropriate sections. I think we need to:
1. describe the new autovacuum_max_parallel_workers GUC parameter (in
config.sgml)
2. describe the new autovacuum_parallel_workers storage parameter (in
create_table.sgml)
3. mention that autovacuum could use parallel vacuum (in maintenance.sgml).
I think that part 1 should include the basic explanation of the GUC
parameter as well as how the number of workers is decided (which could
be similar to the description for PARALLEL options of the VACUUM
command). Part 2 can explain the storage parameter as follow:
Per-table value for <xref linkend="guc-autovacuum-max-parallel-workers"/>
parameter. If -1 is specified,
<varname>autovacuum_max_parallel_workers</varname>
value will be used. The default value is 0.
Part 3 can briefly mention that autovacuum can perform parallel vacuum
with parallel workers capped by autovacuum_max_parallel_workers as
follow:
For tables with the <xref linkend="reloption-autovacuum-parallel-workers"/>
storage parameter set, an autovacuum worker can perform index vacuuming and
index cleanup with background workers. The number of workers launched by
a single autovacuum worker is limited by the
<xref linkend="guc-autovacuum-max-parallel-workers"/>.
What do you think?
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
From 31592aa698726b0ec3a72d5c2bac59f5ef9f2806 Mon Sep 17 00:00:00 2001
From: Daniil Davidov <[email protected]>
Date: Mon, 16 Mar 2026 19:01:05 +0700
Subject: [PATCH v30] Add parallel vacuum worker usage to VACUUM (VERBOSE) and
autovacuum logs.
This commit adds both the number of parallel workers planned and the
number of parallel workers actually launched to the output of
VACUUM (VERBOSE) and autovacuum logs.
Previously, this information was only reported as an INFO message
during VACUUM (VERBOSE), which meant it was not included in autovacuum
logs in practice. Although autovacuum does not yet support parallel
vacuum, a subsequent patch will enable it and utilize these logs in
its regression tests. This change also improves observability by
making it easier to verify if parallel vacuum is utilizing the
expected number of workers.
Author: Daniil Davydov <[email protected]>
Reviewed-by: Masahiko Sawada <[email protected]>
Reviewed-by: Sami Imseih <[email protected]>
Discussion: https://postgr.es/m/CACG=ezZOrNsuLoETLD1gAswZMuH2nGGq7Ogcc0QOE5hhWaw=c...@mail.gmail.com
---
src/backend/access/heap/vacuumlazy.c | 31 +++++++++++++++++++++++++--
src/backend/commands/vacuumparallel.c | 23 ++++++++++++++------
src/include/commands/vacuum.h | 28 ++++++++++++++++++++++--
src/tools/pgindent/typedefs.list | 2 ++
4 files changed, 74 insertions(+), 10 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 82c5b28e0ad..c57432670e7 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -343,6 +343,13 @@ typedef struct LVRelState
int num_index_scans;
int num_dead_items_resets;
Size total_dead_items_bytes;
+
+ /*
+ * Total number of planned and actually launched parallel workers for
+ * index vacuuming and index cleanup.
+ */
+ PVWorkerUsage worker_usage;
+
/* Counters that follow are only for scanned_pages */
int64 tuples_deleted; /* # deleted from table */
int64 tuples_frozen; /* # newly frozen */
@@ -781,6 +788,11 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
vacrel->new_all_visible_all_frozen_pages = 0;
vacrel->new_all_frozen_pages = 0;
+ vacrel->worker_usage.vacuum.nlaunched = 0;
+ vacrel->worker_usage.vacuum.nplanned = 0;
+ vacrel->worker_usage.cleanup.nlaunched = 0;
+ vacrel->worker_usage.cleanup.nplanned = 0;
+
/*
* Get cutoffs that determine which deleted tuples are considered DEAD,
* not just RECENTLY_DEAD, and which XIDs/MXIDs to freeze. Then determine
@@ -1123,6 +1135,19 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
orig_rel_pages == 0 ? 100.0 :
100.0 * vacrel->lpdead_item_pages / orig_rel_pages,
vacrel->lpdead_items);
+
+ if (vacrel->worker_usage.vacuum.nplanned > 0)
+ appendStringInfo(&buf,
+ _("parallel workers: index vacuum: %d planned, %d launched in total\n"),
+ vacrel->worker_usage.vacuum.nplanned,
+ vacrel->worker_usage.vacuum.nlaunched);
+
+ if (vacrel->worker_usage.cleanup.nplanned > 0)
+ appendStringInfo(&buf,
+ _("parallel workers: index cleanup: %d planned, %d launched\n"),
+ vacrel->worker_usage.cleanup.nplanned,
+ vacrel->worker_usage.cleanup.nlaunched);
+
for (int i = 0; i < vacrel->nindexes; i++)
{
IndexBulkDeleteResult *istat = vacrel->indstats[i];
@@ -2669,7 +2694,8 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
{
/* Outsource everything to parallel variant */
parallel_vacuum_bulkdel_all_indexes(vacrel->pvs, old_live_tuples,
- vacrel->num_index_scans);
+ vacrel->num_index_scans,
+ &(vacrel->worker_usage.vacuum));
/*
* Do a postcheck to consider applying wraparound failsafe now. Note
@@ -3103,7 +3129,8 @@ lazy_cleanup_all_indexes(LVRelState *vacrel)
/* Outsource everything to parallel variant */
parallel_vacuum_cleanup_all_indexes(vacrel->pvs, reltuples,
vacrel->num_index_scans,
- estimated_count);
+ estimated_count,
+ &(vacrel->worker_usage.cleanup));
}
/* Reset the progress counters */
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 279108ca89f..77834b96a21 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -225,7 +225,7 @@ struct ParallelVacuumState
static int parallel_vacuum_compute_workers(Relation *indrels, int nindexes, int nrequested,
bool *will_parallel_vacuum);
static void parallel_vacuum_process_all_indexes(ParallelVacuumState *pvs, int num_index_scans,
- bool vacuum);
+ bool vacuum, PVWorkerStats *wstats);
static void parallel_vacuum_process_safe_indexes(ParallelVacuumState *pvs);
static void parallel_vacuum_process_unsafe_indexes(ParallelVacuumState *pvs);
static void parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
@@ -499,7 +499,7 @@ parallel_vacuum_reset_dead_items(ParallelVacuumState *pvs)
*/
void
parallel_vacuum_bulkdel_all_indexes(ParallelVacuumState *pvs, long num_table_tuples,
- int num_index_scans)
+ int num_index_scans, PVWorkerStats *wstats)
{
Assert(!IsParallelWorker());
@@ -510,7 +510,7 @@ parallel_vacuum_bulkdel_all_indexes(ParallelVacuumState *pvs, long num_table_tup
pvs->shared->reltuples = num_table_tuples;
pvs->shared->estimated_count = true;
- parallel_vacuum_process_all_indexes(pvs, num_index_scans, true);
+ parallel_vacuum_process_all_indexes(pvs, num_index_scans, true, wstats);
}
/*
@@ -518,7 +518,8 @@ parallel_vacuum_bulkdel_all_indexes(ParallelVacuumState *pvs, long num_table_tup
*/
void
parallel_vacuum_cleanup_all_indexes(ParallelVacuumState *pvs, long num_table_tuples,
- int num_index_scans, bool estimated_count)
+ int num_index_scans, bool estimated_count,
+ PVWorkerStats *wstats)
{
Assert(!IsParallelWorker());
@@ -530,7 +531,7 @@ parallel_vacuum_cleanup_all_indexes(ParallelVacuumState *pvs, long num_table_tup
pvs->shared->reltuples = num_table_tuples;
pvs->shared->estimated_count = estimated_count;
- parallel_vacuum_process_all_indexes(pvs, num_index_scans, false);
+ parallel_vacuum_process_all_indexes(pvs, num_index_scans, false, wstats);
}
/*
@@ -607,10 +608,12 @@ parallel_vacuum_compute_workers(Relation *indrels, int nindexes, int nrequested,
/*
* Perform index vacuum or index cleanup with parallel workers. This function
* must be used by the parallel vacuum leader process.
+ *
+ * If wstats is not NULL, the parallel worker statistics are updated.
*/
static void
parallel_vacuum_process_all_indexes(ParallelVacuumState *pvs, int num_index_scans,
- bool vacuum)
+ bool vacuum, PVWorkerStats *wstats)
{
int nworkers;
PVIndVacStatus new_status;
@@ -647,6 +650,10 @@ parallel_vacuum_process_all_indexes(ParallelVacuumState *pvs, int num_index_scan
*/
nworkers = Min(nworkers, pvs->pcxt->nworkers);
+ /* Update the statistics, if we asked to */
+ if (wstats != NULL && nworkers > 0)
+ wstats->nplanned += nworkers;
+
/*
* Set index vacuum status and mark whether parallel vacuum worker can
* process it.
@@ -703,6 +710,10 @@ parallel_vacuum_process_all_indexes(ParallelVacuumState *pvs, int num_index_scan
/* Enable shared cost balance for leader backend */
VacuumSharedCostBalance = &(pvs->shared->cost_balance);
VacuumActiveNWorkers = &(pvs->shared->active_nworkers);
+
+ /* Update the statistics, if we asked to */
+ if (wstats != NULL)
+ wstats->nlaunched += pvs->pcxt->nworkers_launched;
}
if (vacuum)
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index e885a4b9c77..953a506181e 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -300,6 +300,28 @@ typedef struct VacDeadItemsInfo
int64 num_items; /* current # of entries */
} VacDeadItemsInfo;
+/*
+ * Statistics for parallel vacuum workers (planned vs. actual)
+ */
+typedef struct PVWorkerStats
+{
+ /* Number of parallel workers planned to launch */
+ int nplanned;
+
+ /* Number of parallel workers that were successfully launched */
+ int nlaunched;
+} PVWorkerStats;
+
+/*
+ * PVWorkerUsage stores information about total number of launched and
+ * planned workers during parallel vacuum (both for index vacuum and cleanup).
+ */
+typedef struct PVWorkerUsage
+{
+ PVWorkerStats vacuum;
+ PVWorkerStats cleanup;
+} PVWorkerUsage;
+
/* GUC parameters */
extern PGDLLIMPORT int default_statistics_target; /* PGDLLIMPORT for PostGIS */
extern PGDLLIMPORT int vacuum_freeze_min_age;
@@ -394,11 +416,13 @@ extern TidStore *parallel_vacuum_get_dead_items(ParallelVacuumState *pvs,
extern void parallel_vacuum_reset_dead_items(ParallelVacuumState *pvs);
extern void parallel_vacuum_bulkdel_all_indexes(ParallelVacuumState *pvs,
long num_table_tuples,
- int num_index_scans);
+ int num_index_scans,
+ PVWorkerStats *wstats);
extern void parallel_vacuum_cleanup_all_indexes(ParallelVacuumState *pvs,
long num_table_tuples,
int num_index_scans,
- bool estimated_count);
+ bool estimated_count,
+ PVWorkerStats *wstats);
extern void parallel_vacuum_main(dsm_segment *seg, shm_toc *toc);
/* in commands/analyze.c */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 174e2798443..a847d37b526 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2090,6 +2090,8 @@ PVIndStats
PVIndVacStatus
PVOID
PVShared
+PVWorkerUsage
+PVWorkerStats
PX_Alias
PX_Cipher
PX_Combo
--
2.53.0
