v6-0004-Skip-index-vacuum-if-the-table-is-at-risk-XID-wra.patch

From e19efb562dcfbd2ca8ebbb71b331da09ea934f3a Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 24 Mar 2021 11:27:05 +0900
Subject: [PATCH v6 4/4] Skip index vacuum if the table is at risk XID
 wraparound.

This commit add new GUC parameters vacuum_skip_index_age and
vacuum_multixact_skip_index_age that specify age at which VACUUM
should skip index cleanup to hurry finishing in order to
advance relfrozenxid/relminmxid.

After each index vacuuming (in non-parallel vacuum case), we check if
the table's relfrozenxid/relminmxid are too old comparing those new
GUC parameters. If so, we skip further index vacuuming within the
vacuum operation.

This behavior is intended to deal with the risk of XID wraparound, the
default values are much higher, 1.8 billion.

Although users can set those parameters, VACUUM will silently
adjust the effective value more than 105% of
autovacuum_freeze_max_age/autovacuum_multixact_freeze_max_age, so that
only anti-wraparound autovacuuma and aggressive scan have a change to
skip index vacuuming.
---
 doc/src/sgml/config.sgml                      |  51 ++++++
 doc/src/sgml/maintenance.sgml                 |  10 +-
 src/backend/access/heap/vacuumlazy.c          | 146 +++++++++++++++---
 src/backend/commands/vacuum.c                 |   2 +
 src/backend/utils/misc/guc.c                  |  25 ++-
 src/backend/utils/misc/postgresql.conf.sample |   2 +
 src/include/commands/vacuum.h                 |   2 +
 7 files changed, 217 insertions(+), 21 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 5679b40dd5..71483d8598 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8545,6 +8545,31 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-vacuum-skip-index-age" xreflabel="vacuum_skip_index_age">
+      <term><varname>vacuum_skip_index_age</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>vacuum_skip_index_age</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        <command>VACUUM</command> skips index cleanup if the table's
+        <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field has reached
+        the age specified by this setting.   A <command>VACUUM</command> with skipping
+        index cleanup hurries finishing <command>VACUUM</command> to advance
+        <structname>pg_class</structname>.<structfield>relfrozenxid</structfield>
+        as quickly as possible.  This is an equivalent behavior to setting
+        <literal>OFF</literal> to <literal>INDEX_CLEANUP</literal> option except that
+        this parameters skips index cleanup even in the middle of vacuum operation.
+        The default is 1.8 billion transactions. Although users can set this value
+        anywhere from zero to 2.1 billion, <command>VACUUM</command> will silently
+        adjust the effective value more than 105% of
+        <xref linkend="guc-autovacuum-freeze-max-age"/>, so that only anti-wraparound
+        autovacuums and aggressive scans have a chance to skip index cleanup.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
       <term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)
       <indexterm>
@@ -8591,6 +8616,32 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-multixact-vacuum-skip-index-age" xreflabel="vacuum_multixact_skip_index_age">
+      <term><varname>vacuum_multixact_skip_index_age</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>vacuum_multixact_skip_index_age</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        <command>VACUUM</command> skips index cleanup if the table's
+        <structname>pg_class</structname>.<structfield>relminmxid</structfield> field has reached
+        the age specified by this setting.   A <command>VACUUM</command> with skipping
+        index cleanup hurries finishing <command>VACUUM</command> to advance
+        <structname>pg_class</structname>.<structfield>relminmxid</structfield>
+        as quickly as possible.  This is an equivalent behavior to setting
+        <literal>OFF</literal> to <literal>INDEX_CLEANUP</literal> option except that
+        this parameters skips index cleanup even in the middle of vacuum operation.
+        The default is 1.8 billion multixacts. Although users can set this value
+        anywhere from zero to 2.1 billion, <command>VACUUM</command> will silently
+        adjust the effective value more than 105% of
+        <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>, so that only
+        anti-wraparound autovacuums and aggressive scans have a chance to skip
+        index cleanup.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-bytea-output" xreflabel="bytea_output">
       <term><varname>bytea_output</varname> (<type>enum</type>)
       <indexterm>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 4d8ad754f8..4d3674c1b4 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -607,8 +607,14 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
    <para>
     If for some reason autovacuum fails to clear old XIDs from a table, the
-    system will begin to emit warning messages like this when the database's
-    oldest XIDs reach forty million transactions from the wraparound point:
+    system will begin to skip index cleanup to hurry finishing vacuum
+    operation. <xref linkend="guc-vacuum-skip-index-age"/> controls when
+    <command>VACUUM</command> and autovacuum do that.
+   </para>
+
+    <para>
+     The system emits warning messages like this when the database's
+     oldest XIDs reach forty million transactions from the wraparound point:
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index ac250d0fab..0885dc4b08 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -317,6 +317,7 @@ typedef struct LVRelStats
 	LVDeadTuples *dead_tuples;
 	int			num_index_scans;
 	bool		lock_waiter_detected;
+	bool		skip_index_vacuum; /* skip further index vacuuming/cleanup ? */
 
 	/* Statistics about indexes */
 	IndexBulkDeleteResult **indstats;
@@ -398,9 +399,10 @@ static void lazy_vacuum_pruned_items(Relation onerel, LVRelStats *vacrelstats,
 static void lazy_vacuum_heap(Relation onerel, LVRelStats *vacrelstats);
 static bool lazy_check_needs_freeze(Buffer buf, bool *hastup,
 									LVRelStats *vacrelstats);
-static void lazy_vacuum_all_indexes(Relation onerel, Relation *Irel,
+static bool check_index_vacuum_xid_limit(Relation onerel);
+static bool lazy_vacuum_all_indexes(Relation onerel, Relation *Irel,
 									LVRelStats *vacrelstats, LVParallelState *lps,
-									int nindexes);
+									int nindexes, VacOptTernaryValue index_cleanup);
 static void lazy_vacuum_index(Relation indrel, IndexBulkDeleteResult **stats,
 							  LVDeadTuples *dead_tuples, double reltuples, LVRelStats *vacrelstats);
 static void lazy_cleanup_index(Relation indrel,
@@ -558,6 +560,7 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
 	vacrelstats->num_index_scans = 0;
 	vacrelstats->pages_removed = 0;
 	vacrelstats->lock_waiter_detected = false;
+	vacrelstats->skip_index_vacuum = false;
 
 	/* Open all indexes of the relation */
 	vac_open_indexes(onerel, RowExclusiveLock, &nindexes, &Irel);
@@ -1964,7 +1967,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 	 * lazy_vacuum_pruned_items() decided to skip index vacuuming, but not
 	 * with INDEX_CLEANUP=OFF.
 	 */
-	if (nindexes > 0 && params->index_cleanup != VACOPT_TERNARY_DISABLED)
+	if (nindexes > 0 && params->index_cleanup != VACOPT_TERNARY_DISABLED &&
+		!vacrelstats->skip_index_vacuum)
 		lazy_cleanup_all_indexes(Irel, vacrelstats, lps, nindexes);
 
 	/*
@@ -2049,6 +2053,17 @@ lazy_vacuum_pruned_items(Relation onerel, LVRelStats *vacrelstats,
 	if (index_cleanup == VACOPT_TERNARY_DISABLED)
 		skipping = true;
 
+	/*
+	 * Skip index vacuuming if the table's relfrozenxid/relminmxid is too
+	 * old so at risk of XID wraparound.  Once we decided to skip index
+	 * vacuuming, the decision never goes back to index vacuuming.  This saves
+	 * extra check_index_vacuum_xid_limit() calls and is less confusing for
+	 * users since we have ereport'ed that we decided not to do index
+	 * vacuuming.
+	 */
+	else if (vacrelstats->skip_index_vacuum)
+		skipping = true;
+
 	/*
 	 * Don't skip index and heap vacuuming if it's not only called once during
 	 * the entire vacuum operation.
@@ -2083,12 +2098,10 @@ lazy_vacuum_pruned_items(Relation onerel, LVRelStats *vacrelstats,
 			skipping = false;
 	}
 
-	if (!skipping)
+	if (!skipping && lazy_vacuum_all_indexes(onerel, Irel, vacrelstats, lps, nindexes,
+											 index_cleanup))
 	{
-		/* Okay, we're going to do index vacuuming */
-		lazy_vacuum_all_indexes(onerel, Irel, vacrelstats, lps, nindexes);
-
-		/* Remove tuples from heap */
+		/* All dead tuples in indexes are removed, so remove tuples from heap as well */
 		lazy_vacuum_heap(onerel, vacrelstats);
 	}
 	else
@@ -2133,14 +2146,29 @@ lazy_vacuum_pruned_items(Relation onerel, LVRelStats *vacrelstats,
  * rely on conflicts from heap pruning instead (i.e. a heap_page_prune() call
  * that took place earlier, usually though not always during the ongoing
  * VACUUM operation).
+ *
+ * Return true if we vacuum all indexes.
  */
-static void
+static bool
 lazy_vacuum_all_indexes(Relation onerel, Relation *Irel,
 						LVRelStats *vacrelstats, LVParallelState *lps,
-						int nindexes)
+						int nindexes, VacOptTernaryValue index_cleanup)
 {
+	int i;
+
 	Assert(!IsParallelWorker());
 	Assert(nindexes > 0);
+	Assert(index_cleanup == VACOPT_TERNARY_ENABLED);
+
+	/* Check if the table is at risk of XID wraparound */
+	if (check_index_vacuum_xid_limit(onerel))
+	{
+		vacrelstats->skip_index_vacuum = true;
+		return false;
+	}
+
+	/* Increase and report the number of index scans */
+	vacrelstats->num_index_scans++;
 
 	/* Report that we are now vacuuming indexes */
 	pgstat_progress_update_param(PROGRESS_VACUUM_PHASE,
@@ -2161,21 +2189,103 @@ lazy_vacuum_all_indexes(Relation onerel, Relation *Irel,
 		lps->lvshared->estimated_count = true;
 
 		lazy_parallel_vacuum_indexes(Irel, vacrelstats, lps, nindexes);
+
+		pgstat_progress_update_param(PROGRESS_VACUUM_NUM_INDEX_VACUUMS,
+									 vacrelstats->num_index_scans);
+
+		/*
+		 * In parallel vacuum, since we hand the indexes over to parallel vacuum
+		 * workers, always return true.
+		 */
+		return true;
 	}
-	else
+
+	/*
+	 * Vacuum the indexes one by one. If index_cleanup option is on, we check
+	 * if the table's relfrozenxid/relminmxid is too old after each index vacuuming.
+	 * If so, we stop index vacuuming and return false, telling the caller not to
+	 * delete LP_DEAD items.
+	 */
+	for (i = 0; i < nindexes; i++)
 	{
-		int			idx;
+		lazy_vacuum_index(Irel[i], &(vacrelstats->indstats[i]),
+						  vacrelstats->dead_tuples,
+						  vacrelstats->old_live_tuples, vacrelstats);
 
-		for (idx = 0; idx < nindexes; idx++)
-			lazy_vacuum_index(Irel[idx], &(vacrelstats->indstats[idx]),
-							  vacrelstats->dead_tuples,
-							  vacrelstats->old_live_tuples, vacrelstats);
+		if (check_index_vacuum_xid_limit(onerel))
+		{
+			/* Stop index vacuuming */
+			vacrelstats->skip_index_vacuum = true;
+			break;
+		}
 	}
 
-	/* Increase and report the number of index scans */
-	vacrelstats->num_index_scans++;
 	pgstat_progress_update_param(PROGRESS_VACUUM_NUM_INDEX_VACUUMS,
 								 vacrelstats->num_index_scans);
+
+	/* Vacuumed all indexes? */
+	return (i >= nindexes);
+}
+
+/*
+ * Return true if the table's relfrozenxid/relminmxid is older than the skip
+ * index vacuum age.
+ */
+static bool
+check_index_vacuum_xid_limit(Relation onerel)
+{
+	TransactionId	xid_skip_limit;
+	MultiXactId		multi_skip_limit;
+	int	skip_index_vacuum;
+	int effective_multixact_freeze_max_age;
+
+	/*
+	 * Determine the index skipping age to use. In any case not less than
+	 * autovacuum_freeze_max_age * 1.05, so that VACUUM always does an
+	 * aggressive scan.
+	 */
+	skip_index_vacuum = Max(vacuum_skip_index_age, autovacuum_freeze_max_age * 1.05);
+
+	xid_skip_limit = ReadNextTransactionId() - skip_index_vacuum;
+	if (!TransactionIdIsNormal(xid_skip_limit))
+		xid_skip_limit = FirstNormalTransactionId;
+
+	if (TransactionIdIsNormal(onerel->rd_rel->relfrozenxid) &&
+		TransactionIdPrecedes(onerel->rd_rel->relfrozenxid,
+							  xid_skip_limit))
+	{
+		/* The table's relfrozenxid is too old */
+		return true;
+	}
+
+	/*
+	 * Similar to above, determine the index skipping age to use for multixact.
+	 * In any case not less than autovacuum_multixact_freeze_max_age * 1.05.
+	 */
+	skip_index_vacuum = Max(vacuum_multixact_skip_index_age,
+							 autovacuum_multixact_freeze_max_age * 1.05);
+
+
+	/*
+	 * Compute the multixact age for which freezing is urgent.  This is
+	 * normally autovacuum_multixact_freeze_max_age, but may be less if we are
+	 * short of multixact member space.
+	 */
+	effective_multixact_freeze_max_age = MultiXactMemberFreezeThreshold();
+
+	multi_skip_limit = ReadNextMultiXactId() - skip_index_vacuum;
+	if (multi_skip_limit < FirstMultiXactId)
+		multi_skip_limit = FirstMultiXactId;
+
+	if (MultiXactIdIsValid(onerel->rd_rel->relminmxid) &&
+		MultiXactIdPrecedes(onerel->rd_rel->relminmxid,
+							multi_skip_limit))
+	{
+		/* The table's relminmxid is too old */
+		return true;
+	}
+
+	return false;
 }
 
 /*
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index c064352e23..f6256a65c8 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -62,6 +62,8 @@ int			vacuum_freeze_min_age;
 int			vacuum_freeze_table_age;
 int			vacuum_multixact_freeze_min_age;
 int			vacuum_multixact_freeze_table_age;
+int			vacuum_skip_index_age;
+int			vacuum_multixact_skip_index_age;
 
 
 /* A few variables that don't seem worth passing around as parameters */
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 3b36a31a47..7dc7e6f44b 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2624,6 +2624,26 @@ static struct config_int ConfigureNamesInt[] =
 		0, 0, 1000000,		/* see ComputeXidHorizons */
 		NULL, NULL, NULL
 	},
+	{
+		{"vacuum_skip_index_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Age at which VACUUM should skip index vacuuming."),
+			NULL
+		},
+		&vacuum_skip_index_age,
+		/* This upper-limit can be 1.05 of autovacuum_freeze_max_age */
+		1800000000, 0, 2100000000,
+		NULL, NULL, NULL
+	},
+	{
+		{"vacuum_multixact_skip_index_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Multixact age at which VACUUM should skip index vacuuming."),
+			NULL
+		},
+		&vacuum_multixact_skip_index_age,
+		/* This upper-limit can be 1.05 of autovacuum_multixact_freeze_max_age */
+		1800000000, 0, 2100000000,
+		NULL, NULL, NULL
+	},
 
 	/*
 	 * See also CheckRequiredParameterValues() if this parameter changes
@@ -3224,7 +3244,10 @@ static struct config_int ConfigureNamesInt[] =
 			NULL
 		},
 		&autovacuum_freeze_max_age,
-		/* see pg_resetwal if you change the upper-limit value */
+		/*
+		 * see pg_resetwal and vacuum_skip_index_age if you change the
+		 * upper-limit value.
+		 */
 		200000000, 100000, 2000000000,
 		NULL, NULL, NULL
 	},
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 86425965d0..30bc4d5f45 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -674,6 +674,8 @@
 #vacuum_freeze_table_age = 150000000
 #vacuum_multixact_freeze_min_age = 5000000
 #vacuum_multixact_freeze_table_age = 150000000
+#vacuum_skip_index_age = 1800000000
+#vacuum_multixact_skip_index_age = 1800000000
 #bytea_output = 'hex'			# hex, escape
 #xmlbinary = 'base64'
 #xmloption = 'content'
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index d029da5ac0..741437cdaf 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -235,6 +235,8 @@ extern int	vacuum_freeze_min_age;
 extern int	vacuum_freeze_table_age;
 extern int	vacuum_multixact_freeze_min_age;
 extern int	vacuum_multixact_freeze_table_age;
+extern int	vacuum_skip_index_age;
+extern int	vacuum_multixact_skip_index_age;
 
 /* Variables for cost-based parallel vacuum */
 extern pg_atomic_uint32 *VacuumSharedCostBalance;
-- 
2.27.0