Horton
Menu

Re: New IndexAM API controlling index vacuum strategies

Andres Freund <andres@anarazel.de>

View thread
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Robert Haas <robertmhaas@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Noah Misch <noah@leadboat.com>
Date: 2021-04-16T00:12:18Z
Lists: pgsql-hackers 
Hi,

On 2021-04-14 21:30:29 -0700, Peter Geoghegan wrote:
> I think that this was once true, but is now much less common, mostly
> due to the freeze map stuff in 9.6. And due a general recognition that
> the *risk* of increasing them is just too great (a risk that we can
> hope was diminished by the failsafe, incidentally). As an example of
> this, Christophe Pettus had a Damascene conversion when it came to
> increasing autovacuum_freeze_max_age aggressively, which we explains
> here:
> 
> https://thebuild.com/blog/2019/02/08/do-not-change-autovacuum-age-settings/

Not at all convinced. The issue of needing to modify a lot of
all-visible pages again to freeze them is big enough to let it be a
problem even after the freeze map. Yes, there's workloads where it's
much less of a problem, but not all the time.



> As I said, we handle the case where autovacuum_freeze_max_age is set
> to something larger than vacuum_failsafe_age is a straightforward and
> pretty sensible way. I am curious, though: what
> autovacuum_freeze_max_age setting is "much higher" than 1.6 billion,
> but somehow also not extremely ill-advised and dangerous? What number
> is that, precisely? Apparently this is common, but I must confess that
> it's the first I've heard about it.

I didn't intend to say that the autovacuum_freeze_max_age would be set
much higher than 1.6 billion, just that that the headroom would be much
less. I've set it, and seen it set, to 1.5-1.8bio without problems,
while reducing overhead substantially.

Greetings,

Andres Freund

Commits

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

  1. Don't truncate heap when VACUUM's failsafe is in effect.

  2. Teach VACUUM to bypass unnecessary index vacuuming.

  3. Add wraparound failsafe to VACUUM.

  4. Truncate line pointer array during VACUUM.

  5. Remove tupgone special case from vacuumlazy.c.

  6. Refactor lazy_scan_heap() loop.

  7. Propagate parallel VACUUM's buffer access strategy.

  8. Simplify state managed by VACUUM.

  9. Notice that heap page has dead items during VACUUM.

  10. Adjust lazy_scan_heap() accounting comments.

  11. Use full 64-bit XID for checking if a deleted GiST page is old enough.

  12. Fix some problems with VACUUM (INDEX_CLEANUP FALSE).