From 06eae9e6218ab2acf64ea497bad0360e4c90e32d Mon Sep 17 00:00:00 2001 From: Melanie Plageman Date: Mon, 3 Mar 2025 14:42:00 -0500 Subject: Trigger more frequent autovacuums with relallfrozen Calculate the insert threshold for triggering an autovacuum of a relation based on the number of unfrozen pages. By only considering the unfrozen portion of the table when calculating how many tuples to add to the insert threshold, we can trigger more frequent vacuums of insert-heavy tables. This increases the chances of vacuuming those pages when they still reside in shared buffers This also increases the number of autovacuums triggered by tuples inserted and not by wraparound risk. We prefer to freeze these pages during insert-triggered autovacuums, as anti-wraparound vacuums are not automatically canceled by conflicting lock requests. We calculate the unfrozen percentage of the table using the recently added (99f8f3fbbc8f) relallfrozen column of pg_class. Author: Melanie Plageman Reviewed-by: Nathan Bossart Reviewed-by: Greg Sabino Mullane Reviewed-by: Robert Treat Reviewed-by: wenhui qiu Discussion: https://postgr.es/m/flat/CAAKRu_aj-P7YyBz_cPNwztz6ohP%2BvWis%3Diz3YcomkB3NpYA--w%40mail.gmail.com --- src/backend/utils/misc/postgresql.conf.sample | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'src/backend/utils/misc/postgresql.conf.sample') diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 5362ff80519..2d1de9c37bd 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -675,8 +675,8 @@ autovacuum_worker_slots = 16 # autovacuum worker slots to allocate #autovacuum_analyze_threshold = 50 # min number of row updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum -#autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of inserts over table - # size before insert vacuum +#autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of unfrozen pages + # before insert vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_vacuum_max_threshold = 100000000 # max number of row updates # before vacuum; -1 disables max -- cgit v1.2.3