Add options to control whether VACUUM runs vac_update_datfrozenxid.

VACUUM normally ends by running vac_update_datfrozenxid(), which
requires a scan of pg_class.  Therefore, if one attempts to vacuum a
database one table at a time --- as vacuumdb has done since v12 ---
we will spend O(N^2) time in vac_update_datfrozenxid().  That causes
serious performance problems in databases with tens of thousands of
tables, and indeed the effect is measurable with only a few hundred.
To add insult to injury, only one process can run
vac_update_datfrozenxid at the same time per DB, so this behavior
largely defeats vacuumdb's -j option.

Hence, invent options SKIP_DATABASE_STATS and ONLY_DATABASE_STATS
to allow applications to postpone vac_update_datfrozenxid() until the
end of a series of VACUUM requests, and teach vacuumdb to use them.

Per bug #17717 from Gunnar L.  Sadly, this answer doesn't seem
like something we'd consider back-patching, so the performance
problem will remain in v12-v15.

Tom Lane and Nathan Bossart

Discussion: https://postgr.es/m/17717-6c50eb1c7d23a886@postgresql.org

2 files changed, 45 insertions, 12 deletions

diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index cd356b11c57..3cfbaaec0d4 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -22,15 +22,15 @@ $node->issues_sql_like(
 	'SQL VACUUM run');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-f', 'postgres' ],
-	qr/statement: VACUUM \(FULL\).*;/,
+	qr/statement: VACUUM \(SKIP_DATABASE_STATS, FULL\).*;/,
 	'vacuumdb -f');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-F', 'postgres' ],
-	qr/statement: VACUUM \(FREEZE\).*;/,
+	qr/statement: VACUUM \(SKIP_DATABASE_STATS, FREEZE\).*;/,
 	'vacuumdb -F');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-zj2', 'postgres' ],
-	qr/statement: VACUUM \(ANALYZE\).*;/,
+	qr/statement: VACUUM \(SKIP_DATABASE_STATS, ANALYZE\).*;/,
 	'vacuumdb -zj2');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-Z', 'postgres' ],
@@ -38,11 +38,11 @@ $node->issues_sql_like(
 	'vacuumdb -Z');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--disable-page-skipping', 'postgres' ],
-	qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING\).*;/,
+	qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING, SKIP_DATABASE_STATS\).*;/,
 	'vacuumdb --disable-page-skipping');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--skip-locked', 'postgres' ],
-	qr/statement: VACUUM \(SKIP_LOCKED\).*;/,
+	qr/statement: VACUUM \(SKIP_DATABASE_STATS, SKIP_LOCKED\).*;/,
 	'vacuumdb --skip-locked');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--skip-locked', '--analyze-only', 'postgres' ],
@@ -53,32 +53,32 @@ $node->command_fails(
 	'--analyze-only and --disable-page-skipping specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--no-index-cleanup', 'postgres' ],
-	qr/statement: VACUUM \(INDEX_CLEANUP FALSE\).*;/,
+	qr/statement: VACUUM \(INDEX_CLEANUP FALSE, SKIP_DATABASE_STATS\).*;/,
 	'vacuumdb --no-index-cleanup');
 $node->command_fails(
 	[ 'vacuumdb', '--analyze-only', '--no-index-cleanup', 'postgres' ],
 	'--analyze-only and --no-index-cleanup specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--no-truncate', 'postgres' ],
-	qr/statement: VACUUM \(TRUNCATE FALSE\).*;/,
+	qr/statement: VACUUM \(TRUNCATE FALSE, SKIP_DATABASE_STATS\).*;/,
 	'vacuumdb --no-truncate');
 $node->command_fails(
 	[ 'vacuumdb', '--analyze-only', '--no-truncate', 'postgres' ],
 	'--analyze-only and --no-truncate specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--no-process-toast', 'postgres' ],
-	qr/statement: VACUUM \(PROCESS_TOAST FALSE\).*;/,
+	qr/statement: VACUUM \(PROCESS_TOAST FALSE, SKIP_DATABASE_STATS\).*;/,
 	'vacuumdb --no-process-toast');
 $node->command_fails(
 	[ 'vacuumdb', '--analyze-only', '--no-process-toast', 'postgres' ],
 	'--analyze-only and --no-process-toast specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-P', 2, 'postgres' ],
-	qr/statement: VACUUM \(PARALLEL 2\).*;/,
+	qr/statement: VACUUM \(SKIP_DATABASE_STATS, PARALLEL 2\).*;/,
 	'vacuumdb -P 2');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-P', 0, 'postgres' ],
-	qr/statement: VACUUM \(PARALLEL 0\).*;/,
+	qr/statement: VACUUM \(SKIP_DATABASE_STATS, PARALLEL 0\).*;/,
 	'vacuumdb -P 0');
 $node->command_ok([qw(vacuumdb -Z --table=pg_am dbname=template1)],
 	'vacuumdb with connection string');
@@ -119,7 +119,7 @@ $node->command_fails([ 'vacuumdb', '-P', -1, 'postgres' ],
 	'negative parallel degree');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--analyze', '--table', 'vactable(a, b)', 'postgres' ],
-	qr/statement: VACUUM \(ANALYZE\) public.vactable\(a, b\);/,
+	qr/statement: VACUUM \(SKIP_DATABASE_STATS, ANALYZE\) public.vactable\(a, b\);/,
 	'vacuumdb --analyze with complete column list');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--analyze-only', '--table', 'vactable(b)', 'postgres' ],
@@ -150,7 +150,7 @@ $node->issues_sql_like(
 	'vacuumdb --table --min-xid-age');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--schema', '"Foo"', 'postgres' ],
-	qr/VACUUM "Foo".bar/,
+	qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
 	'vacuumdb --schema');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--exclude-schema', '"Foo"', 'postgres' ],
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index c66386d6d3a..58b894216b8 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -44,6 +44,7 @@ typedef struct vacuumingOptions
 	bool		force_index_cleanup;
 	bool		do_truncate;
 	bool		process_toast;
+	bool		skip_database_stats;
 } vacuumingOptions;
 
 /* object filter options */
@@ -533,6 +534,9 @@ vacuum_one_database(ConnParams *cparams,
 		pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
 				 "--parallel", "13");
 
+	/* skip_database_stats is used automatically if server supports it */
+	vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
+
 	if (!quiet)
 	{
 		if (stage != ANALYZE_NO_STAGE)
@@ -790,7 +794,29 @@ vacuum_one_database(ConnParams *cparams,
 	} while (cell != NULL);
 
 	if (!ParallelSlotsWaitCompletion(sa))
+	{
 		failed = true;
+		goto finish;
+	}
+
+	/* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */
+	if (vacopts->skip_database_stats && stage == ANALYZE_NO_STAGE)
+	{
+		const char *cmd = "VACUUM (ONLY_DATABASE_STATS);";
+		ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL);
+
+		if (!free_slot)
+		{
+			failed = true;
+			goto finish;
+		}
+
+		ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
+		run_vacuum_command(free_slot->connection, cmd, echo, NULL);
+
+		if (!ParallelSlotsWaitCompletion(sa))
+			failed = true;
+	}
 
 finish:
 	ParallelSlotsTerminate(sa);
@@ -957,6 +983,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
 				sep = comma;
 			}
+			if (vacopts->skip_database_stats)
+			{
+				/* SKIP_DATABASE_STATS is supported since v16 */
+				Assert(serverVersion >= 160000);
+				appendPQExpBuffer(sql, "%sSKIP_DATABASE_STATS", sep);
+				sep = comma;
+			}
 			if (vacopts->skip_locked)
 			{
 				/* SKIP_LOCKED is supported since v12 */