Date: Fri, 29 Nov 2019 19:01:39 +0100
From: David Fetter <david@fetter.org>
To: PostgreSQL Development <pgsql-hackers@postgresql.org>
Cc: Christophe Pettus <xof@thebuild.com>
Subject: Make autovacuum sort tables in descending order of xid_age
Message-ID: <20191129180138.GD8731@fetter.org>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="fdj2RfSjLxBAspz7"
Content-Disposition: inline
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk


--fdj2RfSjLxBAspz7
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Folks,

Per a suggestion Christophe made, please find attached a patch to
$Subject:

Apart from carefully fudging with pg_resetwal, and short of running in
production for a few weeks, what would be some good ways to test this?

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--fdj2RfSjLxBAspz7
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v1-0001-Autovacuum-tables-in-descending-order-of-age.patch"

From 7718bb3ed310ada318d31d02854a7793a04e9512 Mon Sep 17 00:00:00 2001
From: David Fetter <david@fetter.org>
Date: Wed, 27 Nov 2019 23:50:48 -0800
Subject: [PATCH v1] Autovacuum tables in descending order of age
To: hackers
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------2.23.0"

This is a multi-part message in MIME format.
--------------2.23.0
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit


diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index c1dd8168ca..54b9ff2f80 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -288,6 +288,15 @@ typedef struct
 	AutoVacuumWorkItem av_workItems[NUM_WORKITEMS];
 } AutoVacuumShmemStruct;
 
+/*
+ * Struct for deciding which tables to vacuum first
+ */
+typedef struct
+{
+	uint32	relfrozenxid_age;
+	Oid		table_oid;
+} RelFrozenXidAge;
+
 static AutoVacuumShmemStruct *AutoVacuumShmem;
 
 /*
@@ -319,6 +328,7 @@ static void rebuild_database_list(Oid newdb);
 static int	db_comparator(const void *a, const void *b);
 static void autovac_balance_cost(void);
 
+static int	rfxa_comparator(const void *a, const void *b);
 static void do_autovacuum(void);
 static void FreeWorkerInfo(int code, Datum arg);
 
@@ -1936,6 +1946,9 @@ do_autovacuum(void)
 	HeapTuple	tuple;
 	TableScanDesc relScan;
 	Form_pg_database dbForm;
+	RelFrozenXidAge	*table_ages;
+	int			table_ages_size = 1024;
+	int			table_ages_count = 0;
 	List	   *table_oids = NIL;
 	List	   *orphan_oids = NIL;
 	HASHCTL		ctl;
@@ -1951,6 +1964,7 @@ do_autovacuum(void)
 	bool		found_concurrent_worker = false;
 	int			i;
 
+	table_ages = (RelFrozenXidAge *)palloc(table_ages_size * sizeof(RelFrozenXidAge));
 	/*
 	 * StartTransactionCommand and CommitTransactionCommand will automatically
 	 * switch to other contexts.  We need this one to keep the list of
@@ -2102,9 +2116,22 @@ do_autovacuum(void)
 								  effective_multixact_freeze_max_age,
 								  &dovacuum, &doanalyze, &wraparound);
 
-		/* Relations that need work are added to table_oids */
+		/* Relations that need work are added to table_ages */
 		if (dovacuum || doanalyze)
-			table_oids = lappend_oid(table_oids, relid);
+		{
+			RelFrozenXidAge	rfxa;
+			rfxa.relfrozenxid_age = DirectFunctionCall1(xid_age,
+														classForm->relfrozenxid);
+			rfxa.table_oid = relid;
+			if (table_ages_count == table_ages_size)
+			{
+				table_ages_size *= 2;
+				table_ages = (RelFrozenXidAge *)repalloc(table_ages, table_ages_size);
+			}
+			table_ages[table_ages_count] = rfxa;
+			table_ages_count++;
+		}
+
 
 		/*
 		 * Remember TOAST associations for the second pass.  Note: we must do
@@ -2187,7 +2214,20 @@ do_autovacuum(void)
 
 		/* ignore analyze for toast tables */
 		if (dovacuum)
-			table_oids = lappend_oid(table_oids, relid);
+		{
+			RelFrozenXidAge	rfxa;
+
+			rfxa.relfrozenxid_age = DirectFunctionCall1(xid_age,
+														classForm->relfrozenxid);
+			rfxa.table_oid = relid;
+
+			if (table_ages_count == table_ages_size)
+			{
+				table_ages_size *= 2;
+				table_ages = (RelFrozenXidAge *)repalloc(table_ages, table_ages_size);
+			}
+			table_ages[table_ages_count++] = rfxa;
+		}
 	}
 
 	table_endscan(relScan);
@@ -2295,6 +2335,12 @@ do_autovacuum(void)
 										  "Autovacuum Portal",
 										  ALLOCSET_DEFAULT_SIZES);
 
+
+	qsort(table_ages, table_ages_count, sizeof(table_ages), rfxa_comparator);
+	for (int i = 0; i < table_ages_count; i++)
+	{
+		table_oids = lappend_oid(table_oids, table_ages[i].table_oid);
+	}
 	/*
 	 * Perform operations on collected tables.
 	 */
@@ -2608,6 +2654,22 @@ deleted:
 	CommitTransactionCommand();
 }
 
+/*
+ * qsort comparator for RelFrozenXidAges
+ *
+ * N.B. This comparator sorts in descending order.
+ */
+static int
+rfxa_comparator(const void *a, const void *b)
+{
+	const RelFrozenXidAge *pa = a;
+	const RelFrozenXidAge *pb = b;
+	if (pa->relfrozenxid_age == pb->relfrozenxid_age)
+		return 0;
+	else
+		return (pa->relfrozenxid_age > pb->relfrozenxid_age) ? 1 : -1;
+}
+
 /*
  * Execute a previously registered work item.
  */

--------------2.23.0--


--fdj2RfSjLxBAspz7--