Skip to content

Commit 7cb3048

Browse files
michaelpqmichaelpq
committed
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
1 parent 5fd5900 commit 7cb3048

File tree

10 files changed

+104
-14
lines changed

10 files changed

+104
-14
lines changed

‎doc/src/sgml/ref/vacuum.sgml

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
3333
DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
3434
SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
3535
INDEX_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
36+
PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
3637
TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
3738
PARALLEL <replaceable class="parameter">integer</replaceable>
3839

@@ -210,6 +211,20 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
210211
</listitem>
211212
</varlistentry>
212213

214+
<varlistentry>
215+
<term><literal>PROCESS_TOAST</literal></term>
216+
<listitem>
217+
<para>
218+
Specifies that <command>VACUUM</command> should attempt to process the
219+
corresponding <literal>TOAST</literal> table for each relation, if one
220+
exists. This is normally the desired behavior and is the default.
221+
Setting this option to false may be useful when it is only necessary to
222+
vacuum the main relation. This option is required when the
223+
<literal>FULL</literal> option is used.
224+
</para>
225+
</listitem>
226+
</varlistentry>
227+
213228
<varlistentry>
214229
<term><literal>TRUNCATE</literal></term>
215230
<listitem>

‎doc/src/sgml/ref/vacuumdb.sgml

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -244,6 +244,21 @@ PostgreSQL documentation
244244
</listitem>
245245
</varlistentry>
246246

247+
<varlistentry>
248+
<term><option>--no-process-toast</option></term>
249+
<listitem>
250+
<para>
251+
Skip the TOAST table associated with the table to vacuum, if any.
252+
</para>
253+
<note>
254+
<para>
255+
This option is only available for servers running
256+
<productname>PostgreSQL</productname> 14 and later.
257+
</para>
258+
</note>
259+
</listitem>
260+
</varlistentry>
261+
247262
<varlistentry>
248263
<term><option>--no-truncate</option></term>
249264
<listitem>

‎src/backend/commands/vacuum.c

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
104104
bool freeze = false;
105105
bool full = false;
106106
bool disable_page_skipping = false;
107+
bool process_toast = true;
107108
ListCell *lc;
108109

109110
/* Set default value */
@@ -140,6 +141,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
140141
disable_page_skipping = defGetBoolean(opt);
141142
else if (strcmp(opt->defname, "index_cleanup") == 0)
142143
params.index_cleanup = get_vacopt_ternary_value(opt);
144+
else if (strcmp(opt->defname, "process_toast") == 0)
145+
process_toast = defGetBoolean(opt);
143146
else if (strcmp(opt->defname, "truncate") == 0)
144147
params.truncate = get_vacopt_ternary_value(opt);
145148
else if (strcmp(opt->defname, "parallel") == 0)
@@ -189,13 +192,13 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
189192
(analyze ? VACOPT_ANALYZE : 0) |
190193
(freeze ? VACOPT_FREEZE : 0) |
191194
(full ? VACOPT_FULL : 0) |
192-
(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
195+
(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
196+
(process_toast ? VACOPT_PROCESS_TOAST : 0);
193197

194198
/* sanity checks on options */
195199
Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
196200
Assert((params.options & VACOPT_VACUUM) ||
197201
!(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
198-
Assert(!(params.options & VACOPT_SKIPTOAST));
199202

200203
if ((params.options & VACOPT_FULL) && params.nworkers > 0)
201204
ereport(ERROR,
@@ -318,6 +321,13 @@ vacuum(List *relations, VacuumParams *params,
318321
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
319322
errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
320323

324+
/* sanity check for PROCESS_TOAST */
325+
if ((params->options & VACOPT_FULL) != 0 &&
326+
(params->options & VACOPT_PROCESS_TOAST) == 0)
327+
ereport(ERROR,
328+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
329+
errmsg("PROCESS_TOAST required with VACUUM FULL")));
330+
321331
/*
322332
* Send info about dead objects to the statistics collector, unless we are
323333
* in autovacuum --- autovacuum.c does this for itself.
@@ -1895,7 +1905,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
18951905
* us to process it. In VACUUM FULL, though, the toast table is
18961906
* automatically rebuilt by cluster_rel so we shouldn't recurse to it.
18971907
*/
1898-
if (!(params->options & VACOPT_SKIPTOAST) && !(params->options & VACOPT_FULL))
1908+
if ((params->options & VACOPT_PROCESS_TOAST) != 0 &&
1909+
(params->options & VACOPT_FULL) == 0)
18991910
toast_relid = onerel->rd_rel->reltoastrelid;
19001911
else
19011912
toast_relid = InvalidOid;

‎src/backend/postmaster/autovacuum.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2918,8 +2918,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
29182918
tab = palloc(sizeof(autovac_table));
29192919
tab->at_relid = relid;
29202920
tab->at_sharedrel = classForm->relisshared;
2921-
tab->at_params.options = VACOPT_SKIPTOAST |
2922-
(dovacuum ? VACOPT_VACUUM : 0) |
2921+
2922+
/* Note that this skips toast relations */
2923+
tab->at_params.options = (dovacuum ? VACOPT_VACUUM : 0) |
29232924
(doanalyze ? VACOPT_ANALYZE : 0) |
29242925
(!wraparound ? VACOPT_SKIP_LOCKED : 0);
29252926
tab->at_params.index_cleanup = VACOPT_TERNARY_DEFAULT;

‎src/bin/psql/tab-complete.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3870,8 +3870,9 @@ psql_completion(const char *text, int start, int end)
38703870
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
38713871
COMPLETE_WITH("FULL", "FREEZE", "ANALYZE", "VERBOSE",
38723872
"DISABLE_PAGE_SKIPPING", "SKIP_LOCKED",
3873-
"INDEX_CLEANUP", "TRUNCATE", "PARALLEL");
3874-
else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
3873+
"INDEX_CLEANUP", "PROCESS_TOAST",
3874+
"TRUNCATE", "PARALLEL");
3875+
else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|PROCESS_TOAST|TRUNCATE"))
38753876
COMPLETE_WITH("ON", "OFF");
38763877
}
38773878
else if (HeadMatches("VACUUM") && TailMatches("("))

‎src/bin/scripts/t/100_vacuumdb.pl

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33

44
use PostgresNode;
55
use TestLib;
6-
use Test::More tests => 55;
6+
use Test::More tests => 58;
77

88
program_help_ok('vacuumdb');
99
program_version_ok('vacuumdb');
@@ -56,12 +56,19 @@
5656
[ 'vacuumdb', '--analyze-only', '--no-index-cleanup', 'postgres' ],
5757
'--analyze-only and --no-index-cleanup specified together');
5858
$node->issues_sql_like(
59-
[ 'vacuumdb', '--no-truncate', 'postgres' ],
60-
qr/statement: VACUUM \(TRUNCATE FALSE\).*;/,
61-
'vacuumdb --no-truncate');
59+
[ 'vacuumdb', '--no-truncate', 'postgres' ],
60+
qr/statement: VACUUM \(TRUNCATE FALSE\).*;/,
61+
'vacuumdb --no-truncate');
6262
$node->command_fails(
63-
[ 'vacuumdb', '--analyze-only', '--no-truncate', 'postgres' ],
64-
'--analyze-only and --no-truncate specified together');
63+
[ 'vacuumdb', '--analyze-only', '--no-truncate', 'postgres' ],
64+
'--analyze-only and --no-truncate specified together');
65+
$node->issues_sql_like(
66+
[ 'vacuumdb', '--no-process-toast', 'postgres' ],
67+
qr/statement: VACUUM \(PROCESS_TOAST FALSE\).*;/,
68+
'vacuumdb --no-process-toast');
69+
$node->command_fails(
70+
[ 'vacuumdb', '--analyze-only', '--no-process-toast', 'postgres' ],
71+
'--analyze-only and --no-process-toast specified together');
6572
$node->issues_sql_like(
6673
[ 'vacuumdb', '-P', 2, 'postgres' ],
6774
qr/statement: VACUUM \(PARALLEL 2\).*;/,

‎src/bin/scripts/vacuumdb.c

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@ typedef struct vacuumingOptions
4141
* parallel degree, otherwise -1 */
4242
bool do_index_cleanup;
4343
bool do_truncate;
44+
bool process_toast;
4445
} vacuumingOptions;
4546

4647

@@ -99,6 +100,7 @@ main(int argc, char *argv[])
99100
{"min-mxid-age", required_argument, NULL, 7},
100101
{"no-index-cleanup", no_argument, NULL, 8},
101102
{"no-truncate", no_argument, NULL, 9},
103+
{"no-process-toast", no_argument, NULL, 10},
102104
{NULL, 0, NULL, 0}
103105
};
104106

@@ -126,6 +128,7 @@ main(int argc, char *argv[])
126128
vacopts.parallel_workers = -1;
127129
vacopts.do_index_cleanup = true;
128130
vacopts.do_truncate = true;
131+
vacopts.process_toast = true;
129132

130133
pg_logging_init(argv[0]);
131134
progname = get_progname(argv[0]);
@@ -235,6 +238,9 @@ main(int argc, char *argv[])
235238
case 9:
236239
vacopts.do_truncate = false;
237240
break;
241+
case 10:
242+
vacopts.process_toast = false;
243+
break;
238244
default:
239245
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
240246
exit(1);
@@ -291,6 +297,12 @@ main(int argc, char *argv[])
291297
"no-truncate");
292298
exit(1);
293299
}
300+
if (!vacopts.process_toast)
301+
{
302+
pg_log_error("cannot use the \"%s\" option when performing only analyze",
303+
"no-process-toast");
304+
exit(1);
305+
}
294306
/* allow 'and_analyze' with 'analyze_only' */
295307
}
296308

@@ -456,6 +468,14 @@ vacuum_one_database(const ConnParams *cparams,
456468
exit(1);
457469
}
458470

471+
if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
472+
{
473+
PQfinish(conn);
474+
pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
475+
"no-process-toast", "14");
476+
exit(1);
477+
}
478+
459479
if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
460480
{
461481
PQfinish(conn);
@@ -872,6 +892,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
872892
appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
873893
sep = comma;
874894
}
895+
if (!vacopts->process_toast)
896+
{
897+
/* PROCESS_TOAST is supported since v14 */
898+
Assert(serverVersion >= 140000);
899+
appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
900+
sep = comma;
901+
}
875902
if (vacopts->skip_locked)
876903
{
877904
/* SKIP_LOCKED is supported since v12 */
@@ -971,6 +998,7 @@ help(const char *progname)
971998
printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
972999
printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
9731000
printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1001+
printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
9741002
printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
9751003
printf(_(" -P, --parallel=PARALLEL_DEGREE use this many background workers for vacuum, if available\n"));
9761004
printf(_(" -q, --quiet don't write any messages\n"));

‎src/include/commands/vacuum.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -181,7 +181,7 @@ typedef struct VacAttrStats
181181
#define VACOPT_FREEZE 0x08 /* FREEZE option */
182182
#define VACOPT_FULL 0x10 /* FULL (non-concurrent) vacuum */
183183
#define VACOPT_SKIP_LOCKED 0x20 /* skip if cannot get lock */
184-
#define VACOPT_SKIPTOAST 0x40 /* don't process the TOAST table, if any */
184+
#define VACOPT_PROCESS_TOAST 0x40 /* process the TOAST table, if any */
185185
#define VACOPT_DISABLE_PAGE_SKIPPING 0x80 /* don't skip any pages */
186186

187187
/*

‎src/test/regress/expected/vacuum.out

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -252,6 +252,12 @@ RESET default_transaction_isolation;
252252
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
253253
ANALYZE vactst;
254254
COMMIT;
255+
-- PROCESS_TOAST option
256+
ALTER TABLE vactst ADD COLUMN t TEXT;
257+
ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
258+
VACUUM (PROCESS_TOAST FALSE) vactst;
259+
VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
260+
ERROR: PROCESS_TOAST required with VACUUM FULL
255261
DROP TABLE vaccluster;
256262
DROP TABLE vactst;
257263
DROP TABLE vacparted;

‎src/test/regress/sql/vacuum.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -213,6 +213,12 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
213213
ANALYZE vactst;
214214
COMMIT;
215215

216+
-- PROCESS_TOAST option
217+
ALTER TABLE vactst ADD COLUMN t TEXT;
218+
ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
219+
VACUUM (PROCESS_TOAST FALSE) vactst;
220+
VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
221+
216222
DROP TABLE vaccluster;
217223
DROP TABLE vactst;
218224
DROP TABLE vacparted;

0 commit comments

Comments
 (0)