public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
To: Melanie Plageman <[email protected]>
Cc: Pg Hackers <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: David Rowley <[email protected]>
Subject: Re: Trigger more frequent autovacuums of heavy insert tables
Date: Fri, 7 Feb 2025 14:38:43 -0600
Message-ID: <Z6ZvUx4N4svJmXjF@nathan> (raw)
In-Reply-To: <CAAKRu_arp7a-Kiy8DhL21CZrpm0w3CU3JOFei_LUqSShfpCUPg@mail.gmail.com>
References: <CAAKRu_aj-P7YyBz_cPNwztz6ohP+vWis=iz3YcomkB3NpYA--w@mail.gmail.com>
<Z6ZEzroNEykAQN4A@nathan>
<CAAKRu_arp7a-Kiy8DhL21CZrpm0w3CU3JOFei_LUqSShfpCUPg@mail.gmail.com>
On Fri, Feb 07, 2025 at 02:21:07PM -0500, Melanie Plageman wrote:
> On Fri, Feb 7, 2025 at 12:37 PM Nathan Bossart <[email protected]> wrote:
>> My first reaction is to question whether
>> it makes send to have two strategies for this sort of thing:
>> autovacuum_vacuum_max_threshold for updates/deletes and this for inserts.
>> Perhaps we don't want to more aggressively clean up bloat (except for the
>> very largest tables via the hard cap), but we do want to more aggressively
>> mark newly-inserted tuples frozen. I'm curious what you think.
>
> The goal with insert-only tables is to set the whole page frozen in
> the VM. So, the number of pages is more important than the total
> number of tuples inserted. Whereas, with updates/deletes, it seems
> like the total amount of garbage (# tuples) needing cleaning is more
> important.
I think this is a reasonable position. To be clear, I don't have a problem
with having different strategies, or even with swapping
autovacuum_vacuum_max_threshold with a similar change, if it's the right
thing to do. I just want to be able to articulate why they're different.
>> Wouldn't relallvisible be sufficient here? We'll skip all-visible pages
>> unless this is an anti-wraparound vacuum, at which point I would think the
>> insert threshold goes out the window.
>
> It's a great question. There are a couple reasons why I don't think so.
>
> I think this might lead to triggering vacuums too often for
> insert-mostly tables. For those tables, the pages that are not
> all-visible will largely be just those with data that is new since the
> last vacuum. And if we trigger vacuums based off of the % not
> all-visible, we might decrease the number of cases where we are able
> to vacuum inserted data and freeze it the first time it is vacuumed --
> thereby increasing the total amount of work.
Rephrasing to make sure I understand correctly: you're saying that using
all-frozen would trigger less frequent insert vacuums, which would give us
a better chance of freezing more than more frequent insert vacuums
triggered via all-visible? My suspicion is that the difference would tend
to be quite subtle in practice, but I have no concrete evidence to back
that up.
--
nathan
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Trigger more frequent autovacuums of heavy insert tables
In-Reply-To: <Z6ZvUx4N4svJmXjF@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox