Horton

Re: Trigger more frequent autovacuums of heavy insert tables

Nathan Bossart <nathandbossart@gmail.com>

View thread View on postgr.es
From: Nathan Bossart <nathandbossart@gmail.com>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>, Andres Freund <andres@anarazel.de>, Peter Geoghegan <pg@bowt.ie>, David Rowley <dgrowley@gmail.com>, Corey Huinker <corey.huinker@gmail.com>
Date: 2025-02-25T22:33:27Z
Lists: pgsql-hackers 
On Tue, Feb 25, 2025 at 05:19:30PM -0500, Melanie Plageman wrote:
> Yes, so one thing you haven't said yet is if you are +1 on going
> forward with these patches in general.

Sorry, yes, I'm +1 in general.  It conceptually makes sense to me that we
should disregard frozen pages when deciding whether to do an insert vacuum,
and it's hard to argue with the results in your original post.  I also am
not overly concerned about worker starvation.  While this patch does give
higher priority to insert-only/mostly tables, it's also reducing the amount
of resources required to vacuum them, anyway.

-- 
nathan

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.