From: Peter Geoghegan <pg@bowt.ie>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Add eager and lazy freezing strategies to VACUUM.
Date: Wed, 25 Jan 2023 22:15:59 +0000
Message-ID: <E1pKo3i-005TBD-QS@gemulon.postgresql.org> (raw)
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(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-committers@postgresql.org
Cc: pg@bowt.ie
Subject: Re: pgsql: Add eager and lazy freezing strategies to VACUUM.
In-Reply-To: <E1pKo3i-005TBD-QS@gemulon.postgresql.org>
References: <E1pKo3i-005TBD-QS@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox