Regular Maintenance - PostgreSQL 9 Administration - page 215

$ ln -sf autovacuum.conf.night autovacuum.conf
$ pg_ctl -D datadir reload # server reload command

(customized depending upon your platform).

This then allows us to switch profiles twice per day without needing to edit the configuration
files. You can also tell easily which is the active profile simply by looking at the full details of
the linked file (using ls -l). The exact details of the schedule are up to you; night/day was just
an example, which is unlikely to suit everybody.

See also

autovacuum_freeze_max_age is explained in the recipe Avoiding auto freezing , as are the
more complex table-level parameters.

Avoiding auto freezing and page corruptions

There are some aspects of VACUUM that are complex to explain why they exist, though have
some occasional negative behaviors. Let's look more deeply at those and find some solutions.

Getting ready

PostgreSQL performs regular sweeps to clean out old transaction identifiers, which is known
as "freezing". It does this to defer transaction wraparound, which is discussed in more detail
in the next recipe.

There are two routes that a row can take in PostgreSQL: the row version dies and needs to be
removed by VACUUM, or a row version gets old enough to need to be frozen, also performed by
the VACUUM process.

Why do we care? Say we load a table with 100 million rows. Everything is fine. When those
rows have been there long enough to begin being frozen, the next VACUUM on that table
will re-write all of these rows to freeze their transaction identifiers. Put that another way,
autovacuum will wake up and start using lots of I/O to perform the freezing.

How to do it...

The most obvious way to forestall that exact problem is to explicitly VACUUM a table after a
major load. Of course that doesn't remove the problem entirely, and you might not have time
for that.

Many people's knee-jerk reaction is to turn off autovacuum, because it keeps waking up
at the most inconvenient times. My way is described in the recipe, Controlling automatic
database maintenance .