Subject: Re: PG17 optimizations to vacuum

From: Peter Geoghegan <[email-masked]>
Date: Sun, 1 Sep 2024 18:00:25 -0400

On Sun, Sep 1, 2024 at 5:44 PM Pavel Luzanov <[email-masked]> wrote:
> I see a perfectly working TID-store optimization.
> With reduced maintenance_work_mem it used only one 'vacuuming indexes'
> phase instead of 21 in v16.
> But I also expected to see a reduction in the number of WAL records
> and the total size of the WAL. Instead, WAL numbers have significantly
> degraded.
>
> What am I doing wrong?

That does seem weird.

CC'ing the authors of the relevant VACUUM enhancements.

-- 
Peter Geoghegan

---

From: Melanie Plageman <[email-masked]>
Date: Mon, 2 Sep 2024 13:28:49 -0400

> > and the total size of the WAL. Instead, WAL numbers have significantly

I'll investigate more tomorrow, but based on my initial investigation,
there appears to be some interaction related to how much of the
relation is in shared buffers after creating the table and updating
it. If you set shared_buffers sufficiently high and prewarm the table
after the update, master has fewer WAL records reported by vacuum
verbose.

- Melanie