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