Teaching users how they can get the most out of HOT in Postgres 14

diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index cb27257bb6..9c3b9c5e1f 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -221,6 +221,10 @@ typedef struct VacuumParams
 	VacOptTernaryValue truncate;	/* Truncate empty pages at the end,
 									 * default value depends on reloptions */
 
+	double		cleanup_index_scale_factor;	/* Minimum fraction of relation pages
+											 * with dead items required for index
+											 * cleanup */
+
 	/*
 	 * The number of parallel vacuum workers.  0 by default which means choose
 	 * based on the number of indexes.  -1 indicates parallel vacuum is
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 95202d37af..9356b9c5b2 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -256,6 +256,8 @@ extern int	VacuumCostPageDirty;
 extern int	VacuumCostLimit;
 extern double VacuumCostDelay;
 
+extern double VacuumCleanupIndexScale;
+
 extern int64 VacuumPageHit;
 extern int64 VacuumPageMiss;
 extern int64 VacuumPageDirty;
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index aacdd0f575..24cfbdad15 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -41,6 +41,7 @@ extern int	autovacuum_freeze_max_age;
 extern int	autovacuum_multixact_freeze_max_age;
 extern double autovacuum_vac_cost_delay;
 extern int	autovacuum_vac_cost_limit;
+extern double autovacuum_cleanup_index_scale;
 
 /* autovacuum launcher PID, only valid when worker is shutting down */
 extern int	AutovacuumLauncherPid;
diff --git a/src/include/utils/guc_tables.h b/src/include/utils/guc_tables.h
index 6b40f1eeb8..30e69a9640 100644
--- a/src/include/utils/guc_tables.h
+++ b/src/include/utils/guc_tables.h
@@ -84,6 +84,7 @@ enum config_group
 	STATS_MONITORING,
 	STATS_COLLECTOR,
 	AUTOVACUUM,
+	VACUUM_INDEX,
 	CLIENT_CONN_STATEMENT,
 	CLIENT_CONN_LOCALE,
 	CLIENT_CONN_PRELOAD,
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 17519a970f..8871fb8758 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -103,12 +103,6 @@
 #define VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL		50	/* ms */
 #define VACUUM_TRUNCATE_LOCK_TIMEOUT			5000	/* ms */
 
-/*
- * Threshold that controls whether we bypass index vacuuming and heap
- * vacuuming as an optimization
- */
-#define BYPASS_THRESHOLD_PAGES	0.02	/* i.e. 2% of rel_pages */
-
 /*
  * When a table is small (i.e. smaller than this), save cycles by avoiding
  * repeated failsafe checks
@@ -407,7 +401,8 @@ static void lazy_scan_prune(LVRelState *vacrel, Buffer buf,
 							BlockNumber blkno, Page page,
 							GlobalVisState *vistest,
 							LVPagePruneState *prunestate);
-static void lazy_vacuum(LVRelState *vacrel, bool onecall);
+static void lazy_vacuum(LVRelState *vacrel, VacuumParams *params,
+						bool onecall);
 static bool lazy_vacuum_all_indexes(LVRelState *vacrel);
 static void lazy_vacuum_heap_rel(LVRelState *vacrel);
 static int	lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno,
@@ -1149,7 +1144,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 			}
 
 			/* Remove the collected garbage tuples from table and indexes */
-			lazy_vacuum(vacrel, false);
+			lazy_vacuum(vacrel, params, false);
 			have_vacuumed_indexes = true;
 
 			/*
@@ -1567,7 +1562,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 
 	/* If any tuples need to be deleted, perform final vacuum cycle */
 	if (dead_tuples->num_tuples > 0)
-		lazy_vacuum(vacrel, !have_vacuumed_indexes);
+		lazy_vacuum(vacrel, params, !have_vacuumed_indexes);
 
 	/*
 	 * Vacuum the remainder of the Free Space Map.  We must do this whether or
@@ -2052,7 +2047,7 @@ retry:
  * wraparound.
  */
 static void
-lazy_vacuum(LVRelState *vacrel, bool onecall)
+lazy_vacuum(LVRelState *vacrel, VacuumParams *params, bool onecall)
 {
 	bool		do_bypass_optimization;
 
@@ -2119,7 +2114,7 @@ lazy_vacuum(LVRelState *vacrel, bool onecall)
 		 * The discrepancy should be negligible.  If this optimization is ever
 		 * expanded to cover more cases then this may need to be reconsidered.
 		 */
-		threshold = (double) vacrel->rel_pages * BYPASS_THRESHOLD_PAGES;
+		threshold = (double) vacrel->rel_pages * params->cleanup_index_scale_factor;
 		do_bypass_optimization =
 			(vacrel->lpdead_item_pages < threshold &&
 			 vacrel->lpdead_items < MAXDEADTUPLES(32L * 1024L * 1024L));
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index d549d0d86f..55c458da07 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -250,6 +250,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	/* user-invoked vacuum never uses this parameter */
 	params.log_min_duration = -1;
 
+	/* set scale factor for index cleanup based on GUC */
+	params.cleanup_index_scale_factor = VacuumCleanupIndexScale;
+
 	/* Now go through the common routine */
 	vacuum(vacstmt->rels, &params, NULL, isTopLevel);
 }
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index d516df0ac5..df9d52a2d2 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -124,6 +124,7 @@ int			autovacuum_anl_thresh;
 double		autovacuum_anl_scale;
 int			autovacuum_freeze_max_age;
 int			autovacuum_multixact_freeze_max_age;
+double		autovacuum_cleanup_index_scale = 0.02;
 
 double		autovacuum_vac_cost_delay;
 int			autovacuum_vac_cost_limit;
@@ -2922,6 +2923,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		int			vac_cost_limit;
 		double		vac_cost_delay;
 		int			log_min_duration;
+		int			cleanup_index_scale_factor;
 
 		/*
 		 * Calculate the vacuum cost parameters and the freeze ages.  If there
@@ -2968,6 +2970,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 			? avopts->multixact_freeze_table_age
 			: default_multixact_freeze_table_age;
 
+		cleanup_index_scale_factor = autovacuum_cleanup_index_scale >= 0 ?
+			autovacuum_cleanup_index_scale : VacuumCostDelay;
+
 		tab = palloc(sizeof(autovac_table));
 		tab->at_relid = relid;
 		tab->at_sharedrel = classForm->relisshared;
@@ -2986,6 +2991,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age;
 		tab->at_params.is_wraparound = wraparound;
 		tab->at_params.log_min_duration = log_min_duration;
+		tab->at_params.cleanup_index_scale_factor = cleanup_index_scale_factor;
 		tab->at_vacuum_cost_limit = vac_cost_limit;
 		tab->at_vacuum_cost_delay = vac_cost_delay;
 		tab->at_relname = NULL;
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 381d9e548d..953fb2ae08 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -143,6 +143,11 @@ int			VacuumCostPageMiss = 2;
 int			VacuumCostPageDirty = 20;
 int			VacuumCostLimit = 200;
 double		VacuumCostDelay = 0;
+/*
+ * Fraction number that controls whether we bypass index vacuuming and heap
+ * vacuuming as an optimization.
+ */
+double		VacuumCleanupIndexScale = 0.02;		 /* i.e. 2% of rel_pages */
 
 int64		VacuumPageHit = 0;
 int64		VacuumPageMiss = 0;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index eb7f7181e4..3999bbeddb 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -759,6 +759,8 @@ const char *const config_group_names[] =
 	gettext_noop("Statistics / Query and Index Statistics Collector"),
 	/* AUTOVACUUM */
 	gettext_noop("Autovacuum"),
+	/* VACUUM_INDEX */
+	gettext_noop("Vacuum / Index"),
 	/* CLIENT_CONN_STATEMENT */
 	gettext_noop("Client Connection Defaults / Statement Behavior"),
 	/* CLIENT_CONN_LOCALE */
@@ -3739,6 +3741,26 @@ static struct config_real ConfigureNamesReal[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"vacuum_cleanup_index_scale_factor", PGC_SIGHUP, VACUUM_INDEX,
+			gettext_noop("Fraction of relation pages, with at least one dead item, required to clean up indexes."),
+			NULL
+		},
+		&VacuumCleanupIndexScale,
+		0.02, 0.0, 0.05,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"autovacuum_cleanup_index_scale_factor", PGC_SIGHUP, AUTOVACUUM,
+			gettext_noop("Fraction of relation pages, with at least one dead item, required to clean up indexes, for autovacuum."),
+			NULL
+		},
+		&autovacuum_cleanup_index_scale,
+		-1, -1, 0.05,
+		NULL, NULL, NULL
+	},
+
 	{
 		{"autovacuum_vacuum_scale_factor", PGC_SIGHUP, AUTOVACUUM,
 			gettext_noop("Number of tuple updates or deletes prior to vacuum as a fraction of reltuples."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index efde01ee56..d9b8d1ad42 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -173,6 +173,13 @@
 #vacuum_cost_page_dirty = 20		# 0-10000 credits
 #vacuum_cost_limit = 200		# 1-10000 credits
 
+# - Vacuum / Index
+
+#vacuum_cleanup_index_scale_factor = 0.02	# Minimum fraction of relation
+					# pages with at least one dead item
+					# needed to trigger index cleanup during
+					# manual VACUUM.  Default is 2%.
+
 # - Background Writer -
 
 #bgwriter_delay = 200ms			# 10-10000ms between rounds
@@ -642,6 +649,11 @@
 #autovacuum_vacuum_cost_limit = -1	# default vacuum cost limit for
 					# autovacuum, -1 means use
 					# vacuum_cost_limit
+#autovacuum_cleanup_index_scale_factor = -1	# Minimum fraction of relation
+					# pages with at least one dead item
+					# needed to trigger index cleanup,
+					# -1 means use
+					# vacuum_cleanup_index_scale_factor
 
 
 #------------------------------------------------------------------------------
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 45bd1f1b7e..ac77727564 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2241,6 +2241,38 @@ include_dir 'conf.d'
      </note>
     </sect2>
 
+    <sect2 id="runtime-config-vacuum-index">
+     <title>Vacuum parameters for Indexes</title>
+     <para>
+      During the execution of <xref linkend="sql-vacuum"/>
+      and <xref linkend="sql-analyze"/>
+      commands, this controls the execution of vacuum operations
+      performed on indexes of the relation worked on.
+     </para>
+
+     <variablelist>
+      <varlistentry id="guc-vacuum-cleanup-index-scale-factor" xreflabel="vacuum_cleanup_index_scale_factor">
+       <term><varname>vacuum_cleanup_index_scale_factor</varname> (<type>floating point</type>)
+       <indexterm>
+        <primary><varname>vacuum_cleanup_index_scale_factor</varname></primary>
+        <secondary>configuration parameter</secondary>
+       </indexterm>
+       </term>
+       <listitem>
+        <para>
+         Specifies the minimum percentage of relation pages required to have
+         at least one dead item to trigger index cleanup in
+         <command>VACUUM</command> operations.  The default is
+         <literal>0.02</literal>, meaning that at least 2% of the relation
+         pages need to have at least one dead item to trigger the cleanup
+         This parameter can only be set in the <filename>postgresql.conf</filename>
+         file or on the server command line.
+        </para>
+       </listitem>
+      </varlistentry>
+     </variablelist>
+    </sect2>
+      
     <sect2 id="runtime-config-resource-background-writer">
      <title>Background Writer</title>
 
@@ -8001,6 +8033,26 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-autovacuum--cleanup-index-scale-factor" xreflabel="autovacuum_cleanup_index_scale_factor">
+      <term><varname>autovacuum_cleanup_index_scale_factor</varname> (<type>floating point</type>)
+      <indexterm>
+       <primary><varname>autovacuum_cleanup_index_scale_factor</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the minimum percentage of relation pages required to have
+        at least one dead item to trigger index cleanup in automatic
+        <command>VACUUM</command> operations.  If <literal>-1</literal> is
+        specified (which is the default), the regular
+        <xref linkend="guc-vacuum-cleanup-index-scale-factor"/> value will be used.
+        This parameter can only be set in the <filename>postgresql.conf</filename>
+        file or on the server command line.
+       </para>
+      </listitem>
+     </varlistentry>
+
     </variablelist>
    </sect1>
 

From 54823d82fcbb2c8bf3eb2a122c461b41b66eb5b6 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <pg@bowt.ie>
Date: Wed, 26 May 2021 17:17:05 -0700
Subject: [PATCH] Generalize VACUUM's INDEX_CLEANUP option.

Generalize the reloption INDEX_CLEANUP to enable users to disable the
index vacuum bypassing optimization added by commit 5100010e, as well as
any similar optimizations that may be added in the future.

The reloption is now a trinary boolean style enum whose default is
'auto', which preserves the current VACUUM behavior.  Explicitly setting
INDEX_CLEANUP to 'on' now avoids any optimizations that skip index
vacuuming, though it doesn't have any effect on the failsafe mechanism.

The INDEX_CLEANUP reloption was originally added by commit a96c41fe as
an emergency option to allow DBAs to opt out of index vacuuming in
extreme cases -- cases where the likely alternative is an anti-wraparoud
failure.  The reloption can still be used for that, but it seems like a
much less useful option now that VACUUM has the failsafe mechanism added
by commit 1e55e7d1.
---
 src/include/commands/vacuum.h          | 14 +----------
 src/include/utils/rel.h                | 15 +++++++++++-
 src/backend/access/common/reloptions.c | 33 +++++++++++++++++++-------
 src/backend/access/heap/vacuumlazy.c   | 33 ++++++++++++++------------
 src/backend/commands/vacuum.c          | 11 +++------
 5 files changed, 60 insertions(+), 46 deletions(-)

diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index cb27257bb6..d45891ca31 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -21,6 +21,7 @@
 #include "parser/parse_node.h"
 #include "storage/buf.h"
 #include "storage/lock.h"
+#include "utils/rel.h"
 #include "utils/relcache.h"
 
 /*
@@ -184,19 +185,6 @@ typedef struct VacAttrStats
 #define VACOPT_PROCESS_TOAST 0x40	/* process the TOAST table, if any */
 #define VACOPT_DISABLE_PAGE_SKIPPING 0x80	/* don't skip any pages */
 
-/*
- * A ternary value used by vacuum parameters.
- *
- * DEFAULT value is used to determine the value based on other
- * configurations, e.g. reloptions.
- */
-typedef enum VacOptTernaryValue
-{
-	VACOPT_TERNARY_DEFAULT = 0,
-	VACOPT_TERNARY_DISABLED,
-	VACOPT_TERNARY_ENABLED,
-} VacOptTernaryValue;
-
 /*
  * Parameters customizing behavior of VACUUM and ANALYZE.
  *
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 774ac5b2b1..740e22942e 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -307,6 +307,19 @@ typedef struct AutoVacOpts
 	float8		analyze_scale_factor;
 } AutoVacOpts;
 
+/*
+ * A ternary value used by vacuum parameters.
+ *
+ * DEFAULT value is used to determine the value based on other
+ * configurations, e.g. reloptions.
+ */
+typedef enum VacOptTernaryValue
+{
+	VACOPT_TERNARY_DEFAULT = 0,
+	VACOPT_TERNARY_DISABLED,
+	VACOPT_TERNARY_ENABLED,
+} VacOptTernaryValue;
+
 typedef struct StdRdOptions
 {
 	int32		vl_len_;		/* varlena header (do not touch directly!) */
@@ -316,7 +329,7 @@ typedef struct StdRdOptions
 	AutoVacOpts autovacuum;		/* autovacuum-related options */
 	bool		user_catalog_table; /* use as an additional catalog relation */
 	int			parallel_workers;	/* max number of parallel workers */
-	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
+	VacOptTernaryValue vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
 } StdRdOptions;
 
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 5554275e64..60df707f36 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -140,15 +140,6 @@ static relopt_bool boolRelOpts[] =
 		},
 		false
 	},
-	{
-		{
-			"vacuum_index_cleanup",
-			"Enables index vacuuming and index cleanup",
-			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
-			ShareUpdateExclusiveLock
-		},
-		true
-	},
 	{
 		{
 			"vacuum_truncate",
@@ -474,6 +465,19 @@ static relopt_real realRelOpts[] =
 	{{NULL}}
 };
 
+/* values from HeapOptIndexCleanupMode */
+relopt_enum_elt_def HeapOptIndexCleanupOptValues[] =
+{
+	{"auto", VACOPT_TERNARY_DEFAULT},
+	{"on", VACOPT_TERNARY_ENABLED},
+	{"off", VACOPT_TERNARY_DISABLED},
+	{"true", VACOPT_TERNARY_ENABLED},
+	{"false", VACOPT_TERNARY_DISABLED},
+	{"1", VACOPT_TERNARY_ENABLED},
+	{"0", VACOPT_TERNARY_DISABLED},
+	{(const char *) NULL}		/* list terminator */
+};
+
 /* values from GistOptBufferingMode */
 relopt_enum_elt_def gistBufferingOptValues[] =
 {
@@ -494,6 +498,17 @@ relopt_enum_elt_def viewCheckOptValues[] =
 
 static relopt_enum enumRelOpts[] =
 {
+	{
+		{
+			"vacuum_index_cleanup",
+			"Enables index vacuuming and index cleanup",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		HeapOptIndexCleanupOptValues,
+		VACOPT_TERNARY_DEFAULT,
+		gettext_noop("Valid values are \"on\", \"off\", and \"auto\".")
+	},
 	{
 		{
 			"buffering",
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index ad3feb88b3..db22ca1ee1 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -313,6 +313,8 @@ typedef struct LVRelState
 	bool		do_index_cleanup;
 	/* Wraparound failsafe in effect? (implies !do_index_vacuuming) */
 	bool		do_failsafe;
+	/* Consider bypass optimization? */
+	bool		do_bypass_optimization;
 
 	/* Buffer access strategy and parallel state */
 	BufferAccessStrategy bstrategy;
@@ -406,7 +408,7 @@ static void lazy_scan_prune(LVRelState *vacrel, Buffer buf,
 							BlockNumber blkno, Page page,
 							GlobalVisState *vistest,
 							LVPagePruneState *prunestate);
-static void lazy_vacuum(LVRelState *vacrel, bool onecall);
+static void lazy_vacuum(LVRelState *vacrel);
 static bool lazy_vacuum_all_indexes(LVRelState *vacrel);
 static void lazy_vacuum_heap_rel(LVRelState *vacrel);
 static int	lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno,
@@ -507,7 +509,6 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 	MultiXactId MultiXactCutoff;
 
 	Assert(params != NULL);
-	Assert(params->index_cleanup != VACOPT_TERNARY_DEFAULT);
 	Assert(params->truncate != VACOPT_TERNARY_DEFAULT);
 
 	/* measure elapsed time iff autovacuum logging requires it */
@@ -560,11 +561,15 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 	vacrel->do_index_vacuuming = true;
 	vacrel->do_index_cleanup = true;
 	vacrel->do_failsafe = false;
+	vacrel->do_bypass_optimization = true;
 	if (params->index_cleanup == VACOPT_TERNARY_DISABLED)
 	{
 		vacrel->do_index_vacuuming = false;
 		vacrel->do_index_cleanup = false;
 	}
+	else if (params->index_cleanup == VACOPT_TERNARY_ENABLED)
+		vacrel->do_bypass_optimization = false;
+
 	vacrel->bstrategy = bstrategy;
 	vacrel->old_rel_pages = rel->rd_rel->relpages;
 	vacrel->old_live_tuples = rel->rd_rel->reltuples;
@@ -893,8 +898,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 				next_fsm_block_to_vacuum;
 	PGRUsage	ru0;
 	Buffer		vmbuffer = InvalidBuffer;
-	bool		skipping_blocks,
-				have_vacuumed_indexes = false;
+	bool		skipping_blocks;
 	StringInfoData buf;
 	const int	initprog_index[] = {
 		PROGRESS_VACUUM_PHASE,
@@ -1167,8 +1171,8 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 			}
 
 			/* Remove the collected garbage tuples from table and indexes */
-			lazy_vacuum(vacrel, false);
-			have_vacuumed_indexes = true;
+			vacrel->do_bypass_optimization = false;
+			lazy_vacuum(vacrel);
 
 			/*
 			 * Vacuum the Free Space Map to make newly-freed space visible on
@@ -1580,7 +1584,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 
 	/* If any tuples need to be deleted, perform final vacuum cycle */
 	if (dead_tuples->num_tuples > 0)
-		lazy_vacuum(vacrel, !have_vacuumed_indexes);
+		lazy_vacuum(vacrel);
 
 	/*
 	 * Vacuum the remainder of the Free Space Map.  We must do this whether or
@@ -2065,9 +2069,9 @@ retry:
  * wraparound.
  */
 static void
-lazy_vacuum(LVRelState *vacrel, bool onecall)
+lazy_vacuum(LVRelState *vacrel)
 {
-	bool		do_bypass_optimization;
+	bool		bypass;
 
 	/* Should not end up here with no indexes */
 	Assert(vacrel->nindexes > 0);
@@ -2100,8 +2104,8 @@ lazy_vacuum(LVRelState *vacrel, bool onecall)
 	 * It's far easier to ensure that 99%+ of all UPDATEs against a table use
 	 * HOT through careful tuning.
 	 */
-	do_bypass_optimization = false;
-	if (onecall && vacrel->rel_pages > 0)
+	bypass = false;
+	if (vacrel->do_bypass_optimization && vacrel->rel_pages > 0)
 	{
 		BlockNumber threshold;
 
@@ -2133,12 +2137,11 @@ lazy_vacuum(LVRelState *vacrel, bool onecall)
 		 * expanded to cover more cases then this may need to be reconsidered.
 		 */
 		threshold = (double) vacrel->rel_pages * BYPASS_THRESHOLD_PAGES;
-		do_bypass_optimization =
-			(vacrel->lpdead_item_pages < threshold &&
-			 vacrel->lpdead_items < MAXDEADTUPLES(32L * 1024L * 1024L));
+		bypass = (vacrel->lpdead_item_pages < threshold &&
+				  vacrel->lpdead_items < MAXDEADTUPLES(32L * 1024L * 1024L));
 	}
 
-	if (do_bypass_optimization)
+	if (bypass)
 	{
 		/*
 		 * There are almost zero TIDs.  Behave as if there were precisely
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7421d7cfbd..3b17d028af 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1939,14 +1939,9 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	LockRelationIdForSession(&lockrelid, lmode);
 
 	/* Set index cleanup option based on reloptions if not yet */
-	if (params->index_cleanup == VACOPT_TERNARY_DEFAULT)
-	{
-		if (rel->rd_options == NULL ||
-			((StdRdOptions *) rel->rd_options)->vacuum_index_cleanup)
-			params->index_cleanup = VACOPT_TERNARY_ENABLED;
-		else
-			params->index_cleanup = VACOPT_TERNARY_DISABLED;
-	}
+	if (params->index_cleanup == VACOPT_TERNARY_DEFAULT && rel->rd_options != NULL)
+		params->index_cleanup =
+				((StdRdOptions *) rel->rd_options)->vacuum_index_cleanup;
 
 	/* Set truncate option based on reloptions if not yet */
 	if (params->truncate == VACOPT_TERNARY_DEFAULT)
-- 
2.27.0

From b7813aee4f9770fe23cf64eaffc8c119a517f3f3 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <pg@bowt.ie>
Date: Wed, 26 May 2021 17:17:05 -0700
Subject: [PATCH v2] Generalize VACUUM's INDEX_CLEANUP option.

Generalize the reloption INDEX_CLEANUP to enable users to disable the
index vacuum bypassing optimization added by commit 5100010e, as well as
any similar optimizations that may be added in the future.

The reloption is now a trinary boolean style enum whose default is
'auto', which preserves the current VACUUM behavior.  Explicitly setting
INDEX_CLEANUP to 'on' now avoids any optimizations that skip index
vacuuming, though it doesn't have any effect on the failsafe mechanism.

The INDEX_CLEANUP reloption was originally added by commit a96c41fe as
an emergency option to allow DBAs to opt out of index vacuuming in
extreme cases -- cases where the likely alternative is an anti-wraparoud
failure.  The reloption can still be used for that, but it seems like a
much less useful option now that VACUUM has the failsafe mechanism added
by commit 1e55e7d1.

Author: Peter Geoghegan <pg@bowt.ie>
Reviewed-By: Masahiko Sawada <sawada.mshk@gmail.com>
Discussion: https://postgr.es/m/CAH2-Wz=w7r1dpFEr=ija2XFn798wd41+6QUSbkcc8HmWKhEhdA@mail.gmail.com
---
 src/include/commands/vacuum.h          |  4 +-
 src/include/utils/rel.h                | 15 ++++++-
 src/backend/access/common/reloptions.c | 35 +++++++++++----
 src/backend/access/heap/vacuumlazy.c   | 48 ++++++++++++++------
 src/backend/commands/vacuum.c          | 47 +++++++++++++++++---
 src/bin/scripts/vacuumdb.c             | 61 +++++++++++++++++++++-----
 doc/src/sgml/ref/create_table.sgml     | 21 +++++----
 doc/src/sgml/ref/vacuum.sgml           | 43 +++++++++++-------
 doc/src/sgml/ref/vacuumdb.sgml         | 15 +++++++
 src/test/regress/expected/vacuum.out   |  8 ++--
 src/test/regress/sql/vacuum.sql        |  8 ++--
 11 files changed, 236 insertions(+), 69 deletions(-)

diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index cb27257bb6..a357ce148d 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -188,11 +188,13 @@ typedef struct VacAttrStats
  * A ternary value used by vacuum parameters.
  *
  * DEFAULT value is used to determine the value based on other
- * configurations, e.g. reloptions.
+ * configurations, e.g. reloptions.  AUTO value is used by
+ * index_cleanup option only.
  */
 typedef enum VacOptTernaryValue
 {
 	VACOPT_TERNARY_DEFAULT = 0,
+	VACOPT_TERNARY_AUTO,
 	VACOPT_TERNARY_DISABLED,
 	VACOPT_TERNARY_ENABLED,
 } VacOptTernaryValue;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 774ac5b2b1..6924ed730d 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -307,6 +307,19 @@ typedef struct AutoVacOpts
 	float8		analyze_scale_factor;
 } AutoVacOpts;
 
+/*
+ * A ternary value used by vacuum parameters.
+ *
+ * DEFAULT value is used to determine the value based on other
+ * configurations, e.g. reloptions.
+ */
+typedef enum StdRdOptIndexCleanup
+{
+	STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO = 0,
+	STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF,
+	STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON
+} StdRdOptIndexCleanup;
+
 typedef struct StdRdOptions
 {
 	int32		vl_len_;		/* varlena header (do not touch directly!) */
@@ -316,7 +329,7 @@ typedef struct StdRdOptions
 	AutoVacOpts autovacuum;		/* autovacuum-related options */
 	bool		user_catalog_table; /* use as an additional catalog relation */
 	int			parallel_workers;	/* max number of parallel workers */
-	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
+	StdRdOptIndexCleanup vacuum_index_cleanup;	/* controls index vacuuming */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
 } StdRdOptions;
 
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 5554275e64..7ef934cbc4 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -140,15 +140,6 @@ static relopt_bool boolRelOpts[] =
 		},
 		false
 	},
-	{
-		{
-			"vacuum_index_cleanup",
-			"Enables index vacuuming and index cleanup",
-			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
-			ShareUpdateExclusiveLock
-		},
-		true
-	},
 	{
 		{
 			"vacuum_truncate",
@@ -474,6 +465,21 @@ static relopt_real realRelOpts[] =
 	{{NULL}}
 };
 
+/* values from StdRdOptIndexCleanup */
+relopt_enum_elt_def StdRdOptIndexCleanupValues[] =
+{
+	{"auto", STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO},
+	{"on", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
+	{"off", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
+	{"true", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
+	{"false", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
+	{"yes", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
+	{"no", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
+	{"1", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
+	{"0", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
+	{(const char *) NULL}		/* list terminator */
+};
+
 /* values from GistOptBufferingMode */
 relopt_enum_elt_def gistBufferingOptValues[] =
 {
@@ -494,6 +500,17 @@ relopt_enum_elt_def viewCheckOptValues[] =
 
 static relopt_enum enumRelOpts[] =
 {
+	{
+		{
+			"vacuum_index_cleanup",
+			"Forces or disables index vacuuming and index cleanup",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		StdRdOptIndexCleanupValues,
+		STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO,
+		gettext_noop("Valid values are \"on\", \"off\", and \"auto\".")
+	},
 	{
 		{
 			"buffering",
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 88db2e2cfc..8c7f72c775 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -313,6 +313,8 @@ typedef struct LVRelState
 	bool		do_index_cleanup;
 	/* Wraparound failsafe in effect? (implies !do_index_vacuuming) */
 	bool		do_failsafe;
+	/* Consider bypass optimization? */
+	bool		do_bypass_optimization;
 
 	/* Buffer access strategy and parallel state */
 	BufferAccessStrategy bstrategy;
@@ -405,7 +407,7 @@ static void lazy_scan_prune(LVRelState *vacrel, Buffer buf,
 							BlockNumber blkno, Page page,
 							GlobalVisState *vistest,
 							LVPagePruneState *prunestate);
-static void lazy_vacuum(LVRelState *vacrel, bool onecall);
+static void lazy_vacuum(LVRelState *vacrel);
 static bool lazy_vacuum_all_indexes(LVRelState *vacrel);
 static void lazy_vacuum_heap_rel(LVRelState *vacrel);
 static int	lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno,
@@ -509,6 +511,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 	Assert(params != NULL);
 	Assert(params->index_cleanup != VACOPT_TERNARY_DEFAULT);
 	Assert(params->truncate != VACOPT_TERNARY_DEFAULT);
+	Assert(params->truncate != VACOPT_TERNARY_AUTO);
 
 	/* measure elapsed time iff autovacuum logging requires it */
 	if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
@@ -557,14 +560,33 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 	vacrel->rel = rel;
 	vac_open_indexes(vacrel->rel, RowExclusiveLock, &vacrel->nindexes,
 					 &vacrel->indrels);
+
+	/*
+	 * index_cleanup either disables index vacuuming and cleanup or forces it
+	 * to go ahead when we would otherwise apply the bypass optimization.  The
+	 * default is 'auto', which leaves the final decision up to lazy_vacuum().
+	 */
 	vacrel->do_index_vacuuming = true;
 	vacrel->do_index_cleanup = true;
 	vacrel->do_failsafe = false;
+	vacrel->do_bypass_optimization = true;
 	if (params->index_cleanup == VACOPT_TERNARY_DISABLED)
 	{
+		/* Force disable index vacuuming up-front */
 		vacrel->do_index_vacuuming = false;
 		vacrel->do_index_cleanup = false;
 	}
+	else if (params->index_cleanup == VACOPT_TERNARY_ENABLED)
+	{
+		/* Force index vacuuming (never bypass) */
+		vacrel->do_bypass_optimization = false;
+	}
+	else
+	{
+		/* Default/auto, make all decisions dynamically */
+		Assert(params->index_cleanup == VACOPT_TERNARY_AUTO);
+	}
+
 	vacrel->bstrategy = bstrategy;
 	vacrel->old_rel_pages = rel->rd_rel->relpages;
 	vacrel->old_live_tuples = rel->rd_rel->reltuples;
@@ -893,8 +915,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 				next_fsm_block_to_vacuum;
 	PGRUsage	ru0;
 	Buffer		vmbuffer = InvalidBuffer;
-	bool		skipping_blocks,
-				have_vacuumed_indexes = false;
+	bool		skipping_blocks;
 	StringInfoData buf;
 	const int	initprog_index[] = {
 		PROGRESS_VACUUM_PHASE,
@@ -1166,8 +1187,8 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 			}
 
 			/* Remove the collected garbage tuples from table and indexes */
-			lazy_vacuum(vacrel, false);
-			have_vacuumed_indexes = true;
+			vacrel->do_bypass_optimization = false;
+			lazy_vacuum(vacrel);
 
 			/*
 			 * Vacuum the Free Space Map to make newly-freed space visible on
@@ -1579,7 +1600,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 
 	/* If any tuples need to be deleted, perform final vacuum cycle */
 	if (dead_tuples->num_tuples > 0)
-		lazy_vacuum(vacrel, !have_vacuumed_indexes);
+		lazy_vacuum(vacrel);
 
 	/*
 	 * Vacuum the remainder of the Free Space Map.  We must do this whether or
@@ -2064,9 +2085,9 @@ retry:
  * wraparound.
  */
 static void
-lazy_vacuum(LVRelState *vacrel, bool onecall)
+lazy_vacuum(LVRelState *vacrel)
 {
-	bool		do_bypass_optimization;
+	bool		bypass;
 
 	/* Should not end up here with no indexes */
 	Assert(vacrel->nindexes > 0);
@@ -2099,8 +2120,8 @@ lazy_vacuum(LVRelState *vacrel, bool onecall)
 	 * It's far easier to ensure that 99%+ of all UPDATEs against a table use
 	 * HOT through careful tuning.
 	 */
-	do_bypass_optimization = false;
-	if (onecall && vacrel->rel_pages > 0)
+	bypass = false;
+	if (vacrel->do_bypass_optimization && vacrel->rel_pages > 0)
 	{
 		BlockNumber threshold;
 
@@ -2132,12 +2153,11 @@ lazy_vacuum(LVRelState *vacrel, bool onecall)
 		 * expanded to cover more cases then this may need to be reconsidered.
 		 */
 		threshold = (double) vacrel->rel_pages * BYPASS_THRESHOLD_PAGES;
-		do_bypass_optimization =
-			(vacrel->lpdead_item_pages < threshold &&
-			 vacrel->lpdead_items < MAXDEADTUPLES(32L * 1024L * 1024L));
+		bypass = (vacrel->lpdead_item_pages < threshold &&
+				  vacrel->lpdead_items < MAXDEADTUPLES(32L * 1024L * 1024L));
 	}
 
-	if (do_bypass_optimization)
+	if (bypass)
 	{
 		/*
 		 * There are almost zero TIDs.  Behave as if there were precisely
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7421d7cfbd..9190e9cb70 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -142,7 +142,21 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		else if (strcmp(opt->defname, "disable_page_skipping") == 0)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
-			params.index_cleanup = get_vacopt_ternary_value(opt);
+		{
+			/* Interpret no string as the default, which is 'auto' */
+			if (!opt->arg)
+				params.index_cleanup = VACOPT_TERNARY_AUTO;
+			else
+			{
+				char	   *sval = defGetString(opt);
+
+				/* Try matching on 'auto' string, or fall back on boolean */
+				if (pg_strcasecmp(sval, "auto") == 0)
+					params.index_cleanup = VACOPT_TERNARY_AUTO;
+				else
+					params.index_cleanup = get_vacopt_ternary_value(opt);
+			}
+		}
 		else if (strcmp(opt->defname, "process_toast") == 0)
 			process_toast = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
@@ -1938,17 +1952,37 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	lockrelid = rel->rd_lockInfo.lockRelId;
 	LockRelationIdForSession(&lockrelid, lmode);
 
-	/* Set index cleanup option based on reloptions if not yet */
+	/*
+	 * Set index cleanup option based on vacuum_index_cleanup reloptions if
+	 * not yet.  This is a ternary style boolean option.
+	 */
 	if (params->index_cleanup == VACOPT_TERNARY_DEFAULT)
 	{
-		if (rel->rd_options == NULL ||
-			((StdRdOptions *) rel->rd_options)->vacuum_index_cleanup)
+		StdRdOptIndexCleanup vacuum_index_cleanup;
+
+		/* The reloption default is 'auto', just like the VACUUM option */
+		if (rel->rd_options == NULL)
+			vacuum_index_cleanup = STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO;
+		else
+			vacuum_index_cleanup =
+					((StdRdOptions *) rel->rd_options)->vacuum_index_cleanup;
+
+		if (vacuum_index_cleanup == STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO)
+			params->index_cleanup = VACOPT_TERNARY_AUTO;
+		else if (vacuum_index_cleanup == STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON)
 			params->index_cleanup = VACOPT_TERNARY_ENABLED;
 		else
+		{
+			Assert(vacuum_index_cleanup ==
+				   STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF);
 			params->index_cleanup = VACOPT_TERNARY_DISABLED;
+		}
 	}
 
-	/* Set truncate option based on reloptions if not yet */
+	/*
+	 * Set index cleanup option based on truncate reloptions if not yet.  This
+	 * is a simple boolean reloption.
+	 */
 	if (params->truncate == VACOPT_TERNARY_DEFAULT)
 	{
 		if (rel->rd_options == NULL ||
@@ -2219,6 +2253,9 @@ compute_parallel_delay(void)
  *
  * This function returns VACOPT_TERNARY_ENABLED and VACOPT_TERNARY_DISABLED
  * instead of true and false.
+ *
+ * There is also a VACOPT_TERNARY_AUTO value that is used only by the
+ * INDEX_CLEANUP option.  We don't handle that here.
  */
 static VacOptTernaryValue
 get_vacopt_ternary_value(DefElem *def)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 069a861aab..7df75602eb 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -39,7 +39,8 @@ typedef struct vacuumingOptions
 	int			min_mxid_age;
 	int			parallel_workers;	/* >= 0 indicates user specified the
 									 * parallel degree, otherwise -1 */
-	bool		do_index_cleanup;
+	bool		no_index_cleanup;
+	bool		force_index_cleanup;
 	bool		do_truncate;
 	bool		process_toast;
 } vacuumingOptions;
@@ -99,8 +100,9 @@ main(int argc, char *argv[])
 		{"min-xid-age", required_argument, NULL, 6},
 		{"min-mxid-age", required_argument, NULL, 7},
 		{"no-index-cleanup", no_argument, NULL, 8},
-		{"no-truncate", no_argument, NULL, 9},
-		{"no-process-toast", no_argument, NULL, 10},
+		{"force-index-cleanup", no_argument, NULL, 9},
+		{"no-truncate", no_argument, NULL, 10},
+		{"no-process-toast", no_argument, NULL, 11},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -126,7 +128,8 @@ main(int argc, char *argv[])
 	/* initialize options */
 	memset(&vacopts, 0, sizeof(vacopts));
 	vacopts.parallel_workers = -1;
-	vacopts.do_index_cleanup = true;
+	vacopts.no_index_cleanup = false;
+	vacopts.force_index_cleanup = false;
 	vacopts.do_truncate = true;
 	vacopts.process_toast = true;
 
@@ -233,12 +236,15 @@ main(int argc, char *argv[])
 				}
 				break;
 			case 8:
-				vacopts.do_index_cleanup = false;
+				vacopts.no_index_cleanup = true;
 				break;
 			case 9:
-				vacopts.do_truncate = false;
+				vacopts.force_index_cleanup = true;
 				break;
 			case 10:
+				vacopts.do_truncate = false;
+				break;
+			case 11:
 				vacopts.process_toast = false;
 				break;
 			default:
@@ -285,12 +291,18 @@ main(int argc, char *argv[])
 						 "disable-page-skipping");
 			exit(1);
 		}
-		if (!vacopts.do_index_cleanup)
+		if (vacopts.no_index_cleanup)
 		{
 			pg_log_error("cannot use the \"%s\" option when performing only analyze",
 						 "no-index-cleanup");
 			exit(1);
 		}
+		if (vacopts.force_index_cleanup)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "force-index-cleanup");
+			exit(1);
+		}
 		if (!vacopts.do_truncate)
 		{
 			pg_log_error("cannot use the \"%s\" option when performing only analyze",
@@ -323,6 +335,14 @@ main(int argc, char *argv[])
 		}
 	}
 
+	/* Prohibit --no-index-cleanup and --force-index-cleanup together */
+	if (vacopts.no_index_cleanup && vacopts.force_index_cleanup)
+	{
+		pg_log_error("cannot use the \"%s\" option with the \"%s\" option",
+					 "no-index-cleanup", "force-index-cleanup");
+		exit(1);
+	}
+
 	/* fill cparams except for dbname, which is set below */
 	cparams.pghost = host;
 	cparams.pgport = port;
@@ -453,7 +473,7 @@ vacuum_one_database(ConnParams *cparams,
 		exit(1);
 	}
 
-	if (!vacopts->do_index_cleanup && PQserverVersion(conn) < 120000)
+	if (vacopts->no_index_cleanup && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
 		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
@@ -461,6 +481,14 @@ vacuum_one_database(ConnParams *cparams,
 		exit(1);
 	}
 
+	if (vacopts->force_index_cleanup && PQserverVersion(conn) < 120000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "force-index-cleanup", "12");
+		exit(1);
+	}
+
 	if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -878,10 +906,23 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
 				sep = comma;
 			}
-			if (!vacopts->do_index_cleanup)
+			if (vacopts->no_index_cleanup)
 			{
-				/* INDEX_CLEANUP is supported since v12 */
+				/* "INDEX_CLEANUP FALSE" has been supported since v12 */
 				Assert(serverVersion >= 120000);
+				Assert(!vacopts->force_index_cleanup);
+				appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
+				sep = comma;
+			}
+			if (vacopts->force_index_cleanup)
+			{
+				/*
+				 * "INDEX_CLEANUP TRUE" has been supported since v12. Though
+				 * the --force-index-cleanup vacuumdb option was only added in
+				 * v14, it still works in the same way on v12+.
+				 */
+				Assert(serverVersion >= 120000);
+				Assert(!vacopts->no_index_cleanup);
 				appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
 				sep = comma;
 			}
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index c6d0a35e50..a5193eeaac 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1473,20 +1473,25 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
    </varlistentry>
 
    <varlistentry id="reloption-vacuum-index-cleanup" xreflabel="vacuum_index_cleanup">
-    <term><literal>vacuum_index_cleanup</literal>, <literal>toast.vacuum_index_cleanup</literal> (<type>boolean</type>)
+    <term><literal>vacuum_index_cleanup</literal>, <literal>toast.vacuum_index_cleanup</literal> (<type>enum</type>)
     <indexterm>
      <primary><varname>vacuum_index_cleanup</varname> storage parameter</primary>
     </indexterm>
     </term>
     <listitem>
      <para>
-      Enables or disables index cleanup when <command>VACUUM</command> is
-      run on this table.  The default value is <literal>true</literal>.
-      Disabling index cleanup can speed up <command>VACUUM</command> very
-      significantly, but may also lead to severely bloated indexes if table
-      modifications are frequent.  The <literal>INDEX_CLEANUP</literal>
-      parameter of <link linkend="sql-vacuum"><command>VACUUM</command></link>, if specified, overrides
-      the value of this option.
+      Disables or forces index cleanup when <command>VACUUM</command>
+      is run on this table.  The default value is
+      <literal>AUTO</literal>.  With <literal>OFF</literal> index
+      cleanup is disabled, with <literal>ON</literal> it is enabled,
+      and with <literal>AUTO</literal> the decision is made
+      dynamically by a <command>VACUUM</command> cost model.
+      Disabling index cleanup can speed up <command>VACUUM</command>
+      very significantly, but may also lead to severely bloated
+      indexes if table modifications are frequent.
+      The <literal>INDEX_CLEANUP</literal> parameter of <link
+       linkend="sql-vacuum"><command>VACUUM</command></link>, if
+      specified, overrides the value of this option.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 5f67c9d18b..3a32144ad0 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -32,7 +32,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
     DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
     SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
-    INDEX_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+    INDEX_CLEANUP [ <replaceable class="parameter">enum</replaceable> ]
     PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
@@ -193,20 +193,24 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     <term><literal>INDEX_CLEANUP</literal></term>
     <listitem>
      <para>
-      Specifies that <command>VACUUM</command> should attempt to remove
-      index entries pointing to dead tuples.  This is normally the desired
-      behavior and is the default unless the
-      <literal>vacuum_index_cleanup</literal> option has been set to false
-      for the table to be vacuumed.  Setting this option to false may be
-      useful when it is necessary to make vacuum run as quickly as possible,
-      for example to avoid imminent transaction ID wraparound
-      (see <xref linkend="vacuum-for-wraparound"/>).  However, if index
-      cleanup is not performed regularly, performance may suffer, because
-      as the table is modified, indexes will accumulate dead tuples
-      and the table itself will accumulate dead line pointers that cannot be
-      removed until index cleanup is completed.  This option has no effect
-      for tables that do not have an index and is ignored if the
-      <literal>FULL</literal> option is used.
+      Specifies when and how <command>VACUUM</command> should remove
+      index entries pointing to dead tuples.  The default setting is
+      <literal>AUTO</literal>, which makes vacuum remove index entries
+      unless the table has an insignificant number of dead tuples.
+      Other valid settings are <literal>OFF</literal>, which forces
+      index cleanup to be bypassed in all cases, and
+      <literal>ON</literal>, which prevents vacuum from considering
+      bypassing index cleanup in cases where there is more than zero
+      dead tuples.
+     </para>
+     <para>
+      If <literal>INDEX_CLEANUP</literal> is set to
+      <literal>OFF</literal> performance may suffer, because as the
+      table is modified, indexes will accumulate dead tuples and the
+      table itself will accumulate dead line pointers that cannot be
+      removed until index cleanup is completed.  This option has no
+      effect for tables that do not have an index and is ignored if
+      the <literal>FULL</literal> option is used.
      </para>
     </listitem>
    </varlistentry>
@@ -284,6 +288,15 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><replaceable class="parameter">enum</replaceable></term>
+    <listitem>
+     <para>
+      Specifies an enumeration value passed to the selected option.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><replaceable class="parameter">integer</replaceable></term>
     <listitem>
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 3e7f7ed68f..537724184e 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -244,6 +244,21 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-index-cleanup</option></term>
+      <listitem>
+       <para>
+        Always remove index entries pointing to dead tuples.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 12 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-process-toast</option></term>
       <listitem>
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 5e657849aa..e5771462d5 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -146,13 +146,15 @@ VACUUM no_index_cleanup;
 -- Both parent relation and toast are cleaned up.
 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
 VACUUM no_index_cleanup;
+ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = auto);
+VACUUM no_index_cleanup;
 -- Parameter is set for both the parent table and its toast relation.
 INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
     repeat('1234567890',269));
 DELETE FROM no_index_cleanup WHERE i < 45;
 -- Only toast index is cleaned up.
-ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false,
-    toast.vacuum_index_cleanup = true);
+ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = off,
+    toast.vacuum_index_cleanup = yes);
 VACUUM no_index_cleanup;
 -- Only parent is cleaned up.
 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
@@ -160,7 +162,7 @@ ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
 VACUUM no_index_cleanup;
 -- Test some extra relations.
 VACUUM (INDEX_CLEANUP FALSE) vaccluster;
-VACUUM (INDEX_CLEANUP FALSE) vactst; -- index cleanup option is ignored if no indexes
+VACUUM (INDEX_CLEANUP AUTO) vactst; -- index cleanup option is ignored if no indexes
 VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
 -- TRUNCATE option
 CREATE TABLE vac_truncate_test(i INT NOT NULL, j text)
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 93fd258fc0..f220fc28a7 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -127,13 +127,15 @@ VACUUM no_index_cleanup;
 -- Both parent relation and toast are cleaned up.
 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
 VACUUM no_index_cleanup;
+ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = auto);
+VACUUM no_index_cleanup;
 -- Parameter is set for both the parent table and its toast relation.
 INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
     repeat('1234567890',269));
 DELETE FROM no_index_cleanup WHERE i < 45;
 -- Only toast index is cleaned up.
-ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false,
-    toast.vacuum_index_cleanup = true);
+ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = off,
+    toast.vacuum_index_cleanup = yes);
 VACUUM no_index_cleanup;
 -- Only parent is cleaned up.
 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
@@ -141,7 +143,7 @@ ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
 VACUUM no_index_cleanup;
 -- Test some extra relations.
 VACUUM (INDEX_CLEANUP FALSE) vaccluster;
-VACUUM (INDEX_CLEANUP FALSE) vactst; -- index cleanup option is ignored if no indexes
+VACUUM (INDEX_CLEANUP AUTO) vactst; -- index cleanup option is ignored if no indexes
 VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
 
 -- TRUNCATE option
-- 
2.27.0

From 51143ed7d65748490e10a5d81476388e53f6af90 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <pg@bowt.ie>
Date: Wed, 26 May 2021 17:17:05 -0700
Subject: [PATCH v3] Generalize VACUUM's INDEX_CLEANUP option.

Generalize the reloption INDEX_CLEANUP to enable users to disable the
index vacuum bypassing optimization added by commit 5100010e, as well as
any similar optimizations that may be added in the future.

The reloption is now a trinary boolean style enum whose default is
'auto', which preserves the current VACUUM behavior.  Explicitly setting
INDEX_CLEANUP to 'on' now avoids any optimizations that skip index
vacuuming, though it doesn't have any effect on the failsafe mechanism.

The INDEX_CLEANUP reloption was originally added by commit a96c41fe as
an emergency option to allow DBAs to opt out of index vacuuming in
extreme cases -- cases where the likely alternative is an anti-wraparoud
failure.  The reloption can still be used for that, but it seems like a
much less useful option now that VACUUM has the failsafe mechanism added
by commit 1e55e7d1.

Author: Peter Geoghegan <pg@bowt.ie>
Reviewed-By: Masahiko Sawada <sawada.mshk@gmail.com>
Reviewed-By: Justin Pryzby <pryzby@telsasoft.com>
Discussion: https://postgr.es/m/CAH2-WznrBoCST4_Gxh_G9hA8NzGUbeBGnOUC8FcXcrhqsv6OHQ@mail.gmail.com
---
 src/include/commands/vacuum.h          | 31 ++++++-----
 src/include/utils/rel.h                | 15 +++++-
 src/backend/access/common/reloptions.c | 35 ++++++++----
 src/backend/access/heap/vacuumlazy.c   | 56 ++++++++++++-------
 src/backend/commands/vacuum.c          | 75 +++++++++++++++++++-------
 src/backend/postmaster/autovacuum.c    |  9 +++-
 src/bin/psql/tab-complete.c            |  4 +-
 src/bin/scripts/vacuumdb.c             | 62 +++++++++++++++++----
 doc/src/sgml/ref/create_table.sgml     | 21 +++++---
 doc/src/sgml/ref/vacuum.sgml           | 29 +++++-----
 doc/src/sgml/ref/vacuumdb.sgml         | 15 ++++++
 src/test/regress/expected/vacuum.out   |  8 +--
 src/test/regress/sql/vacuum.sql        |  8 +--
 13 files changed, 268 insertions(+), 100 deletions(-)

diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index cb27257bb6..57f93db4a9 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -185,17 +185,24 @@ typedef struct VacAttrStats
 #define VACOPT_DISABLE_PAGE_SKIPPING 0x80	/* don't skip any pages */
 
 /*
- * A ternary value used by vacuum parameters.
+ * Values used by VACUUM's index_cleanup and truncate parameters.
  *
- * DEFAULT value is used to determine the value based on other
- * configurations, e.g. reloptions.
+ * *_UNSPECIFIED is used as an initial placeholder.  When VACUUM command does
+ * not have explicit specification (or when running in an autovacuum worker),
+ * final value used by vacuumlazy.c is taken from the reloption.
+ *
+ * *_AUTO is used by index_cleanup option/reloption only, where it is the
+ * default.
+ *
+ * *_ENABLED is the default for the truncate option/reloption.
  */
-typedef enum VacOptTernaryValue
+typedef enum VacOptValue
 {
-	VACOPT_TERNARY_DEFAULT = 0,
-	VACOPT_TERNARY_DISABLED,
-	VACOPT_TERNARY_ENABLED,
-} VacOptTernaryValue;
+	VACOPT_VAL_UNSPECIFIED = 0,
+	VACOPT_VAL_AUTO,
+	VACOPT_VAL_DISABLED,
+	VACOPT_VAL_ENABLED,
+} VacOptValue;
 
 /*
  * Parameters customizing behavior of VACUUM and ANALYZE.
@@ -216,10 +223,10 @@ typedef struct VacuumParams
 	int			log_min_duration;	/* minimum execution threshold in ms at
 									 * which  verbose logs are activated, -1
 									 * to use default */
-	VacOptTernaryValue index_cleanup;	/* Do index vacuum and cleanup,
-										 * default value depends on reloptions */
-	VacOptTernaryValue truncate;	/* Truncate empty pages at the end,
-									 * default value depends on reloptions */
+	VacOptValue index_cleanup;	/* Do index vacuum and cleanup, default value
+								 *  depends on reloptions */
+	VacOptValue truncate;	/* Truncate empty pages at the end, default value
+							 * depends on reloptions */
 
 	/*
 	 * The number of parallel vacuum workers.  0 by default which means choose
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 774ac5b2b1..6924ed730d 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -307,6 +307,19 @@ typedef struct AutoVacOpts
 	float8		analyze_scale_factor;
 } AutoVacOpts;
 
+/*
+ * A ternary value used by vacuum parameters.
+ *
+ * DEFAULT value is used to determine the value based on other
+ * configurations, e.g. reloptions.
+ */
+typedef enum StdRdOptIndexCleanup
+{
+	STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO = 0,
+	STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF,
+	STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON
+} StdRdOptIndexCleanup;
+
 typedef struct StdRdOptions
 {
 	int32		vl_len_;		/* varlena header (do not touch directly!) */
@@ -316,7 +329,7 @@ typedef struct StdRdOptions
 	AutoVacOpts autovacuum;		/* autovacuum-related options */
 	bool		user_catalog_table; /* use as an additional catalog relation */
 	int			parallel_workers;	/* max number of parallel workers */
-	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
+	StdRdOptIndexCleanup vacuum_index_cleanup;	/* controls index vacuuming */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
 } StdRdOptions;
 
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 5554275e64..7ef934cbc4 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -140,15 +140,6 @@ static relopt_bool boolRelOpts[] =
 		},
 		false
 	},
-	{
-		{
-			"vacuum_index_cleanup",
-			"Enables index vacuuming and index cleanup",
-			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
-			ShareUpdateExclusiveLock
-		},
-		true
-	},
 	{
 		{
 			"vacuum_truncate",
@@ -474,6 +465,21 @@ static relopt_real realRelOpts[] =
 	{{NULL}}
 };
 
+/* values from StdRdOptIndexCleanup */
+relopt_enum_elt_def StdRdOptIndexCleanupValues[] =
+{
+	{"auto", STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO},
+	{"on", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
+	{"off", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
+	{"true", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
+	{"false", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
+	{"yes", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
+	{"no", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
+	{"1", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
+	{"0", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
+	{(const char *) NULL}		/* list terminator */
+};
+
 /* values from GistOptBufferingMode */
 relopt_enum_elt_def gistBufferingOptValues[] =
 {
@@ -494,6 +500,17 @@ relopt_enum_elt_def viewCheckOptValues[] =
 
 static relopt_enum enumRelOpts[] =
 {
+	{
+		{
+			"vacuum_index_cleanup",
+			"Forces or disables index vacuuming and index cleanup",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		StdRdOptIndexCleanupValues,
+		STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO,
+		gettext_noop("Valid values are \"on\", \"off\", and \"auto\".")
+	},
 	{
 		{
 			"buffering",
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 88db2e2cfc..44f1bf0d86 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -313,6 +313,8 @@ typedef struct LVRelState
 	bool		do_index_cleanup;
 	/* Wraparound failsafe in effect? (implies !do_index_vacuuming) */
 	bool		do_failsafe;
+	/* Consider bypass optimization? */
+	bool		do_bypass_optimization;
 
 	/* Buffer access strategy and parallel state */
 	BufferAccessStrategy bstrategy;
@@ -405,7 +407,7 @@ static void lazy_scan_prune(LVRelState *vacrel, Buffer buf,
 							BlockNumber blkno, Page page,
 							GlobalVisState *vistest,
 							LVPagePruneState *prunestate);
-static void lazy_vacuum(LVRelState *vacrel, bool onecall);
+static void lazy_vacuum(LVRelState *vacrel);
 static bool lazy_vacuum_all_indexes(LVRelState *vacrel);
 static void lazy_vacuum_heap_rel(LVRelState *vacrel);
 static int	lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno,
@@ -507,8 +509,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 	MultiXactId MultiXactCutoff;
 
 	Assert(params != NULL);
-	Assert(params->index_cleanup != VACOPT_TERNARY_DEFAULT);
-	Assert(params->truncate != VACOPT_TERNARY_DEFAULT);
+	Assert(params->index_cleanup != VACOPT_VAL_UNSPECIFIED);
+	Assert(params->truncate != VACOPT_VAL_UNSPECIFIED);
+	Assert(params->truncate != VACOPT_VAL_AUTO);
 
 	/* measure elapsed time iff autovacuum logging requires it */
 	if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
@@ -557,14 +560,33 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 	vacrel->rel = rel;
 	vac_open_indexes(vacrel->rel, RowExclusiveLock, &vacrel->nindexes,
 					 &vacrel->indrels);
+
+	/*
+	 * index_cleanup either disables index vacuuming and cleanup or forces it
+	 * to go ahead when we would otherwise apply the bypass optimization.  The
+	 * default is 'auto', which leaves the final decision up to lazy_vacuum().
+	 */
 	vacrel->do_index_vacuuming = true;
 	vacrel->do_index_cleanup = true;
 	vacrel->do_failsafe = false;
-	if (params->index_cleanup == VACOPT_TERNARY_DISABLED)
+	vacrel->do_bypass_optimization = true;
+	if (params->index_cleanup == VACOPT_VAL_DISABLED)
 	{
+		/* Force disable index vacuuming up-front */
 		vacrel->do_index_vacuuming = false;
 		vacrel->do_index_cleanup = false;
 	}
+	else if (params->index_cleanup == VACOPT_VAL_ENABLED)
+	{
+		/* Force index vacuuming (never bypass) */
+		vacrel->do_bypass_optimization = false;
+	}
+	else
+	{
+		/* Default/auto, make all decisions dynamically */
+		Assert(params->index_cleanup == VACOPT_VAL_AUTO);
+	}
+
 	vacrel->bstrategy = bstrategy;
 	vacrel->old_rel_pages = rel->rd_rel->relpages;
 	vacrel->old_live_tuples = rel->rd_rel->reltuples;
@@ -893,8 +915,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 				next_fsm_block_to_vacuum;
 	PGRUsage	ru0;
 	Buffer		vmbuffer = InvalidBuffer;
-	bool		skipping_blocks,
-				have_vacuumed_indexes = false;
+	bool		skipping_blocks;
 	StringInfoData buf;
 	const int	initprog_index[] = {
 		PROGRESS_VACUUM_PHASE,
@@ -1166,8 +1187,8 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 			}
 
 			/* Remove the collected garbage tuples from table and indexes */
-			lazy_vacuum(vacrel, false);
-			have_vacuumed_indexes = true;
+			vacrel->do_bypass_optimization = false;
+			lazy_vacuum(vacrel);
 
 			/*
 			 * Vacuum the Free Space Map to make newly-freed space visible on
@@ -1579,7 +1600,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
 
 	/* If any tuples need to be deleted, perform final vacuum cycle */
 	if (dead_tuples->num_tuples > 0)
-		lazy_vacuum(vacrel, !have_vacuumed_indexes);
+		lazy_vacuum(vacrel);
 
 	/*
 	 * Vacuum the remainder of the Free Space Map.  We must do this whether or
@@ -2064,9 +2085,9 @@ retry:
  * wraparound.
  */
 static void
-lazy_vacuum(LVRelState *vacrel, bool onecall)
+lazy_vacuum(LVRelState *vacrel)
 {
-	bool		do_bypass_optimization;
+	bool		bypass;
 
 	/* Should not end up here with no indexes */
 	Assert(vacrel->nindexes > 0);
@@ -2099,8 +2120,8 @@ lazy_vacuum(LVRelState *vacrel, bool onecall)
 	 * It's far easier to ensure that 99%+ of all UPDATEs against a table use
 	 * HOT through careful tuning.
 	 */
-	do_bypass_optimization = false;
-	if (onecall && vacrel->rel_pages > 0)
+	bypass = false;
+	if (vacrel->do_bypass_optimization && vacrel->rel_pages > 0)
 	{
 		BlockNumber threshold;
 
@@ -2132,12 +2153,11 @@ lazy_vacuum(LVRelState *vacrel, bool onecall)
 		 * expanded to cover more cases then this may need to be reconsidered.
 		 */
 		threshold = (double) vacrel->rel_pages * BYPASS_THRESHOLD_PAGES;
-		do_bypass_optimization =
-			(vacrel->lpdead_item_pages < threshold &&
-			 vacrel->lpdead_items < MAXDEADTUPLES(32L * 1024L * 1024L));
+		bypass = (vacrel->lpdead_item_pages < threshold &&
+				  vacrel->lpdead_items < MAXDEADTUPLES(32L * 1024L * 1024L));
 	}
 
-	if (do_bypass_optimization)
+	if (bypass)
 	{
 		/*
 		 * There are almost zero TIDs.  Behave as if there were precisely
@@ -3140,7 +3160,7 @@ should_attempt_truncation(LVRelState *vacrel, VacuumParams *params)
 {
 	BlockNumber possibly_freeable;
 
-	if (params->truncate == VACOPT_TERNARY_DISABLED)
+	if (params->truncate == VACOPT_VAL_DISABLED)
 		return false;
 
 	if (vacrel->do_failsafe)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7421d7cfbd..fcf7da641f 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -88,7 +88,7 @@ static void vac_truncate_clog(TransactionId frozenXID,
 							  MultiXactId lastSaneMinMulti);
 static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params);
 static double compute_parallel_delay(void);
-static VacOptTernaryValue get_vacopt_ternary_value(DefElem *def);
+static VacOptValue get_vacopt_ternary_value(DefElem *def);
 
 /*
  * Primary entry point for manual VACUUM and ANALYZE commands
@@ -109,9 +109,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		process_toast = true;
 	ListCell   *lc;
 
-	/* Set default value */
-	params.index_cleanup = VACOPT_TERNARY_DEFAULT;
-	params.truncate = VACOPT_TERNARY_DEFAULT;
+	/* index_cleanup and truncate are unspecified at first */
+	params.index_cleanup = VACOPT_VAL_UNSPECIFIED;
+	params.truncate = VACOPT_VAL_UNSPECIFIED;
 
 	/* By default parallel vacuum is enabled */
 	params.nworkers = 0;
@@ -142,7 +142,21 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		else if (strcmp(opt->defname, "disable_page_skipping") == 0)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
-			params.index_cleanup = get_vacopt_ternary_value(opt);
+		{
+			/* Interpret no string as the default, which is 'auto' */
+			if (!opt->arg)
+				params.index_cleanup = VACOPT_VAL_AUTO;
+			else
+			{
+				char	   *sval = defGetString(opt);
+
+				/* Try matching on 'auto' string, or fall back on boolean */
+				if (pg_strcasecmp(sval, "auto") == 0)
+					params.index_cleanup = VACOPT_VAL_AUTO;
+				else
+					params.index_cleanup = get_vacopt_ternary_value(opt);
+			}
+		}
 		else if (strcmp(opt->defname, "process_toast") == 0)
 			process_toast = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
@@ -1938,24 +1952,44 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	lockrelid = rel->rd_lockInfo.lockRelId;
 	LockRelationIdForSession(&lockrelid, lmode);
 
-	/* Set index cleanup option based on reloptions if not yet */
-	if (params->index_cleanup == VACOPT_TERNARY_DEFAULT)
+	/*
+	 * Set index_cleanup option based on index_cleanup reloption if it wasn't
+	 * specified in VACUUM command, or when running in an autovacuum worker
+	 */
+	if (params->index_cleanup == VACOPT_VAL_UNSPECIFIED)
 	{
-		if (rel->rd_options == NULL ||
-			((StdRdOptions *) rel->rd_options)->vacuum_index_cleanup)
-			params->index_cleanup = VACOPT_TERNARY_ENABLED;
+		StdRdOptIndexCleanup vacuum_index_cleanup;
+
+		/* The reloption default is 'auto', just like the VACUUM option */
+		if (rel->rd_options == NULL)
+			vacuum_index_cleanup = STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO;
 		else
-			params->index_cleanup = VACOPT_TERNARY_DISABLED;
+			vacuum_index_cleanup =
+					((StdRdOptions *) rel->rd_options)->vacuum_index_cleanup;
+
+		if (vacuum_index_cleanup == STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO)
+			params->index_cleanup = VACOPT_VAL_AUTO;
+		else if (vacuum_index_cleanup == STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON)
+			params->index_cleanup = VACOPT_VAL_ENABLED;
+		else
+		{
+			Assert(vacuum_index_cleanup ==
+				   STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF);
+			params->index_cleanup = VACOPT_VAL_DISABLED;
+		}
 	}
 
-	/* Set truncate option based on reloptions if not yet */
-	if (params->truncate == VACOPT_TERNARY_DEFAULT)
+	/*
+	 * Set truncate option based on truncate reloption if it wasn't specified
+	 * in VACUUM command, or when running in an autovacuum worker
+	 */
+	if (params->truncate == VACOPT_VAL_UNSPECIFIED)
 	{
 		if (rel->rd_options == NULL ||
 			((StdRdOptions *) rel->rd_options)->vacuum_truncate)
-			params->truncate = VACOPT_TERNARY_ENABLED;
+			params->truncate = VACOPT_VAL_ENABLED;
 		else
-			params->truncate = VACOPT_TERNARY_DISABLED;
+			params->truncate = VACOPT_VAL_DISABLED;
 	}
 
 	/*
@@ -2217,11 +2251,14 @@ compute_parallel_delay(void)
 /*
  * A wrapper function of defGetBoolean().
  *
- * This function returns VACOPT_TERNARY_ENABLED and VACOPT_TERNARY_DISABLED
- * instead of true and false.
+ * This function returns VACOPT_VAL_ENABLED and VACOPT_VAL_DISABLED instead of
+ * true and false.
+ *
+ * There is also a VACOPT_VAC_AUTO value that is used only by the
+ * INDEX_CLEANUP option.  We don't handle that here.
  */
-static VacOptTernaryValue
+static VacOptValue
 get_vacopt_ternary_value(DefElem *def)
 {
-	return defGetBoolean(def) ? VACOPT_TERNARY_ENABLED : VACOPT_TERNARY_DISABLED;
+	return defGetBoolean(def) ? VACOPT_VAL_ENABLED : VACOPT_VAL_DISABLED;
 }
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index d516df0ac5..95a3cf0d52 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2976,8 +2976,13 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab->at_params.options = (dovacuum ? VACOPT_VACUUM : 0) |
 			(doanalyze ? VACOPT_ANALYZE : 0) |
 			(!wraparound ? VACOPT_SKIP_LOCKED : 0);
-		tab->at_params.index_cleanup = VACOPT_TERNARY_DEFAULT;
-		tab->at_params.truncate = VACOPT_TERNARY_DEFAULT;
+		/*
+		 * index_cleanup and truncate are unspecified at first in autovacuum.
+		 * They will be filled in with usable values via the reloption (or its
+		 * default) later.
+		 */
+		tab->at_params.index_cleanup = VACOPT_VAL_UNSPECIFIED;
+		tab->at_params.truncate = VACOPT_VAL_UNSPECIFIED;
 		/* As of now, we don't support parallel vacuum for autovacuum */
 		tab->at_params.nworkers = -1;
 		tab->at_params.freeze_min_age = freeze_min_age;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index bd8e9ea2f8..38af5682f2 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3917,8 +3917,10 @@ psql_completion(const char *text, int start, int end)
 						  "DISABLE_PAGE_SKIPPING", "SKIP_LOCKED",
 						  "INDEX_CLEANUP", "PROCESS_TOAST",
 						  "TRUNCATE", "PARALLEL");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|PROCESS_TOAST|TRUNCATE"))
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_TOAST|TRUNCATE"))
 			COMPLETE_WITH("ON", "OFF");
+		else if (TailMatches("INDEX_CLEANUP"))
+			COMPLETE_WITH("AUTO", "ON", "OFF");
 	}
 	else if (HeadMatches("VACUUM") && TailMatches("("))
 		/* "VACUUM (" should be caught above, so assume we want columns */
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 069a861aab..bf8755285d 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -39,7 +39,8 @@ typedef struct vacuumingOptions
 	int			min_mxid_age;
 	int			parallel_workers;	/* >= 0 indicates user specified the
 									 * parallel degree, otherwise -1 */
-	bool		do_index_cleanup;
+	bool		no_index_cleanup;
+	bool		force_index_cleanup;
 	bool		do_truncate;
 	bool		process_toast;
 } vacuumingOptions;
@@ -99,8 +100,9 @@ main(int argc, char *argv[])
 		{"min-xid-age", required_argument, NULL, 6},
 		{"min-mxid-age", required_argument, NULL, 7},
 		{"no-index-cleanup", no_argument, NULL, 8},
-		{"no-truncate", no_argument, NULL, 9},
-		{"no-process-toast", no_argument, NULL, 10},
+		{"force-index-cleanup", no_argument, NULL, 9},
+		{"no-truncate", no_argument, NULL, 10},
+		{"no-process-toast", no_argument, NULL, 11},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -126,7 +128,8 @@ main(int argc, char *argv[])
 	/* initialize options */
 	memset(&vacopts, 0, sizeof(vacopts));
 	vacopts.parallel_workers = -1;
-	vacopts.do_index_cleanup = true;
+	vacopts.no_index_cleanup = false;
+	vacopts.force_index_cleanup = false;
 	vacopts.do_truncate = true;
 	vacopts.process_toast = true;
 
@@ -233,12 +236,15 @@ main(int argc, char *argv[])
 				}
 				break;
 			case 8:
-				vacopts.do_index_cleanup = false;
+				vacopts.no_index_cleanup = true;
 				break;
 			case 9:
-				vacopts.do_truncate = false;
+				vacopts.force_index_cleanup = true;
 				break;
 			case 10:
+				vacopts.do_truncate = false;
+				break;
+			case 11:
 				vacopts.process_toast = false;
 				break;
 			default:
@@ -285,12 +291,18 @@ main(int argc, char *argv[])
 						 "disable-page-skipping");
 			exit(1);
 		}
-		if (!vacopts.do_index_cleanup)
+		if (vacopts.no_index_cleanup)
 		{
 			pg_log_error("cannot use the \"%s\" option when performing only analyze",
 						 "no-index-cleanup");
 			exit(1);
 		}
+		if (vacopts.force_index_cleanup)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "force-index-cleanup");
+			exit(1);
+		}
 		if (!vacopts.do_truncate)
 		{
 			pg_log_error("cannot use the \"%s\" option when performing only analyze",
@@ -323,6 +335,14 @@ main(int argc, char *argv[])
 		}
 	}
 
+	/* Prohibit --no-index-cleanup and --force-index-cleanup together */
+	if (vacopts.no_index_cleanup && vacopts.force_index_cleanup)
+	{
+		pg_log_error("cannot use the \"%s\" option with the \"%s\" option",
+					 "no-index-cleanup", "force-index-cleanup");
+		exit(1);
+	}
+
 	/* fill cparams except for dbname, which is set below */
 	cparams.pghost = host;
 	cparams.pgport = port;
@@ -453,7 +473,7 @@ vacuum_one_database(ConnParams *cparams,
 		exit(1);
 	}
 
-	if (!vacopts->do_index_cleanup && PQserverVersion(conn) < 120000)
+	if (vacopts->no_index_cleanup && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
 		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
@@ -461,6 +481,14 @@ vacuum_one_database(ConnParams *cparams,
 		exit(1);
 	}
 
+	if (vacopts->force_index_cleanup && PQserverVersion(conn) < 120000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "force-index-cleanup", "12");
+		exit(1);
+	}
+
 	if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -878,13 +906,26 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
 				sep = comma;
 			}
-			if (!vacopts->do_index_cleanup)
+			if (vacopts->no_index_cleanup)
 			{
-				/* INDEX_CLEANUP is supported since v12 */
+				/* "INDEX_CLEANUP FALSE" has been supported since v12 */
 				Assert(serverVersion >= 120000);
+				Assert(!vacopts->force_index_cleanup);
 				appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
 				sep = comma;
 			}
+			if (vacopts->force_index_cleanup)
+			{
+				/*
+				 * "INDEX_CLEANUP TRUE" has been supported since v12. Though
+				 * the --force-index-cleanup vacuumdb option was only added in
+				 * v14, it still works in the same way on v12+.
+				 */
+				Assert(serverVersion >= 120000);
+				Assert(!vacopts->no_index_cleanup);
+				appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
+				sep = comma;
+			}
 			if (!vacopts->do_truncate)
 			{
 				/* TRUNCATE is supported since v12 */
@@ -998,6 +1039,7 @@ help(const char *progname)
 	printf(_("      --min-mxid-age=MXID_AGE     minimum multixact ID age of tables to vacuum\n"));
 	printf(_("      --min-xid-age=XID_AGE       minimum transaction ID age of tables to vacuum\n"));
 	printf(_("      --no-index-cleanup          don't remove index entries that point to dead tuples\n"));
+	printf(_("      --force-index-cleanup       always remove index entries that point to dead tuples\n"));
 	printf(_("      --no-process-toast          skip the TOAST table associated with the table to vacuum\n"));
 	printf(_("      --no-truncate               don't truncate empty pages at the end of the table\n"));
 	printf(_("  -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index c6d0a35e50..a5193eeaac 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1473,20 +1473,25 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
    </varlistentry>
 
    <varlistentry id="reloption-vacuum-index-cleanup" xreflabel="vacuum_index_cleanup">
-    <term><literal>vacuum_index_cleanup</literal>, <literal>toast.vacuum_index_cleanup</literal> (<type>boolean</type>)
+    <term><literal>vacuum_index_cleanup</literal>, <literal>toast.vacuum_index_cleanup</literal> (<type>enum</type>)
     <indexterm>
      <primary><varname>vacuum_index_cleanup</varname> storage parameter</primary>
     </indexterm>
     </term>
     <listitem>
      <para>
-      Enables or disables index cleanup when <command>VACUUM</command> is
-      run on this table.  The default value is <literal>true</literal>.
-      Disabling index cleanup can speed up <command>VACUUM</command> very
-      significantly, but may also lead to severely bloated indexes if table
-      modifications are frequent.  The <literal>INDEX_CLEANUP</literal>
-      parameter of <link linkend="sql-vacuum"><command>VACUUM</command></link>, if specified, overrides
-      the value of this option.
+      Disables or forces index cleanup when <command>VACUUM</command>
+      is run on this table.  The default value is
+      <literal>AUTO</literal>.  With <literal>OFF</literal> index
+      cleanup is disabled, with <literal>ON</literal> it is enabled,
+      and with <literal>AUTO</literal> the decision is made
+      dynamically by a <command>VACUUM</command> cost model.
+      Disabling index cleanup can speed up <command>VACUUM</command>
+      very significantly, but may also lead to severely bloated
+      indexes if table modifications are frequent.
+      The <literal>INDEX_CLEANUP</literal> parameter of <link
+       linkend="sql-vacuum"><command>VACUUM</command></link>, if
+      specified, overrides the value of this option.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 5f67c9d18b..761fdbe4f4 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -32,7 +32,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
     DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
     SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
-    INDEX_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+    INDEX_CLEANUP { AUTO | ON | OFF }
     PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
@@ -193,19 +193,20 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     <term><literal>INDEX_CLEANUP</literal></term>
     <listitem>
      <para>
-      Specifies that <command>VACUUM</command> should attempt to remove
-      index entries pointing to dead tuples.  This is normally the desired
-      behavior and is the default unless the
-      <literal>vacuum_index_cleanup</literal> option has been set to false
-      for the table to be vacuumed.  Setting this option to false may be
-      useful when it is necessary to make vacuum run as quickly as possible,
-      for example to avoid imminent transaction ID wraparound
-      (see <xref linkend="vacuum-for-wraparound"/>).  However, if index
-      cleanup is not performed regularly, performance may suffer, because
-      as the table is modified, indexes will accumulate dead tuples
-      and the table itself will accumulate dead line pointers that cannot be
-      removed until index cleanup is completed.  This option has no effect
-      for tables that do not have an index and is ignored if the
+      Specifies when and how <command>VACUUM</command> should remove
+      index entries pointing to dead tuples.  The default setting is
+      <literal>AUTO</literal>, which makes vacuum remove index entries
+      unless the table has an insignificant number of dead tuples.
+      Other valid settings are <literal>OFF</literal>, which forces
+      index cleanup to be bypassed in all cases, and
+      <literal>ON</literal>, which prevents vacuum from considering
+      bypassing index cleanup in cases where there are more than zero
+      dead tuples.  If <literal>INDEX_CLEANUP</literal> is set to
+      <literal>OFF</literal>, performance may suffer, because as the
+      table is modified indexes will accumulate dead tuples and the
+      table itself will accumulate dead line pointers that cannot be
+      removed until index cleanup is completed.  This option has no
+      effect for tables that have no index and is ignored if the
       <literal>FULL</literal> option is used.
      </para>
     </listitem>
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 3e7f7ed68f..d799804cda 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -244,6 +244,21 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--force-index-cleanup</option></term>
+      <listitem>
+       <para>
+        Always remove index entries pointing to dead tuples.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 12 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-process-toast</option></term>
       <listitem>
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 5e657849aa..e5771462d5 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -146,13 +146,15 @@ VACUUM no_index_cleanup;
 -- Both parent relation and toast are cleaned up.
 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
 VACUUM no_index_cleanup;
+ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = auto);
+VACUUM no_index_cleanup;
 -- Parameter is set for both the parent table and its toast relation.
 INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
     repeat('1234567890',269));
 DELETE FROM no_index_cleanup WHERE i < 45;
 -- Only toast index is cleaned up.
-ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false,
-    toast.vacuum_index_cleanup = true);
+ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = off,
+    toast.vacuum_index_cleanup = yes);
 VACUUM no_index_cleanup;
 -- Only parent is cleaned up.
 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
@@ -160,7 +162,7 @@ ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
 VACUUM no_index_cleanup;
 -- Test some extra relations.
 VACUUM (INDEX_CLEANUP FALSE) vaccluster;
-VACUUM (INDEX_CLEANUP FALSE) vactst; -- index cleanup option is ignored if no indexes
+VACUUM (INDEX_CLEANUP AUTO) vactst; -- index cleanup option is ignored if no indexes
 VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
 -- TRUNCATE option
 CREATE TABLE vac_truncate_test(i INT NOT NULL, j text)
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 93fd258fc0..f220fc28a7 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -127,13 +127,15 @@ VACUUM no_index_cleanup;
 -- Both parent relation and toast are cleaned up.
 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
 VACUUM no_index_cleanup;
+ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = auto);
+VACUUM no_index_cleanup;
 -- Parameter is set for both the parent table and its toast relation.
 INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
     repeat('1234567890',269));
 DELETE FROM no_index_cleanup WHERE i < 45;
 -- Only toast index is cleaned up.
-ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false,
-    toast.vacuum_index_cleanup = true);
+ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = off,
+    toast.vacuum_index_cleanup = yes);
 VACUUM no_index_cleanup;
 -- Only parent is cleaned up.
 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
@@ -141,7 +143,7 @@ ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
 VACUUM no_index_cleanup;
 -- Test some extra relations.
 VACUUM (INDEX_CLEANUP FALSE) vaccluster;
-VACUUM (INDEX_CLEANUP FALSE) vactst; -- index cleanup option is ignored if no indexes
+VACUUM (INDEX_CLEANUP AUTO) vactst; -- index cleanup option is ignored if no indexes
 VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
 
 -- TRUNCATE option
-- 
2.27.0