v12-0002-Add-eager-and-lazy-freezing-strategies-to-VACUUM.patch

From c9ba6835f4446716040d2579006180940e1fa61a Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <pg@bowt.ie>
Date: Thu, 24 Nov 2022 18:20:36 -0800
Subject: [PATCH v12 2/4] Add eager and lazy freezing strategies to VACUUM.

Avoid large build-ups of all-visible pages by making non-aggressive
VACUUMs freeze pages proactively for VACUUMs/tables where eager
vacuuming is deemed appropriate.  VACUUM determines its freezing
strategy based on the value of the new vacuum_freeze_strategy_threshold
GUC (or reloption) in most cases: Tables that exceeds the size threshold
use the eager freezing strategy.  Otherwise VACUUM uses the lazy
freezing strategy,  which is essentially the same approach that VACUUM
has always taken to freezing (though not quite, due to the influence of
page level freezing following recent work).

When the eager strategy is in use, lazy_scan_prune will trigger freezing
a page's tuples at the point that it notices that it will at least
become all-visible -- it can be made all-frozen instead.  We still
respect FreezeLimit, though: the presence of any XID < FreezeLimit also
triggers page-level freezing (just as it would with the lazy strategy).
Eager freezing is generally only applied when vacuuming larger tables,
where freezing most individual heap pages at the first opportunity (in
the first VACUUM operation where they can definitely be set all-visible)
will improve performance stability.

A later commit will add vmsnap scanning strategies, which are designed
to work in tandem with the freezing strategies from this commit.

Author: Peter Geoghegan <pg@bowt.ie>
Reviewed-By: Jeff Davis <pgsql@j-davis.com>
Reviewed-By: Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/CAH2-WzkFok_6EAHuK39GaW4FjEFQsY=3J0AAd6FXk93u-Xq3Fg@mail.gmail.com
---
 src/include/commands/vacuum.h                 | 10 +++++
 src/include/utils/rel.h                       |  1 +
 src/backend/access/common/reloptions.c        | 11 +++++
 src/backend/access/heap/heapam.c              |  1 +
 src/backend/access/heap/vacuumlazy.c          | 43 ++++++++++++++++++-
 src/backend/commands/vacuum.c                 | 17 +++++++-
 src/backend/postmaster/autovacuum.c           | 10 +++++
 src/backend/utils/misc/guc_tables.c           | 11 +++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 doc/src/sgml/config.sgml                      | 19 +++++++-
 doc/src/sgml/ref/create_table.sgml            | 14 ++++++
 doc/src/sgml/ref/vacuum.sgml                  | 16 +++----
 12 files changed, 143 insertions(+), 11 deletions(-)

diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 2f274f2be..b39178d5b 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -220,6 +220,9 @@ typedef struct VacuumParams
 											 * use default */
 	int			multixact_freeze_table_age; /* multixact age at which to scan
 											 * whole table */
+	int			freeze_strategy_threshold;	/* threshold to use eager
+											 * freezing, in total heap blocks,
+											 * -1 to use default */
 	bool		is_wraparound;	/* force a for-wraparound vacuum */
 	int			log_min_duration;	/* minimum execution threshold in ms at
 									 * which autovacuum is logged, -1 to use
@@ -272,6 +275,12 @@ struct VacuumCutoffs
 	 */
 	TransactionId FreezeLimit;
 	MultiXactId MultiXactCutoff;
+
+	/*
+	 * Threshold cutoff point (expressed in # of physical heap rel blocks in
+	 * rel's main fork) that triggers VACUUM's eager freezing strategy
+	 */
+	BlockNumber freeze_strategy_threshold;
 };
 
 /*
@@ -295,6 +304,7 @@ extern PGDLLIMPORT int vacuum_freeze_min_age;
 extern PGDLLIMPORT int vacuum_freeze_table_age;
 extern PGDLLIMPORT int vacuum_multixact_freeze_min_age;
 extern PGDLLIMPORT int vacuum_multixact_freeze_table_age;
+extern PGDLLIMPORT int vacuum_freeze_strategy_threshold;
 extern PGDLLIMPORT int vacuum_failsafe_age;
 extern PGDLLIMPORT int vacuum_multixact_failsafe_age;
 
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index f383a2fca..e195b63d7 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -308,6 +308,7 @@ typedef struct AutoVacOpts
 	int			vacuum_ins_threshold;
 	int			analyze_threshold;
 	int			vacuum_cost_limit;
+	int			freeze_strategy_threshold;
 	int			freeze_min_age;
 	int			freeze_max_age;
 	int			freeze_table_age;
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 75b734489..4b680501c 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -260,6 +260,15 @@ static relopt_int intRelOpts[] =
 		},
 		-1, 1, 10000
 	},
+	{
+		{
+			"autovacuum_freeze_strategy_threshold",
+			"Table size at which VACUUM freezes using eager strategy.",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		-1, 0, INT_MAX
+	},
 	{
 		{
 			"autovacuum_freeze_min_age",
@@ -1851,6 +1860,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, analyze_threshold)},
 		{"autovacuum_vacuum_cost_limit", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_cost_limit)},
+		{"autovacuum_freeze_strategy_threshold", RELOPT_TYPE_INT,
+		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, freeze_strategy_threshold)},
 		{"autovacuum_freeze_min_age", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, freeze_min_age)},
 		{"autovacuum_freeze_max_age", RELOPT_TYPE_INT,
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 71dfe5933..4651895f8 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -6876,6 +6876,7 @@ heap_freeze_tuple(HeapTupleHeader tuple,
 	cutoffs.OldestMxact = MultiXactCutoff;
 	cutoffs.FreezeLimit = FreezeLimit;
 	cutoffs.MultiXactCutoff = MultiXactCutoff;
+	cutoffs.freeze_strategy_threshold = 0;
 
 	pagefrz.freeze_required = true;
 	pagefrz.FreezePageRelfrozenXid = FreezeLimit;
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 18192fed5..8021f7fd5 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -153,6 +153,8 @@ typedef struct LVRelState
 	bool		aggressive;
 	/* Use visibility map to skip? (disabled by DISABLE_PAGE_SKIPPING) */
 	bool		skipwithvm;
+	/* Eagerly freeze all tuples on pages about to be set all-visible? */
+	bool		eager_freeze_strategy;
 	/* Wraparound failsafe has been triggered? */
 	bool		failsafe_active;
 	/* Consider index vacuuming bypass optimization? */
@@ -242,6 +244,7 @@ typedef struct LVSavedErrInfo
 
 /* non-export function prototypes */
 static void lazy_scan_heap(LVRelState *vacrel);
+static void lazy_scan_strategy(LVRelState *vacrel);
 static BlockNumber lazy_scan_skip(LVRelState *vacrel, Buffer *vmbuffer,
 								  BlockNumber next_block,
 								  bool *next_unskippable_allvis,
@@ -470,6 +473,10 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 
 	vacrel->skipwithvm = skipwithvm;
 
+	/*
+	 * Now determine VACUUM's freezing strategy.
+	 */
+	lazy_scan_strategy(vacrel);
 	if (verbose)
 	{
 		if (vacrel->aggressive)
@@ -1249,6 +1256,38 @@ lazy_scan_heap(LVRelState *vacrel)
 		lazy_cleanup_all_indexes(vacrel);
 }
 
+/*
+ *	lazy_scan_strategy() -- Determine freezing strategy.
+ *
+ * Our lazy freezing strategy is useful when putting off the work of freezing
+ * totally avoids freezing that turns out to have been wasted effort later on.
+ * Our eager freezing strategy is useful with larger tables that experience
+ * continual growth, where freezing pages proactively is needed just to avoid
+ * falling behind on freezing (eagerness is also likely to be cheaper in the
+ * short/medium term for such tables, but the long term picture matters most).
+ */
+static void
+lazy_scan_strategy(LVRelState *vacrel)
+{
+	BlockNumber rel_pages = vacrel->rel_pages;
+
+	/*
+	 * Decide freezing strategy.
+	 *
+	 * The eager freezing strategy is used when the threshold controlled by
+	 * freeze_strategy_threshold GUC/reloption exceeds rel_pages.
+	 *
+	 * Also freeze eagerly with an unlogged or temp table, where the total
+	 * cost of freezing each page is just the cycles needed to prepare a set
+	 * of freeze plans.  Executing the freeze plans adds very little cost.
+	 * Dirtying extra pages isn't a concern, either; VACUUM will definitely
+	 * set PD_ALL_VISIBLE on affected pages, regardless of freezing strategy.
+	 */
+	vacrel->eager_freeze_strategy =
+		(rel_pages >= vacrel->cutoffs.freeze_strategy_threshold ||
+		 !RelationIsPermanent(vacrel->rel));
+}
+
 /*
  *	lazy_scan_skip() -- set up range of skippable blocks using visibility map.
  *
@@ -1770,10 +1809,12 @@ retry:
 	 * one XID/MXID from before FreezeLimit/MultiXactCutoff is present.  Also
 	 * freeze when pruning generated an FPI, if doing so means that we set the
 	 * page all-frozen afterwards (might not happen until second heap pass).
+	 * When ongoing VACUUM opted to use the eager freezing strategy, we freeze
+	 * any page that will thereby become all-frozen in the visibility map.
 	 */
 	if (pagefrz.freeze_required || tuples_frozen == 0 ||
 		(prunestate->all_visible && prunestate->all_frozen &&
-		 fpi_before != pgWalUsage.wal_fpi))
+		 (fpi_before != pgWalUsage.wal_fpi || vacrel->eager_freeze_strategy)))
 	{
 		/*
 		 * We're freezing the page.  Our final NewRelfrozenXid doesn't need to
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index ba965b8c7..7c68bd8ff 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -67,6 +67,7 @@ int			vacuum_freeze_min_age;
 int			vacuum_freeze_table_age;
 int			vacuum_multixact_freeze_min_age;
 int			vacuum_multixact_freeze_table_age;
+int			vacuum_freeze_strategy_threshold;
 int			vacuum_failsafe_age;
 int			vacuum_multixact_failsafe_age;
 
@@ -263,6 +264,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		params.multixact_freeze_table_age = -1;
 	}
 
+	/* Determine freezing strategy later on using GUC or reloption */
+	params.freeze_strategy_threshold = -1;
+
 	/* user-invoked vacuum is never "for wraparound" */
 	params.is_wraparound = false;
 
@@ -926,7 +930,8 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
 				multixact_freeze_min_age,
 				freeze_table_age,
 				multixact_freeze_table_age,
-				effective_multixact_freeze_max_age;
+				effective_multixact_freeze_max_age,
+				freeze_strategy_threshold;
 	TransactionId nextXID,
 				safeOldestXmin,
 				aggressiveXIDCutoff;
@@ -939,6 +944,7 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
 	multixact_freeze_min_age = params->multixact_freeze_min_age;
 	freeze_table_age = params->freeze_table_age;
 	multixact_freeze_table_age = params->multixact_freeze_table_age;
+	freeze_strategy_threshold = params->freeze_strategy_threshold;
 
 	/* Set pg_class fields in cutoffs */
 	cutoffs->relfrozenxid = rel->rd_rel->relfrozenxid;
@@ -1053,6 +1059,15 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
 	if (MultiXactIdPrecedes(cutoffs->OldestMxact, cutoffs->MultiXactCutoff))
 		cutoffs->MultiXactCutoff = cutoffs->OldestMxact;
 
+	/*
+	 * Determine the freeze_strategy_threshold to use: as specified by the
+	 * caller, or vacuum_freeze_strategy_threshold
+	 */
+	if (freeze_strategy_threshold < 0)
+		freeze_strategy_threshold = vacuum_freeze_strategy_threshold;
+	Assert(freeze_strategy_threshold >= 0);
+	cutoffs->freeze_strategy_threshold = freeze_strategy_threshold;
+
 	/*
 	 * Finally, figure out if caller needs to do an aggressive VACUUM or not.
 	 *
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0746d8022..23e316e59 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -151,6 +151,7 @@ static int	default_freeze_min_age;
 static int	default_freeze_table_age;
 static int	default_multixact_freeze_min_age;
 static int	default_multixact_freeze_table_age;
+static int	default_freeze_strategy_threshold;
 
 /* Memory context for long-lived data */
 static MemoryContext AutovacMemCxt;
@@ -2010,6 +2011,7 @@ do_autovacuum(void)
 		default_freeze_table_age = 0;
 		default_multixact_freeze_min_age = 0;
 		default_multixact_freeze_table_age = 0;
+		default_freeze_strategy_threshold = 0;
 	}
 	else
 	{
@@ -2017,6 +2019,7 @@ do_autovacuum(void)
 		default_freeze_table_age = vacuum_freeze_table_age;
 		default_multixact_freeze_min_age = vacuum_multixact_freeze_min_age;
 		default_multixact_freeze_table_age = vacuum_multixact_freeze_table_age;
+		default_freeze_strategy_threshold = vacuum_freeze_strategy_threshold;
 	}
 
 	ReleaseSysCache(tuple);
@@ -2801,6 +2804,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		int			freeze_table_age;
 		int			multixact_freeze_min_age;
 		int			multixact_freeze_table_age;
+		int			freeze_strategy_threshold;
 		int			vac_cost_limit;
 		double		vac_cost_delay;
 		int			log_min_duration;
@@ -2850,6 +2854,11 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 			? avopts->multixact_freeze_table_age
 			: default_multixact_freeze_table_age;
 
+		freeze_strategy_threshold = (avopts &&
+									 avopts->freeze_strategy_threshold >= 0)
+			? avopts->freeze_strategy_threshold
+			: default_freeze_strategy_threshold;
+
 		tab = palloc(sizeof(autovac_table));
 		tab->at_relid = relid;
 		tab->at_sharedrel = classForm->relisshared;
@@ -2872,6 +2881,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab->at_params.freeze_table_age = freeze_table_age;
 		tab->at_params.multixact_freeze_min_age = multixact_freeze_min_age;
 		tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age;
+		tab->at_params.freeze_strategy_threshold = freeze_strategy_threshold;
 		tab->at_params.is_wraparound = wraparound;
 		tab->at_params.log_min_duration = log_min_duration;
 		tab->at_vacuum_cost_limit = vac_cost_limit;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 436afe1d2..a009017bd 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2518,6 +2518,17 @@ struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"vacuum_freeze_strategy_threshold", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Table size at which VACUUM freezes using eager strategy."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&vacuum_freeze_strategy_threshold,
+		(UINT64CONST(4) * 1024 * 1024 * 1024) / BLCKSZ, 0, INT_MAX,
+		NULL, NULL, NULL
+	},
+
 	{
 		{"vacuum_defer_cleanup_age", PGC_SIGHUP, REPLICATION_PRIMARY,
 			gettext_noop("Number of transactions by which VACUUM and HOT cleanup should be deferred, if any."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 5afdeb04d..447645b73 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -693,6 +693,7 @@
 #idle_in_transaction_session_timeout = 0	# in milliseconds, 0 is disabled
 #idle_session_timeout = 0		# in milliseconds, 0 is disabled
 #vacuum_freeze_table_age = 150000000
+#vacuum_freeze_strategy_threshold = 4GB
 #vacuum_freeze_min_age = 50000000
 #vacuum_failsafe_age = 1600000000
 #vacuum_multixact_freeze_table_age = 150000000
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 44e15b5fb..167c6570e 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9161,6 +9161,21 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-vacuum-freeze-strategy-threshold" xreflabel="vacuum_freeze_strategy_threshold">
+      <term><varname>vacuum_freeze_strategy_threshold</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>vacuum_freeze_strategy_threshold</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the cutoff size (in pages) that <command>VACUUM</command>
+        should use to decide whether to apply its eager freezing strategy.
+        The default is 4 gigabytes (<literal>4GB</literal>).
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
       <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)
       <indexterm>
@@ -9196,7 +9211,9 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
        <para>
         Specifies the cutoff age (in transactions) that
         <command>VACUUM</command> should use to decide whether to
-        trigger freezing of pages that have an older XID.
+        trigger freezing of pages that have an older XID.  When VACUUM
+        uses its eager freezing strategy, freezing a page can also be
+        triggered when the page contains only all-visible tuples.
         The default is 50 million transactions.  Although
         users can set this value anywhere from zero to one billion,
         <command>VACUUM</command> will silently limit the effective value to half
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index c98223b2a..eabbf9e65 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1682,6 +1682,20 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-autovacuum-freeze-strategy-threshold" xreflabel="autovacuum_freeze_strategy_threshold">
+    <term><literal>autovacuum_freeze_strategy_threshold</literal>, <literal>toast.autovacuum_freeze_strategy_threshold</literal> (<type>integer</type>)
+    <indexterm>
+     <primary><varname>autovacuum_freeze_strategy_threshold</varname> storage parameter</primary>
+    </indexterm>
+    </term>
+    <listitem>
+     <para>
+      Per-table value for <xref linkend="guc-vacuum-freeze-strategy-threshold"/>
+      parameter.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="reloption-autovacuum-freeze-min-age" xreflabel="autovacuum_freeze_min_age">
     <term><literal>autovacuum_freeze_min_age</literal>, <literal>toast.autovacuum_freeze_min_age</literal> (<type>integer</type>)
     <indexterm>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index e14ead882..79595b1cb 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -119,14 +119,14 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     <term><literal>FREEZE</literal></term>
     <listitem>
      <para>
-      Selects aggressive <quote>freezing</quote> of tuples.
-      Specifying <literal>FREEZE</literal> is equivalent to performing
-      <command>VACUUM</command> with the
-      <xref linkend="guc-vacuum-freeze-min-age"/> and
-      <xref linkend="guc-vacuum-freeze-table-age"/> parameters
-      set to zero.  Aggressive freezing is always performed when the
-      table is rewritten, so this option is redundant when <literal>FULL</literal>
-      is specified.
+      Selects eager <quote>freezing</quote> of tuples.  Specifying
+      <literal>FREEZE</literal> is equivalent to performing
+      <command>VACUUM</command> with the <xref
+       linkend="guc-vacuum-freeze-strategy-threshold"/> and <xref
+       linkend="guc-vacuum-freeze-table-age"/> parameters set
+      to zero.  Eager freezing is always performed when the table is
+      rewritten, so this option is redundant when
+      <literal>FULL</literal> is specified.
      </para>
     </listitem>
    </varlistentry>
-- 
2.38.1