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 <nathandboss...@gmail.com> Co-authored-by: Frédéric Yhuel <frederic.yh...@dalibo.com> Reviewed-by: Melanie Plageman <melanieplage...@gmail.com> Reviewed-by: Robert Haas <robertmh...@gmail.com> Reviewed-by: Laurenz Albe <laurenz.a...@cybertec.at> Reviewed-by: Michael Banck <mba...@gmx.net> Reviewed-by: Joe Conway <m...@joeconway.com> Reviewed-by: Sami Imseih <samims...@gmail.com> Reviewed-by: David Rowley <dgrowle...@gmail.com> Reviewed-by: wenhui qiu <qiuwenhu...@gmail.com> Reviewed-by: Vinícius Abrahão <vinnix....@gmail.com> Reviewed-by: Robert Treat <r...@xzilla.net> Reviewed-by: Alena Rybakina <a.rybak...@postgrespro.ru> Discussion: https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/306dc520b9dfd6014613961962a89940a431a069 Modified Files -------------- doc/src/sgml/config.sgml | 24 ++++++++++++++++++++++++ doc/src/sgml/maintenance.sgml | 6 ++++-- doc/src/sgml/ref/create_table.sgml | 15 +++++++++++++++ src/backend/access/common/reloptions.c | 11 +++++++++++ src/backend/postmaster/autovacuum.c | 12 ++++++++++++ src/backend/utils/misc/guc_tables.c | 9 +++++++++ src/backend/utils/misc/postgresql.conf.sample | 3 +++ src/bin/psql/tab-complete.in.c | 2 ++ src/include/postmaster/autovacuum.h | 1 + src/include/utils/rel.h | 1 + 10 files changed, 82 insertions(+), 2 deletions(-)
