Re: autovacuum_freeze_max_age on append-only tables

On Thu, Apr 21, 2022 at 8:15 PM Senor <frio_cervesa(at)hotmail(dot)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.
>

IIUC "conflicting lock to proceed" is just a different way to say "give up
a lock".

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.

> 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.
>
>
You might consider creating a security definer function (that performs
vacuum freeze on the table) owned by the table owner and grant your
inserting process the ability to execute it.

David J.