Add option PROCESS_TOAST to VACUUM

This option controls if toast tables associated with a relation are
vacuumed or not when running a manual VACUUM.  It was already possible
to trigger a manual VACUUM on a toast relation without processing its
main relation, but a manual vacuum on a main relation always forced a
vacuum on its toast table.  This is useful in scenarios where the level
of bloat or transaction age of the main and toast relations differs a
lot.

This option is an extension of the existing VACOPT_SKIPTOAST that was
used by autovacuum to control if toast relations should be skipped or
not.  This internal flag is renamed to VACOPT_PROCESS_TOAST for
consistency with the new option.

A new option switch, called --no-process-toast, is added to vacuumdb.

Author: Nathan Bossart
Reviewed-by: Kirk Jamison, Michael Paquier, Justin Pryzby
Discussion: https://postgr.es/m/BA8951E9-1524-48C5-94AF-73B1F0D7857F@amazon.com

2 files changed, 41 insertions, 6 deletions

diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 9e36b6d2b05..99ec8bebde3 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -3,7 +3,7 @@ use warnings;
 
 use PostgresNode;
 use TestLib;
-use Test::More tests => 55;
+use Test::More tests => 58;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -56,12 +56,19 @@ $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\).*;/,
-    'vacuumdb --no-truncate');
+	[ 'vacuumdb', '--no-truncate', 'postgres' ],
+	qr/statement: VACUUM \(TRUNCATE FALSE\).*;/,
+	'vacuumdb --no-truncate');
 $node->command_fails(
-    [ 'vacuumdb', '--analyze-only', '--no-truncate', 'postgres' ],
-    '--analyze-only and --no-truncate specified together');
+	[ '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\).*;/,
+	'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\).*;/,
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 9dc8aca29f3..602fd45c429 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -41,6 +41,7 @@ typedef struct vacuumingOptions
 									 * parallel degree, otherwise -1 */
 	bool		do_index_cleanup;
 	bool		do_truncate;
+	bool		process_toast;
 } vacuumingOptions;
 
 
@@ -99,6 +100,7 @@ main(int argc, char *argv[])
 		{"min-mxid-age", required_argument, NULL, 7},
 		{"no-index-cleanup", no_argument, NULL, 8},
 		{"no-truncate", no_argument, NULL, 9},
+		{"no-process-toast", no_argument, NULL, 10},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -126,6 +128,7 @@ main(int argc, char *argv[])
 	vacopts.parallel_workers = -1;
 	vacopts.do_index_cleanup = true;
 	vacopts.do_truncate = true;
+	vacopts.process_toast = true;
 
 	pg_logging_init(argv[0]);
 	progname = get_progname(argv[0]);
@@ -235,6 +238,9 @@ main(int argc, char *argv[])
 			case 9:
 				vacopts.do_truncate = false;
 				break;
+			case 10:
+				vacopts.process_toast = false;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -291,6 +297,12 @@ main(int argc, char *argv[])
 						 "no-truncate");
 			exit(1);
 		}
+		if (!vacopts.process_toast)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-process-toast");
+			exit(1);
+		}
 		/* allow 'and_analyze' with 'analyze_only' */
 	}
 
@@ -456,6 +468,14 @@ vacuum_one_database(const ConnParams *cparams,
 		exit(1);
 	}
 
+	if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-process-toast", "14");
+		exit(1);
+	}
+
 	if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -872,6 +892,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
 				sep = comma;
 			}
+			if (!vacopts->process_toast)
+			{
+				/* PROCESS_TOAST is supported since v14 */
+				Assert(serverVersion >= 140000);
+				appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
+				sep = comma;
+			}
 			if (vacopts->skip_locked)
 			{
 				/* SKIP_LOCKED is supported since v12 */
@@ -971,6 +998,7 @@ help(const char *progname)
 	printf(_("      --min-mxid-age=MXID_AGE     minimum multixact ID age of tables to vacuum\n"));
 	printf(_("      --min-xid-age=XID_AGE       minimum transaction ID age of tables to vacuum\n"));
 	printf(_("      --no-index-cleanup          don't remove index entries that point to dead tuples\n"));
+	printf(_("      --no-process-toast          skip the TOAST table associated with the table to vacuum\n"));
 	printf(_("      --no-truncate               don't truncate empty pages at the end of the table\n"));
 	printf(_("  -P, --parallel=PARALLEL_DEGREE  use this many background workers for vacuum, if available\n"));
 	printf(_("  -q, --quiet                     don't write any messages\n"));