Horton

Re: another autovacuum scheduling thread

Jeremy Schneider <schneider@ardentperf.com>

View thread
From: Jeremy Schneider <schneider@ardentperf.com>
To: Sami Imseih <samimseih@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>, pgsql-hackers@postgresql.org
Date: 2025-10-08T23:40:57Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add rudimentary table prioritization to autovacuum.

  2. Trigger more frequent autovacuums with relallfrozen

  3. Harden nbtree page deletion.

  4. Check for interrupts inside the nbtree page deletion code.

On Wed, 8 Oct 2025 12:06:29 -0500
Sami Imseih <samimseih@gmail.com> wrote:
> 
> One risk I see with this approach is that we will end up autovacuuming
> tables that also take the longest time to complete, which could cause
> smaller, quick-to-process tables to be neglected.
> 
> It’s not always the case that the oldest tables in terms of (M)XID age
> are also the most expensive to vacuum, but that is often more true
> than not.

I think an approach of doing largest objects first actually might work
really well for balancing work amongst autovacuum workers. Many years
ago I designed a system to backup many databases with a pool of workers
and used this same simple & naive algorithm of just reverse sorting on
db size, and it worked remarkably well. If you have one big thing then
you probably want someone to get started on that first. As long as
there's a pool of workers available, as you work through the queue, you
can actually end up with pretty optimal use of all the workers.

-Jeremy