May 31, 2024
Summary: In this tutorial, you will learn how to tune autovacuum to avoid transaction wraparound problems in PostgreSQL.
Table of Contents
Normally, autovacuum takes care of transaction wraparound problems and starts a special “anti-wraparound” autovacuum worker whenever the oldest transaction ID in a table is older than autovacuum_freeze_max_age transactions or the oldest multixact is older than autovacuum_multixact_freeze_max_age transactions.
Make sure that anti-wraparound vacuum can freeze tuples in all tables
Again, you have to make sure that there is nothing that blocks autovacuum from freezing old tuples and advancing pg_database.datfrozenxid and pg_database.datminmxid. Such blockers can be:
- very long running database sessions that keep a transaction open or have temporary tables (autovacuum cannot process temporary tables).
- data corruption, which can make all autovacuum workers fail with an error.
To prevent data corruption, use good hardware and always run the latest PostgreSQL minor release.
Tuning tables that receive UPDATEs or DELETEs for anti-wraparound vacuum
On tables that receive UPDATEs or DELETEs, all that you have to do is to see that autovacuum is running fast enough to get done in time (see Tuning autovacuum for dead tuple cleanup).
Tuning tables that receive only INSERTs for anti-wraparound vacuum
From PostgreSQL v13 on, there are no special considerations in this case, because you get regular autovacuum runs on such tables as well.
Before that, insert-only tables were problematic: since there are no dead tuples, normal autovacuum runs are never triggered. Then, as soon as autovacuum_freeze_max_age or autovacuum_multixact_freeze_max_age are exceeded, you may suddenly get a massive autovacuum run that freezes a whole large table, takes a long time and causes massive I/O.
To avoid that, reduce autovacuum_freeze_max_age for such a table:
ALTER TABLE insert_only SET (autovacuum_freeze_max_age = 10000000);
Partitioning
With very big tables, it can be advisable to use partitioning. The advantage here is you can have several autovacuum workers working on several partitions in parallel, so that the partitioned table as a whole is done faster than a single autovacuum worker could.
If you have many partitions, you should increase autovacuum_max_workers, the maximum number of autovacuum workers.
Partitioning can also help with vacuuming tables that receive lots of updates, as long as the updates affect all partitions.