diff options
| author | Tom Lane | 2023-01-06 19:17:25 +0000 |
|---|---|---|
| committer | Tom Lane | 2023-01-06 19:17:25 +0000 |
| commit | a46a7011b27188af526047a111969f257aaf4db8 (patch) | |
| tree | 816e22b0b77bcc10da44ed043eef2879615bc399 /src/test | |
| parent | cd4b2334db4980bbf86a8ba1d446db17e62ca342 (diff) | |
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
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/vacuum.out | 6 | ||||
| -rw-r--r-- | src/test/regress/sql/vacuum.sql | 7 |
2 files changed, 13 insertions, 0 deletions
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 0035d158b7b..d860be0e209 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -282,6 +282,12 @@ ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; VACUUM (PROCESS_TOAST FALSE) vactst; VACUUM (PROCESS_TOAST FALSE, FULL) vactst; ERROR: PROCESS_TOAST required with VACUUM FULL +-- SKIP_DATABASE_STATS option +VACUUM (SKIP_DATABASE_STATS) vactst; +-- ONLY_DATABASE_STATS option +VACUUM (ONLY_DATABASE_STATS); +VACUUM (ONLY_DATABASE_STATS) vactst; -- error +ERROR: ONLY_DATABASE_STATS cannot be specified with a list of tables DROP TABLE vaccluster; DROP TABLE vactst; DROP TABLE vacparted; diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index 9faa8a34a6a..9da8f3e8303 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -237,6 +237,13 @@ ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; VACUUM (PROCESS_TOAST FALSE) vactst; VACUUM (PROCESS_TOAST FALSE, FULL) vactst; +-- SKIP_DATABASE_STATS option +VACUUM (SKIP_DATABASE_STATS) vactst; + +-- ONLY_DATABASE_STATS option +VACUUM (ONLY_DATABASE_STATS); +VACUUM (ONLY_DATABASE_STATS) vactst; -- error + DROP TABLE vaccluster; DROP TABLE vactst; DROP TABLE vacparted; |