On Sat, Mar 26, 2022 at 11:29 AM Loles <lolesft@gmail.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
