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. 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 --- src/backend/postmaster/autovacuum.c | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'src/backend/postmaster') diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 0ab921a169b..09ec9bb6990 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -120,6 +120,7 @@ int autovacuum_max_workers; int autovacuum_work_mem = -1; int autovacuum_naptime; int autovacuum_vac_thresh; +int autovacuum_vac_max_thresh; double autovacuum_vac_scale; int autovacuum_vac_ins_thresh; double autovacuum_vac_ins_scale; @@ -2895,6 +2896,8 @@ recheck_relation_needs_vacanalyze(Oid relid, * threshold. This threshold is calculated as * * threshold = vac_base_thresh + vac_scale_factor * reltuples + * if (threshold > vac_max_thresh) + * threshold = vac_max_thresh; * * For analyze, the analysis done is that the number of tuples inserted, * deleted and updated since the last analyze exceeds a threshold calculated @@ -2933,6 +2936,7 @@ relation_needs_vacanalyze(Oid relid, /* constants from reloptions or GUC variables */ int vac_base_thresh, + vac_max_thresh, vac_ins_base_thresh, anl_base_thresh; float4 vac_scale_factor, @@ -2974,6 +2978,11 @@ relation_needs_vacanalyze(Oid relid, ? relopts->vacuum_threshold : autovacuum_vac_thresh; + /* -1 is used to disable max threshold */ + vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= -1) + ? relopts->vacuum_max_threshold + : autovacuum_vac_max_thresh; + vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0) ? relopts->vacuum_ins_scale_factor : autovacuum_vac_ins_scale; @@ -3047,6 +3056,9 @@ relation_needs_vacanalyze(Oid relid, reltuples = 0; vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples; + if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh) + vacthresh = (float4) vac_max_thresh; + vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples; anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples; -- cgit v1.2.3