v2-0001-Introduce-autovacuum-vacuum-strategy.patch

From 3763c4813593af7267c37203a09e3d04037cf304 Mon Sep 17 00:00:00 2001
From: Junwang Zhao <zhjwpku@gmail.com>
Date: Wed, 30 Apr 2025 10:20:16 +0000
Subject: [PATCH v2 1/2] Introduce autovacuum vacuum strategy

Add a new configuration option `autovacuum_vacuum_strategy` to
control the order in which tables are vacuumed by the autovacuum
process. Two strategies are supported for now:

1. Sequential: Tables are vacuumed in the order they are collected.
2. Random: The list of tables is rotated around a randomly chosen
   pivot before vacuuming to avoid always starting with the same
   table, which prevents vacuuming starvation for some tables.

The default strategy is `sequential`.

Signed-off-by: Junwang Zhao <zhjwpku@gmail.com>
---
 src/backend/postmaster/autovacuum.c           | 33 +++++++++++++++++++
 src/backend/utils/misc/guc_tables.c           | 10 ++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/postmaster/autovacuum.h           |  7 ++++
 src/include/utils/guc.h                       |  1 +
 5 files changed, 52 insertions(+)

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 16756152b71..2452d893533 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -79,6 +79,7 @@
 #include "catalog/pg_namespace.h"
 #include "commands/dbcommands.h"
 #include "commands/vacuum.h"
+#include "common/pg_prng.h"
 #include "common/int.h"
 #include "lib/ilist.h"
 #include "libpq/pqsignal.h"
@@ -132,9 +133,16 @@ int			autovacuum_multixact_freeze_max_age;
 
 double		autovacuum_vac_cost_delay;
 int			autovacuum_vac_cost_limit;
+int			autovacuum_vac_strategy;
 
 int			Log_autovacuum_min_duration = 600000;
 
+const struct config_enum_entry autovacuum_vac_strategy_options[] = {
+	{"sequential", AUTOVACUUM_VAC_STRATEGY_SEQUENTIAL, false},
+	{"random", AUTOVACUUM_VAC_STRATEGY_RANDOM, false},
+	{NULL, 0, false}
+};
+
 /* the minimum allowed time between two awakenings of the launcher */
 #define MIN_AUTOVAC_SLEEPTIME 100.0 /* milliseconds */
 #define MAX_AUTOVAC_SLEEPTIME 300	/* seconds */
@@ -2267,6 +2275,30 @@ do_autovacuum(void)
 										  "Autovacuum Portal",
 										  ALLOCSET_DEFAULT_SIZES);
 
+	/*
+	 * Randomly rotate the list of tables to vacuum.  This is to avoid always
+	 * vacuuming the same table first, which could lead to spinning on the
+	 * same table or vacuuming starvation.
+	 */
+	if (list_length(table_oids) > 1 && autovacuum_vac_strategy == AUTOVACUUM_VAC_STRATEGY_RANDOM)
+	{
+
+		int			rand = 0;
+		static pg_prng_state prng_state;
+		List	   *tmp_oids = NIL;
+
+		pg_prng_seed(&prng_state, (uint64) (getpid() ^ time(NULL)));
+		rand = (int) pg_prng_uint64_range(&prng_state, 0,
+										  list_length(table_oids) - 1);
+		if (rand != 0)
+		{
+			tmp_oids = list_copy_tail(table_oids, rand);
+			table_oids = list_copy_head(table_oids,
+										list_length(table_oids) - rand);
+			table_oids = list_concat(table_oids, tmp_oids);
+		}
+	}
+
 	/*
 	 * Perform operations on collected tables.
 	 */
@@ -3282,6 +3314,7 @@ AutoVacuumRequestWork(AutoVacuumWorkItemType type, Oid relationId,
 		workitem->avw_used = true;
 		workitem->avw_active = false;
 		workitem->avw_type = type;
+
 		workitem->avw_database = MyDatabaseId;
 		workitem->avw_relation = relationId;
 		workitem->avw_blockNumber = blkno;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 2f8cbd86759..c928106fd50 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -5418,6 +5418,16 @@ struct config_enum ConfigureNamesEnum[] =
 		NULL, assign_io_method, NULL
 	},
 
+	{
+		{"autovacuum_vacuum_strategy", PGC_SIGHUP, VACUUM_AUTOVACUUM,
+			gettext_noop("Vacuum strategy for autovacuum."),
+			NULL
+		},
+		&autovacuum_vac_strategy,
+		AUTOVACUUM_VAC_STRATEGY_SEQUENTIAL, autovacuum_vac_strategy_options,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, 0, NULL, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 34826d01380..5c44944008f 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -713,6 +713,7 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 #autovacuum_vacuum_cost_limit = -1	# default vacuum cost limit for
 					# autovacuum, -1 means use
 					# vacuum_cost_limit
+#autovacuum_vacuum_strategy = sequential
 
 # - Cost-Based Vacuum Delay -
 
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index e8135f41a1c..218ea4ee1c8 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -25,6 +25,12 @@ typedef enum
 	AVW_BRINSummarizeRange,
 } AutoVacuumWorkItemType;
 
+/* Autovacuum vacuum strategies */
+enum AutovacuumVacStrategy
+{
+	AUTOVACUUM_VAC_STRATEGY_SEQUENTIAL = 0,
+	AUTOVACUUM_VAC_STRATEGY_RANDOM,
+};
 
 /* GUC variables */
 extern PGDLLIMPORT bool autovacuum_start_daemon;
@@ -43,6 +49,7 @@ extern PGDLLIMPORT int autovacuum_freeze_max_age;
 extern PGDLLIMPORT int autovacuum_multixact_freeze_max_age;
 extern PGDLLIMPORT double autovacuum_vac_cost_delay;
 extern PGDLLIMPORT int autovacuum_vac_cost_limit;
+extern PGDLLIMPORT int autovacuum_vac_strategy;
 
 /* autovacuum launcher PID, only valid when worker is shutting down */
 extern PGDLLIMPORT int AutovacuumLauncherPid;
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index f619100467d..2f5c5363b03 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -322,6 +322,7 @@ extern PGDLLIMPORT const struct config_enum_entry io_method_options[];
 extern PGDLLIMPORT const struct config_enum_entry recovery_target_action_options[];
 extern PGDLLIMPORT const struct config_enum_entry wal_level_options[];
 extern PGDLLIMPORT const struct config_enum_entry wal_sync_method_options[];
+extern PGDLLIMPORT const struct config_enum_entry autovacuum_vac_strategy_options[];
 
 /*
  * Functions exported by guc.c
-- 
2.39.5