v5-0002-Trigger-more-frequent-autovacuums-with-relallfroz.patch

From c27e370df14f6f20d21a76c9563d6f00019949e1 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Thu, 16 Jan 2025 16:31:55 -0500
Subject: [PATCH v5 2/2] Trigger more frequent autovacuums with relallfrozen

Calculate the insert threshold for triggering an autovacuum of a
relation based on the number of unfrozen pages. By only considering the
"active" (unfrozen) portion of the table when calculating how many
tuples to add to the insert threshold, we can trigger more frequent
vacuums of insert-heavy tables and increase the chances of vacuuming
those pages when they still reside in shared buffers.

Reviewed-by: Greg Sabino Mullane
---
 doc/src/sgml/config.sgml                      | 16 +++++-----
 src/backend/postmaster/autovacuum.c           | 32 +++++++++++++++++--
 src/backend/utils/misc/postgresql.conf.sample |  4 +--
 3 files changed, 39 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index a8866292d46..84f2b44ec97 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8651,14 +8651,14 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
        </term>
        <listitem>
         <para>
-         Specifies a fraction of the table size to add to
-         <varname>autovacuum_vacuum_insert_threshold</varname>
-         when deciding whether to trigger a <command>VACUUM</command>.
-         The default is <literal>0.2</literal> (20% of table size).
-         This parameter can only be set in the <filename>postgresql.conf</filename>
-         file or on the server command line;
-         but the setting can be overridden for individual tables by
-         changing table storage parameters.
+        Specifies a fraction of the active (unfrozen) table size to add to
+        <varname>autovacuum_vacuum_insert_threshold</varname>
+        when deciding whether to trigger a <command>VACUUM</command>.
+        The default is 0.2 (20% of active table size).
+        This parameter can only be set in the <filename>postgresql.conf</filename>
+        file or on the server command line;
+        but the setting can be overridden for individual tables by
+        changing table storage parameters.
         </para>
        </listitem>
       </varlistentry>
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0ab921a169b..2d4a7778561 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2929,7 +2929,6 @@ relation_needs_vacanalyze(Oid relid,
 {
 	bool		force_vacuum;
 	bool		av_enabled;
-	float4		reltuples;		/* pg_class.reltuples */
 
 	/* constants from reloptions or GUC variables */
 	int			vac_base_thresh,
@@ -3037,7 +3036,12 @@ relation_needs_vacanalyze(Oid relid,
 	 */
 	if (PointerIsValid(tabentry) && AutoVacuumingActive())
 	{
-		reltuples = classForm->reltuples;
+		float4		pcnt_unfrozen = 1;
+		float4		reltuples = classForm->reltuples;
+		int32		relpages = classForm->relpages;
+		int32		relallfrozen = classForm->relallfrozen;
+		int32		relallvisible = classForm->relallvisible;
+
 		vactuples = tabentry->dead_tuples;
 		instuples = tabentry->ins_since_vacuum;
 		anltuples = tabentry->mod_since_analyze;
@@ -3046,8 +3050,30 @@ relation_needs_vacanalyze(Oid relid,
 		if (reltuples < 0)
 			reltuples = 0;
 
+		/*
+		 * If the table has been vacuumed and we have reliable data for
+		 * relallfrozen and relallvisible, calculate the unfrozen percentage
+		 * of the table to modify insert scale factor. This helps us decide
+		 * whether or not to vacuum an insert-heavy table based on the number
+		 * of inserts to the "active" part of the table.
+		 *
+		 * If relallfrozen is -1, that means relallvisible was updated
+		 * manually and we can't rely on relallfrozen.
+		 */
+		if (relpages > 0 && reltuples > 0 && relallfrozen > -1)
+		{
+			if (relallvisible > relpages)
+				relallvisible = relpages;
+
+			if (relallfrozen > relallvisible)
+				relallfrozen = relallvisible;
+
+			pcnt_unfrozen = 1 - ((float4) relallfrozen / relpages);
+		}
+
 		vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
-		vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
+		vacinsthresh = (float4) vac_ins_base_thresh +
+			vac_ins_scale_factor * reltuples * pcnt_unfrozen;
 		anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
 
 		/*
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 079efa1baa7..1b10f53c659 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -667,8 +667,8 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 #autovacuum_analyze_threshold = 50	# min number of row updates before
 					# analyze
 #autovacuum_vacuum_scale_factor = 0.2	# fraction of table size before vacuum
-#autovacuum_vacuum_insert_scale_factor = 0.2	# fraction of inserts over table
-						# size before insert vacuum
+#autovacuum_vacuum_insert_scale_factor = 0.2	# fraction of inserts over active
+						# table size before insert vacuum
 #autovacuum_analyze_scale_factor = 0.1	# fraction of table size before analyze
 #autovacuum_freeze_max_age = 200000000	# maximum XID age before forced vacuum
 					# (change requires restart)
-- 
2.45.2