Co-authors Luciano Botti and Jakub Wartak.

This article is intended for information on how to setup the avoid_anti_wraparound_vacuums.sh script to perform maintenance freezing ahead of time in order to prevent autovacuum from running vacuum to prevent wraparound during busy hours.

EDB offers their support customers a tool to alleviate the system load caused by vacuum to prevent wraparound during busy hours. This tool is the avoid_anti_wraparound_vacuums.sh script (or aawv for short).

aawv is intended to run in cron (or similar) to schedule maintenance operations to be run on the tables that need them.

avoid_anti_wraparound_vacuums.sh is a bash script that searches for relations with freeze age close to autovacuum_freeze_max_age. It runs VACUUM over each on the selected relations, in ascending order, based on how close the relation is to autovacuum_freeze_max_age.

Please create a ticket to EDB support regarding receiving the script, as it is not offered in our repositories.

Only the most common options for aawv execution are listed here. For a comprehensive list, run ./avoid_anti_wraparound_vacuums.sh -help.

The -i option is recommended on any PostgreSQL12+ installation. Since PostgreSQL 12, the new vacuum option VACUUM (INDEX_CLEANUP off) provides functionality equivalent to (now, legacy) antifreeze extension that should NOT be used.

The -H <hostname> option specifies the host name (or path to socket) that the script connects to.

The -U <username> option specifies the username to connect to the server. The default is postgres.

The -t N option specifies the maximum amount of time, in seconds, that the script will run. This is important because running the script outside a maintenance window may be undesirable. This option defaults to 3600 (1 hour).

The -b N option specifies the percentage of autovacuum_freeze_max_age of relations to process. Valid values are from 0 to 99. With values closer to 99 fewer tables will be processed, while a value of 0 matches all tables. The default is 80, which is the recommended value for typical transaction ID consumption rates.

You might want to feed the script with some new values for postgres parameters. You can do so by placing SET statements in a configuration file and using -c <conf_file> so the script reads those statements before freezing.

Here's a typical example setting parameters of interest:

-- Set extra parameters you will want to use with the vacuum runs.
SET maintenance_work_mem TO '1GB';
SET vacuum_cost_delay TO '5ms';
SET vacuum_cost_limit TO 2000;
SET lock_timeout TO '2s';

The PostgreSQL documentation contains an exhaustive list of parameters, and explanations for those that we show above.

All statements should end with a ; as they will be included through a psql session.

If we want to run the script for two hours, selecting tables that have freeze age older than 75% of autovacuum_freeze_max_age, connecting to localhost and using configuration options from the file /home/edb/aawv.conf:

./avoid_anti_wraparound_vacuums.sh -H localhost -b 75 -c /home/edb/aawv.conf -t 7200

Our usual recommendation is to define an interval of time in which the server is under lower load, or a time at which VACUUM can run freely without locking out stronger locks (normally exclusive locks from DDL and statements like VACUUM FULL or CLUSTER).

Once that interval is decided, you can set the crontab job to run the script during that interval. For example, if the time between 1am and 5am (machine time) any day of the week is the best to perform freezing of tuples, your crontab entry looks like this:

0 1 * * * ./avoid_anti_wraparound_vacuums.sh -H localhost -b 75 -c aawv.conf -t 14400 >> ~/logs/anti_wraparound_`hostname`_`date +'%F_%T'`.log 2>&1

Happy freezing!