Thread: autovacuum_freeze_max_age on append-only tables

Hi All,
I'm attempting to mimic a new feature in version 13 where INSERTS will trigger vacuum for an append-only table.

I'm using v11 and configuring autovacuum_freeze_max_age to a value representing some number of minutes worth of inserts
ona table containing the current day events. I'm looking to understand the details of how the vacuum operates and what
toexpect and plan for. I first ran into an issue when a script attempted to alter the table to change the value of
autovacuum_freeze_max_agewhile a vacuum was running. I know there is a lock conflict while the vacuum is running but I
wasunder the impression that autovacuum_vacuum_cost_limit would limit the time blocked. The ALTER hung much longer than
Iexpected.  

I'm apparently needing an education on how this "to avoid wraparound" vacuum differs from any other. I've seen it
referencedas "more aggressive" but I'd like details. An upgrade to 13 is "right around the corner". 

Pointers to documentation I might have missed is be appreciated.

-Senor

On Wed, Apr 20, 2022 at 4:06 PM senor <frio_cervesa@hotmail.com> wrote:
> I'm attempting to mimic a new feature in version 13 where INSERTS will trigger vacuum for an append-only table.

The problem with that idea is that you need to express the idea that
the table needs to be vacuumed now in terms of its "age", denominated
in XIDs -- but XIDs consumed by the entire system, not just those XIDs
that happen to modify your append-only table. It will likely be very
hard for you to figure out a way to relate these logical units (XIDs)
to some kind of physical cost that captures how far behind you are on
freezing (like blocks, or even tuples). Maybe you'll find something
that works through trial and error, but I wouldn't count on it.

> I'm apparently needing an education on how this "to avoid wraparound" vacuum differs from any other. I've seen it
referencedas "more aggressive" but I'd like details. An upgrade to 13 is "right around the corner".
 

It's complicated -- more complicated than it really should be.
Technically an anti-wraparound autovacuum and an aggressive vacuum are
two different things. In practice anti-wraparound autovacuums are
virtually guaranteed to be aggressive, though an aggressive autovacuum
may not be an antiwraparound VACUUM (sometimes we do aggressive
vacuuming because autovacuum launched a worker before
age(relfrozenxid) reached autovacuum_freeze_max_age, but after
age(relfrozenxid) reached vacuum_freeze_table_age).

See my recent response to a similar question here:

https://postgr.es/m/CAH2-WzkFQ-okvVXizpy4dCEVq75N-Qykh=crhZaO-eaJfLVOPQ@mail.gmail.com

--
Peter Geoghegan

On Wed, 2022-04-20 at 23:06 +0000, senor wrote:
> I'm apparently needing an education on how this "to avoid wraparound" vacuum differs from
> any other. I've seen it referenced as "more aggressive" but I'd like details.

The difference is twofold, as far as I know:

- it will not skip any pages just because it happens not to get a lock on them
- it will refuse to die if the lock it holds on the table conflicts with a user lock

Unless you are in the habit of taking strong locks on the table, you shouldn't
notice a difference.  Anti-wraparound VACUUM is a routine activity and does not
interfere with DML, just like a normal VACUUM.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Thank you both Laurenz and Peter.

Laurenz - It was an article you posted a couple years ago introducing 
the V13 feature that got me thinking about the insert-only situation I had.

Peter - I had been improperly holding anti-wraparound and aggressive in 
my mind as related in a way they are not. You cleared that up.

2 last questions (maybe):

Are the autovacuum_vacuum_cost_* settings handled any differently for 
'to avoid wraparound' vacuums? I understand that it won't give up a lock 
but I was expecting it to still back off due to cost and allow the query 
with conflicting lock to proceed.

Is there any benefit to manually running a vacuum every so many inserts 
as opposed to using autovacuum_freeze_max_age. And in this case should 
it be a vacuum freeze. Rows are never updated or deleted except for the 
occasional roll back due to dropped network connections.

Thanks again

-Senor


On 4/21/2022 6:35, Laurenz Albe wrote:
> On Wed, 2022-04-20 at 23:06 +0000, senor wrote:
>> I'm apparently needing an education on how this "to avoid wraparound" vacuum differs from
>> any other. I've seen it referenced as "more aggressive" but I'd like details.
> The difference is twofold, as far as I know:
>
> - it will not skip any pages just because it happens not to get a lock on them
> - it will refuse to die if the lock it holds on the table conflicts with a user lock
>
> Unless you are in the habit of taking strong locks on the table, you shouldn't
> notice a difference.  Anti-wraparound VACUUM is a routine activity and does not
> interfere with DML, just like a normal VACUUM.
>
> Yours,
> Laurenz Albe

On Thu, Apr 21, 2022 at 8:14 PM Senor <frio_cervesa@hotmail.com> wrote:
> Are the autovacuum_vacuum_cost_* settings handled any differently for
> 'to avoid wraparound' vacuums? I understand that it won't give up a lock
> but I was expecting it to still back off due to cost and allow the query
> with conflicting lock to proceed.

In general, no. For the most part an antiwraparound autovacuum does
exactly the same work as any other autovacuum. Or any other aggressive
VACUUM, at least. But even the extra work that it does over what
non-aggressive VACUUM is still work that any VACUUM might do, if the
circumstances were right.

We still freeze in regular VACUUMs, provided we scan pages with XIDs
that are sufficiently old. The most important difference between it
and aggressive VACUUM is that the former can skip all-visible pages
that have unfrozen XIDs, putting that work off. This can sometimes
lead to a big balloon payment later on, when you finally have an
aggressive VACUUM. I think that that's a design flaw that ought to be
fixed.

Currently non-aggressive VACUUMs always skip all-visible pages. They
should probably freeze some older all-visible pages eagerly, rather
than skipping them, so that the system never gets too far behind on
freezing.

> Is there any benefit to manually running a vacuum every so many inserts
> as opposed to using autovacuum_freeze_max_age. And in this case should
> it be a vacuum freeze.

Given your restrictions, this is probably the best option available.
But maybe you should just set vacuum_freeze_min_age to 0 at the table
level, instead of using vacuum freeze (so you freeze more without
doing aggressive vacuuming all the time, which FREEZE also forces).

Users understandably think that there are several different flavors of
vacuum, but that's not really true (apart from VACUUM FULL, which
really is quite different). The difference between aggressive and
non-aggressive can be big in practice due to an accumulation of
unfrozen pages over multiple non-aggressive vacuums.

-- 
Peter Geoghegan

In any case the cost-based stuff throttles I/O only (per the docs at least) but even while sleeping it still holds its lock.  And it won't be kicked off of the lock by other processes.  I don't see where it is documented that the autovacuum cost settings are altered during the anti-wraparound vacuum so I presume it will still sleep by default.

I knew I was misunderstanding something. I had picked up the impression that the vacuum process cost_delay released the lock for the period. Not just do nothing. Seems like that would be worth mentioning in the Docs. I'm learning this from the inside out in the tradition of "well someone has to do it". I'm sure I'm not alone.

-Senor