pgsql: Add eager and lazy freezing strategies to VACUUM. - Mailing list pgsql-committers

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 <pg@bowt.ie>
Reviewed-By: Jeff Davis <pgsql@j-davis.com>
Reviewed-By: Andres Freund <andres@anarazel.de>
Reviewed-By: Matthias van de Meent <boekewurm+postgres@gmail.com>
Discussion: https://postgr.es/m/CAH2-WzkFok_6EAHuK39GaW4FjEFQsY=3J0AAd6FXk93u-Xq3Fg@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/4d417992613949af35530b4e8e83670c4e67e1b2

Modified Files
--------------
doc/src/sgml/config.sgml                      | 30 ++++++++++++++++
doc/src/sgml/maintenance.sgml                 | 50 ++++++++++++++++++++-------
doc/src/sgml/ref/create_table.sgml            | 14 ++++++++
src/backend/access/common/reloptions.c        | 10 ++++++
src/backend/access/heap/heapam.c              |  1 +
src/backend/access/heap/vacuumlazy.c          | 43 ++++++++++++++++++++++-
src/backend/commands/vacuum.c                 | 25 +++++++++++++-
src/backend/postmaster/autovacuum.c           | 10 ++++++
src/backend/utils/misc/guc_tables.c           | 14 ++++++++
src/backend/utils/misc/postgresql.conf.sample |  1 +
src/include/commands/vacuum.h                 | 12 +++++++
src/include/utils/rel.h                       |  1 +
12 files changed, 197 insertions(+), 14 deletions(-)