Database Reference
In-Depth Information
Freezing takes place when a transaction identifier on a row becomes more than vacuum_
freeze_min_age transactions older than the current next value. Normal VACUUMs will perform
a small amount of freezing as you go, and in most cases, you won't notice at all. As explained
in the earlier example, large transactions leave many rows with the same transaction
identifiers, so those might cause problems at freezing time.
VACUUM is normally optimized to look only at chunks of a table that require cleaning. When
a table reaches vacuum_freeze_table_age, we ignore that optimization, and scan the whole
table. While it does so, it's fairly likely to see rows that need freezing, which need to be re-
written. So that is what causes the great increase in I/O.
If you fiddle with those parameters to try to forestall heavy VACUUMs, then you'll find that the
autovacuum_freeze_max_age
parameter controls when the table will be scanned by a
forced VACUUM. To put that another way, you can't turn off the need to freeze rows, but you
can get to choose when this happens. My advice is to control autovacuum as described in
previous recipe, or perform explicit VACUUMs at a time of your choosing.
VACUUM is also an efficient way to confirm the absence of page corruptions, so it is worth
scanning the whole database from time-to-time, every block. To do this, you can run the
following script on each of your databases:
SET vacuum_freeze_table_age = 0;
VACUUM;
You can do this table-by-table as well; there's nothing special about whole database VACUUMs
anymore—in earlier versions of PostgreSQL this was important, so you may read in random
places on the web that this is a good idea.
If you've never had a corrupt block, then you may only need to scan maybe every
two-to-three months. If you start to get corrupt blocks, then you may want to increase
the scan rate to confirm everything is OK. Corrupt blocks are usually hardware-induced,
though they show up as database errors. It's possible but rare that the corruption was instead
from a PostgreSQL bug.
There's no easy way to fix page corruptions at present. There are ways to investigate and
extract data from corrupt blocks, for example, using the contrib/pageinspect utility I wrote.
Avoiding transaction wraparound
To many users, "transaction wraparound" sounds like a disease from space. Mentioning
"transaction wraparound" usually earns the speaker points for technical merit. Let's take a
look at it, and how to avoid it.
