Horton

Re: Trigger more frequent autovacuums of heavy insert tables

Melanie Plageman <melanieplageman@gmail.com>

View thread View on postgr.es
From: Melanie Plageman <melanieplageman@gmail.com>
To: wenhui qiu <qiuwenhuifx@gmail.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>, Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, Peter Geoghegan <pg@bowt.ie>, David Rowley <dgrowley@gmail.com>
Date: 2025-02-06T15:42:43Z
Lists: pgsql-hackers

Attachments

Attached v6 is rebased over 306dc520b9dfd60

On Wed, Feb 5, 2025 at 8:54 PM wenhui qiu <qiuwenhuifx@gmail.com> wrote:
>
> Hi Melanie Plageman
>    Thank you for working on this ,Actually, there were two patches aimed at optimizing vacuum-triggered processes, and one of them reached a consensus and has been committed:https://commitfest.postgresql.org/52/5046/  , https://commitfest.postgresql.org/51/5395/, Maybe referring to the already committed patch and setting a maximum value for vacuum_max_ins_threshold would be more acceptable.

We could add autovacuum_vacuum_insert_max_threshold, but with an
insert-only workload, we can expect that the cold data is being
frozen. By calculating the threshold based on unfrozen data, we are
effectively capping the threshold for inserted data without adding
another guc. If any of that data is being unfrozen via updates or
deletes, then the autovacuum_vacuum_max_threshold would apply.

Perhaps I'm missing a case where calculating the insert threshold on
unfrozen data would not act as a cap, in which case I could get on
board with a guc.


- Melanie

Commits

  1. doc: Update formula for vacuum insert threshold.

  2. Redefine pg_class.reltuples to be -1 before the first VACUUM or ANALYZE.

  3. Measure the number of all-visible pages for use in index-only scan costing.