Re: Avoid Wraparound Failures

The good thing about this is that I have another argument to convince my
bosses of the need to upgrade.

I have noticed error in my test: vacuum will have the expected effect when
the age of the txid stored in relfrozenxid is autovacuum_freeze_max_age .
Before that, vacuum does not freeze the txids because it considers that
they are likely to be modified soon.

Thank you! and happy Sunday!!

El sáb, 26 mar 2022 a las 19:54, Peter Geoghegan (<pg(at)bowt(dot)ie>) escribió:

> On Sat, Mar 26, 2022 at 11:29 AM Loles <lolesft(at)gmail(dot)com> wrote:
> > Why does vacuum seem to do nothing? Is my test wrong?
>
> No, your test seems fine. I work on this area of the code, and I have
> to concede that it's more confusing than it really needs to be.
>
> > I need to understand.. and be able to continue with my life XD
>
> Technically relfrozenxid can be advanced by any VACUUM operation. In
> practice there are a couple of low-level issues that make it rather
> unlikely that it will happen, outside of an aggressive VACUUM -- so a
> person could be forgiven for thinking that it's only possible during
> aggressive VACUUMs. Aggressive VACUUMs are (by definition) guaranteed
> to be able to advance relfrozenxid such that the final
> age(relfrozenxid) is set to a value approximately equal to your
> vacuum_freeze_min_age setting. They happen because no non-aggressive
> VACUUM ever advanced relfrozenxid (even though, as I said, that's very
> much the common case!).
>
> An aggressive VACUUM can be either an anti-wraparound autovacuum, or a
> VACUUM (manual or autovacuum) that is aggressive by virtue of the
> table's age(relfrozenxid) exceeding vacuum_freeze_table_age when the
> VACUUM begins. In practice most installations usually have
> relfrozenxid advanced by aggressive anti-wraparound VACUUMs.
>
> Fortunately it's usually fine to ignore all of this -- you can just
> rely on autovacuum. To answer your original question: the simplest
> thing you could do to dramatically reduce the risk of wraparound
> failure (which is probably very low for your application already) is
> to upgrade.
>
> In particular, the most recent stable version (Postgres 14) has a new
> wraparound failsafe mechanism that makes autovacuum do everything it
> can to avoid wraparound failure, should you get near the point of
> running out of XID space. It also recovers from the situation without
> operator intervention should the worst happen (unless maybe you have
> something that totally holds back cleanup by VACUUM, like a leaked
> replication slot). Finally, there is the freeze map work added to 9.6,
> which also saves a lot of work during aggressive VACUUMs.
>
> --
> Peter Geoghegan
>