From 306dc520b9dfd6014613961962a89940a431a069 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 5 Feb 2025 15:48:18 -0600 Subject: Introduce autovacuum_vacuum_max_threshold. MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit One way autovacuum chooses tables to vacuum is by comparing the number of updated or deleted tuples with a value calculated using autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor. The threshold specifies the base value for comparison, and the scale factor specifies the fraction of the table size to add to it. This strategy ensures that smaller tables are vacuumed after fewer updates/deletes than larger tables, which is reasonable in many cases but can result in infrequent vacuums on very large tables. This is undesirable for a couple of reasons, such as very large tables incurring a huge amount of bloat between vacuums. This new parameter provides a way to set a limit on the value calculated with autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor so that very large tables are vacuumed more frequently. By default, it is set to 100,000,000 tuples, but it can be disabled by setting it to -1. It can also be adjusted for individual tables by changing storage parameters. Author: Nathan Bossart Co-authored-by: Frédéric Yhuel Reviewed-by: Melanie Plageman Reviewed-by: Robert Haas Reviewed-by: Laurenz Albe Reviewed-by: Michael Banck Reviewed-by: Joe Conway Reviewed-by: Sami Imseih Reviewed-by: David Rowley Reviewed-by: wenhui qiu Reviewed-by: Vinícius Abrahão Reviewed-by: Robert Treat Reviewed-by: Alena Rybakina Discussion: https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com --- doc/src/sgml/config.sgml | 24 ++++++++++++++++++++++++ doc/src/sgml/maintenance.sgml | 6 ++++-- doc/src/sgml/ref/create_table.sgml | 15 +++++++++++++++ 3 files changed, 43 insertions(+), 2 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index a782f109982..38244409e3c 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8685,6 +8685,30 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + autovacuum_vacuum_max_threshold (integer) + + autovacuum_vacuum_max_threshold + configuration parameter + + + + + Specifies the maximum number of updated or deleted tuples needed to + trigger a VACUUM in any one table, i.e., a limit on + the value calculated with + autovacuum_vacuum_threshold and + autovacuum_vacuum_scale_factor. The default is + 100,000,000 tuples. If -1 is specified, autovacuum will not enforce a + maximum number of updated or deleted tuples that will trigger a + VACUUM operation. This parameter can only be set + in the postgresql.conf file or on the server + command line; but the setting can be overridden for individual tables + by changing storage parameters. + + + + autovacuum_freeze_max_age (integer) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 0be90bdc7ef..f84ad7557d9 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -895,9 +895,11 @@ HINT: Execute a database-wide VACUUM in that database. VACUUM exceeds the vacuum threshold, the table is vacuumed. The vacuum threshold is defined as: -vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples +vacuum threshold = Minimum(vacuum max threshold, vacuum base threshold + vacuum scale factor * number of tuples) - where the vacuum base threshold is + where the vacuum max threshold is + , + the vacuum base threshold is , the vacuum scale factor is , diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 2237321cb4f..417498f71db 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1712,6 +1712,21 @@ WITH ( MODULUS numeric_literal, REM + + autovacuum_vacuum_max_threshold, toast.autovacuum_vacuum_max_threshold (integer) + + autovacuum_vacuum_max_threshold + storage parameter + + + + + Per-table value for + parameter. + + + + autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor (floating point) -- cgit v1.2.3