In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC, see Chapter 13): the row version must not be deleted while it is still potentially visible to other transactions. A deleted row version (whether from an UPDATE or DELETE) will usually cease to be of interest to any still running transaction shortly after the original deleting transaction commits.

The space dead tuples occupy must eventually be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. Reclaiming space from dead rows is VACUUM's main responsibility.

The XID cutoff point that VACUUM uses to determine whether or not a deleted tuple is safe to physically remove is reported under removable cutoff in the server log when autovacuum logging (controlled by log_autovacuum_min_duration) reports on a VACUUM operation executed by autovacuum. Tuples that are not yet safe to remove are counted as dead but not yet removable tuples in the log report. VACUUM establishes its removable cutoff once, at the start of the operation. Any older snapshot (or transaction that allocates an XID) that's still running when the cutoff is established may hold it back.

VACUUM will not return space to the operating system, except in the special case where a group of contiguous pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. This relation truncation behavior can be disabled in tables where the exclusive lock is disruptive by setting the table's vacuum_truncate storage parameter to off.

PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID numbers (XID) to determine whether or not the row is visible to each query's MVCC snapshot (see interpreting XID stamps from tuple headers). But since on-disk storage of transaction IDs in heap pages uses a truncated 32-bit representation to save space (rather than the full 64-bit representation), it is necessary to vacuum every table in every database at least once every two billion transactions (though far more frequent vacuuming is typical).

VACUUM marks pages as frozen, indicating that all eligible rows on the page were inserted by a transaction that committed sufficiently far in the past that the effects of the inserting transaction are certain to be visible to all current and future transactions. Frozen rows are treated as if the inserting transaction (the heap tuple's xmin XID) committed “infinitely far in the past”, making the effects of the transaction visible to all current and future MVCC snapshots, forever. Once frozen, pages are “self contained” in the sense that all of its rows can be read without ever having to consult externally stored transaction status metadata (for example, transaction commit status information from pg_xact is not required).

Without freezing, the system will eventually refuse to allocate new transaction IDs, making DML statements throw errors until such time as VACUUM can restore the system's ability to allocate new transaction IDs. The system is unable to recognize distances of more than about 2.1 billion transactions between any two unfrozen XIDs. The only safe option that remains is to disallow allocating new XIDs until VACUUM has performed the steps required to make sure that the “distance” invariant is never violated.

vacuum_freeze_min_age can be used to control when freezing takes place. When VACUUM scans a heap page containing even one XID that already attained an age exceeding this value, the page is frozen. All tuples from the page are frozen, making the page suitable for long-term storage (their is no longer any possible need to interpret the contents of the page using external transaction status information). Increasing this setting may avoid unnecessary work if the pages that would otherwise be frozen will soon be modified again, but decreasing this setting makes it more likely that some future VACUUM operation will need to perform an excessive amount of “catch-up freezing”, all in one go.

Multixact IDs are used to support row locking by multiple transactions. Since there is only limited space in a tuple header to store lock information, that information is encoded as a “multiple transaction ID”, or multixact ID for short, whenever there is more than one transaction concurrently locking a row. Information about which transaction IDs are included in any particular multixact ID is stored separately, and only the multixact ID appears in the xmax field in the tuple header. Like transaction IDs, multixact IDs are implemented as a 32-bit counter and corresponding storage. “Freezing” of multixact IDs is also required, though this actually means setting xmax to InvalidTransactionId. (Actually, any xmax field is processed in the same way when its page is frozen, including xmax fields that contain simple XIDs.)

vacuum_multixact_freeze_min_age can also influence which pages VACUUM freezes. Like vacuum_freeze_min_age, the setting triggers page-level freezing. However, vacuum_multixact_freeze_min_age uses MultiXactIds (not XIDs) to measure “age”.

In general, the major cost of freezing is the additional WAL volume. That's why VACUUM doesn't just freeze every eligible tuple at the earliest opportunity: freezing will go to waste in cases where a recently frozen tuple soon goes on to be deleted anyway. Managing the added WAL volume from freezing over time is an important consideration for VACUUM.

VACUUM also triggers freezing of pages in cases where it already proved necessary to write out an FPI to the WAL as torn page protection (as part of removing dead tuples). The extra WAL volume from proactive freezing is insignificant compared to the cost of the FPI. It is very likely (though not quite certain) that the overall volume of WAL will be lower in the long term with tables that have most freezing triggered by the FPI mechanism, since (at least on average) future VACUUMs shouldn't have to write a second FPI out much later on, when freezing becomes strictly necessary. The FPI freezing mechanism is just an alternative trigger criteria for freezing all eligible tuples on the page. In general, VACUUM freezes pages without regard to the condition that triggered freezing. The physical modifications to the page (how tuples are processed) is decoupled from the mechanism that decides if those modifications should happen at all.

VACUUM may not be able to freeze every tuple's xmin in relatively rare cases. The criteria that determines eligibility for freezing is exactly the same as the one that determines if a deleted tuple should be considered removable or merely dead but not yet removable (namely, the XID-based removable cutoff).

SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),
         age(t.relfrozenxid)) as xid_age,
mxid_age(c.relminmxid)
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');

SELECT datname,
age(datfrozenxid) as xid_age,
mxid_age(datminmxid)
FROM pg_database;

WARNING:  database "mydb" must be vacuumed within 39985967 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.

ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT:  Stop the postmaster and vacuum that database in single-user mode.

Vacuum maintains a visibility map for each table to keep track of which pages contain only tuples that are known to be visible to all active transactions (and all future transactions, until the page is again modified). This has two purposes. First, vacuum itself can skip such pages on the next run, since there is nothing to clean up. Even VACUUMs that use the aggressive strategy can skip pages that are both all-visible and all-frozen (the visibility map keeps track of which pages are all-frozen separately).

Second, it allows PostgreSQL to answer some queries using only the index, without reference to the underlying table. Since PostgreSQL indexes don't contain tuple visibility information, a normal index scan fetches the heap tuple for each matching index entry, to check whether it should be seen by the current transaction. An index-only scan, on the other hand, checks the visibility map first. If it's known that all tuples on the page are visible, the heap fetch can be skipped. This is most useful on large data sets where the visibility map can prevent disk accesses. The visibility map is vastly smaller than the heap, so it can easily be cached even when the heap is very large.

As noted in Section 25.2.2.2, anything that influences when and how relfrozenxid and relminmxid advance will also directly affect the high watermark storage overhead needed to store historical transaction status information. For example, increasing autovacuum_freeze_max_age (and vacuum_freeze_table_age along with it) will make the pg_xact and pg_commit_ts subdirectories of the database cluster take more space, because they store the commit status and (if track_commit_timestamp is enabled) timestamp of all transactions back to the datfrozenxid horizon (the earliest datfrozenxid in the entire cluster).

The commit status uses two bits per transaction. The default autovacuum_freeze_max_age setting of 200 million transactions translates to about 50MB of pg_xact storage. When track_commit_timestamp is enabled, about 2GB of pg_commit_ts storage will also be required.

MultiXactId status information is implemented as two separate SLRU storage areas: pg_multixact/members, and pg_multixact/offsets. There is no simple formula to determine the storage overhead per MultiXactId, since MultiXactIds have a variable number of member XIDs.

Truncating of transaction status information is only possible at the end of VACUUMs that advance relfrozenxid (in the case of pg_xact and pg_commit_ts) or relminmxid (in the case of (pg_multixact/members and pg_multixact/offsets) of whatever table happened to have the oldest value in the cluster when the VACUUM began. This typically happens very infrequently, often during aggressive strategy VACUUMs of one of the database's largest tables.

The PostgreSQL query planner relies on statistical information about the contents of tables in order to generate good plans for queries. These statistics are gathered by the ANALYZE command, which can be invoked by itself or as an optional step in VACUUM. It is important to have reasonably accurate statistics, otherwise poor choices of plans might degrade database performance.

The autovacuum daemon, if enabled, will automatically issue ANALYZE commands whenever the content of a table has changed sufficiently. However, administrators might prefer to rely on manually-scheduled ANALYZE operations, particularly if it is known that update activity on a table will not affect the statistics of “interesting” columns. The daemon schedules ANALYZE strictly as a function of the number of rows inserted or updated; it has no knowledge of whether that will lead to meaningful statistical changes.

Tuples changed in partitions and inheritance children do not trigger analyze on the parent table. If the parent table is empty or rarely changed, it may never be processed by autovacuum, and the statistics for the inheritance tree as a whole won't be collected. It is necessary to run ANALYZE on the parent table manually in order to keep the statistics up to date.

As with vacuuming for space recovery, frequent updates of statistics are more useful for heavily-updated tables than for seldom-updated ones. But even for a heavily-updated table, there might be no need for statistics updates if the statistical distribution of the data is not changing much. A simple rule of thumb is to think about how much the minimum and maximum values of the columns in the table change. For example, a timestamp column that contains the time of row update will have a constantly-increasing maximum value as rows are added and updated; such a column will probably need more frequent statistics updates than, say, a column containing URLs for pages accessed on a website. The URL column might receive changes just as often, but the statistical distribution of its values probably changes relatively slowly.

It is possible to run ANALYZE on specific tables and even just specific columns of a table, so the flexibility exists to update some statistics more frequently than others if your application requires it. In practice, however, it is usually best to just analyze the entire database, because it is a fast operation. ANALYZE uses a statistically random sampling of the rows of a table rather than reading every single row.