From 4d417992613949af35530b4e8e83670c4e67e1b2 Mon Sep 17 00:00:00 2001 From: Peter Geoghegan Date: Wed, 25 Jan 2023 14:15:38 -0800 Subject: Add eager and lazy freezing strategies to VACUUM. Eager freezing strategy avoids large build-ups of all-visible pages. It makes VACUUM trigger page-level freezing whenever doing so will enable the page to become all-frozen in the visibility map. This is useful for tables that experience continual growth, particularly strict append-only tables such as pgbench's history table. Eager freezing significantly improves performance stability by spreading out the cost of freezing over time, rather than doing most freezing during aggressive VACUUMs. It complements the insert autovacuum mechanism added by commit b07642db. VACUUM determines its freezing strategy based on the value of the new vacuum_freeze_strategy_threshold GUC (or reloption) with logged tables. Tables that exceed the size threshold use the eager freezing strategy. Unlogged tables and temp tables always use eager freezing strategy, since the added cost is negligible there. Non-permanent relations won't incur any extra overhead in WAL written (for the obvious reason), nor in pages dirtied (since any extra freezing will only take place on pages whose PD_ALL_VISIBLE bit needed to be set either way). VACUUM uses lazy freezing strategy for logged tables that fall under the GUC size threshold. Page-level freezing triggers based on the criteria established in commit 1de58df4, which added basic page-level freezing. Eager freezing is strictly more aggressive than lazy freezing. Settings like vacuum_freeze_min_age still get applied in just the same way in every VACUUM, independent of the strategy in use. The only mechanical difference between eager and lazy freezing strategies is that only the former applies its own additional criteria to trigger freezing pages. Note that even lazy freezing strategy will trigger freezing whenever a page happens to have required that an FPI be written during pruning, provided that the page will thereby become all-frozen in the visibility map afterwards (due to the FPI optimization from commit 1de58df4). The vacuum_freeze_strategy_threshold default setting is 4GB. This is a relatively low setting that prioritizes performance stability. It will be reviewed at the end of the Postgres 16 beta period. Author: Peter Geoghegan Reviewed-By: Jeff Davis Reviewed-By: Andres Freund Reviewed-By: Matthias van de Meent Discussion: https://postgr.es/m/CAH2-WzkFok_6EAHuK39GaW4FjEFQsY=3J0AAd6FXk93u-Xq3Fg@mail.gmail.com --- doc/src/sgml/config.sgml | 30 +++++++++++++++++++++++ doc/src/sgml/maintenance.sgml | 50 +++++++++++++++++++++++++++++--------- doc/src/sgml/ref/create_table.sgml | 14 +++++++++++ 3 files changed, 82 insertions(+), 12 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index f985afc009d..9be0438c68f 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -9272,6 +9272,36 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + vacuum_freeze_strategy_threshold (integer) + + vacuum_freeze_strategy_threshold configuration parameter + + + + + Specifies the cutoff storage size that + VACUUM should use to determine its freezing + strategy. This is applied by comparing it to the size of the + target table's main + fork at the beginning of each VACUUM. + Eager freezing strategy is used by VACUUM + when the table's main fork size exceeds this value. + VACUUM always uses + eager freezing strategy when processing unlogged tables, + regardless of this setting. Otherwise VACUUM + uses lazy freezing strategy. For more information see . + + + If this value is specified without units, it is taken as + megabytes. The default is four gigabytes + (4GB). + + + + vacuum_failsafe_age (integer) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 759ea5ac9c4..687b168cc56 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -478,13 +478,30 @@ - - controls how old an XID value has to be before rows bearing that XID will be - frozen. Increasing this setting may avoid unnecessary work if the - rows that would otherwise be frozen will soon be modified again, - but decreasing this setting increases - the number of transactions that can elapse before the table must be - vacuumed again. + controls + VACUUM's freezing strategy. The + eager freezing strategy makes + VACUUM freeze all rows on a page whenever each + and every row on the page is considered visible to all current + transactions (immediately after dead row versions are removed). + Freezing pages early and in batch often spreads out the overhead + of freezing over time. VACUUM consistently + avoids allowing unfrozen all-visible pages to build up, improving + system level performance stability. The lazy freezing + strategy makes VACUUM determine + whether pages should be frozen on the basis of the age of the + oldest XID on the page. Freezing pages lazily sometimes avoids + the overhead of freezing that turns out to have been unnecessary + because the rows were modified soon after freezing took place. + + + + controls how old an + XID value has to be before pages with rows bearing that XID are + frozen. This setting is an additional trigger criteria for + freezing a page's tuples. It is used by both freezing strategies, + though it typically has little impact when VACUUM + uses the eager freezing strategy. @@ -506,12 +523,21 @@ always use its aggressive strategy. + + Controlling the overhead of freezing existing all-visible pages + during aggressive vacuuming is the goal of the eager freezing + strategy. Increasing vacuum_freeze_strategy_threshold + may avoid unnecessary work, but it increases the risk of an + eventual aggressive vacuum that performs an excessive amount of + catch up freezing all at once. + + The maximum time that a table can go unvacuumed is two billion transactions minus the vacuum_freeze_min_age value at the time of the last aggressive vacuum. If it were to go - unvacuumed for longer than - that, data loss could result. To ensure that this does not happen, + unvacuumed for longer than that, the system could temporarily refuse to + allocate new transaction IDs. To ensure that this never happens, autovacuum is invoked on any table that might contain unfrozen rows with XIDs older than the age specified by the configuration parameter . (This will happen even if @@ -551,7 +577,7 @@ - The sole disadvantage of increasing autovacuum_freeze_max_age + One disadvantage of increasing autovacuum_freeze_max_age (and vacuum_freeze_table_age along with it) is that the pg_xact and pg_commit_ts subdirectories of the database cluster will take more space, because it @@ -837,8 +863,8 @@ vacuum insert threshold = vacuum base insert threshold + vacuum insert scale fac For tables which receive INSERT operations but no or almost no UPDATE/DELETE operations, it may be beneficial to lower the table's - as this may allow - tuples to be frozen by earlier vacuums. The number of obsolete tuples and + + to allow freezing to take place proactively. The number of obsolete tuples and the number of inserted tuples are obtained from the cumulative statistics system; it is a semi-accurate count updated by each UPDATE, DELETE and INSERT operation. (It is diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index a03dee4afef..3b40b5355d8 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1781,6 +1781,20 @@ WITH ( MODULUS numeric_literal, REM + + autovacuum_freeze_strategy_threshold, toast.autovacuum_freeze_strategy_threshold (integer) + + autovacuum_freeze_strategy_threshold storage parameter + + + + + Per-table value for + parameter. + + + + log_autovacuum_min_duration, toast.log_autovacuum_min_duration (integer) -- cgit v1.2.3