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 transaction ID number (XID) based cutoff point that VACUUM uses to determine if 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 MVCC snapshot (or transaction that allocates an XID) that's still running when the cutoff is established may hold it back.

VACUUM usually won't return space to the operating system. There is one exception: space is returned to the OS whenever a group of contiguous pages appears at the end of a table. VACUUM must acquire an ACCESS EXCLUSIVE lock to perform relation truncation. You can disable relation truncation by setting the table's vacuum_truncate storage parameter to off.

VACUUM often marks some of the pages that it scans 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. The specific Transaction ID number (XID) stored in a frozen heap row's xmin field is no longer needed to determine anything about the row's visibility. Furthermore, when a row undergoing freezing happens to have an XID set in its xmax field (possibly an XID left behind by an earlier SELECT FOR UPDATE row locker), the xmax field's XID is usually also removed.

Once frozen, heap pages are “self-contained”. Every query can read all of the page's rows in a way that assumes that the inserting transaction committed and is visible to its MVCC snapshot. No query will ever have to consult external transaction status metadata to interpret the page's contents, either. In particular, pg_xact transaction XID commit/abort status lookups won't take place during query execution.

Freezing is a WAL-logged operation, so when VACUUM freezes a heap page, any copy of the page located on a physical replication standby server will itself be “frozen” shortly thereafter (when the relevant FREEZE_PAGE WAL record is replayed on the standby). Queries that run on physical replication standbys thereby avoid pg_xact lookups when reading from frozen pages, in just the same way as queries that run on the primary server ^[15].

It can be useful for VACUUM to put off some of the work of freezing, but VACUUM cannot put off freezing forever. Since on-disk storage of transaction IDs in heap row headers uses a truncated 32-bit representation to save space (rather than the full 64-bit representation), freezing plays a crucial role in enabling management of the XID address space by VACUUM. If, for whatever reason, VACUUM is unable to freeze older XIDs on behalf of an application that continues to require new XID allocations, the system will eventually refuse to allocate new transaction IDs. The system generally only enters this state when autovacuum is misconfigured.

vacuum_freeze_min_age controls when freezing takes place. When VACUUM scans a heap page containing even one XID that has already attained an age exceeding this value, the page is frozen.

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 in pg_multixact, and only the MultiXact ID itself (a 32-bit unsigned integer) appears in the tuple's xmax field. This creates a dependency on external transaction status information similar to the dependency that ordinary unfrozen XIDs have on commit status information stored in pg_xact. VACUUM must therefore occasionally remove MultiXact IDs from tuples during freezing.

vacuum_multixact_freeze_min_age also controls when freezing takes place. It is analogous to vacuum_freeze_min_age, but “age” is expressed in units of MultiXact ID. Lowering vacuum_multixact_freeze_min_age forces VACUUM to process xmax fields containing a MultiXact ID in cases where it would otherwise opt to put off the work of processing xmax until the next VACUUM ^[16]. The setting generally doesn't significantly influence the total number of pages VACUUM freezes, even in tables that contain relatively many MultiXact IDs. This is because VACUUM generally prefers proactively processing for most individual xmax fields that contain a MultiXact ID (eager proactive processing is typically cheaper).

Managing the added WAL volume from freezing over time is an important consideration for VACUUM. It is why VACUUM doesn't just freeze every eligible tuple at the earliest opportunity: the WAL written to freeze a page's tuples “goes to waste” in cases where the resulting frozen tuples are soon deleted or updated anyway. It's also why VACUUM will freeze all eligible tuples from a heap page once the decision to freeze at least one tuple is taken: at that point the added cost to freeze all eligible tuples eagerly (measured in “extra bytes of WAL written”) is far lower than the probable cost of deferring freezing until a future VACUUM operation against the same table. Furthermore, once the page is frozen it can generally be marked as all-frozen in the visibility map right away.

VACUUM also triggers freezing of a page in cases where it already proved necessary to write out a full page image (FPI) as part of a WAL record describing how dead tuples were removed ^[17] (see Section 30.1 for background information about how FPIs provide torn page protection). This “freeze on an FPI write” batching mechanism often avoids the need for some future VACUUM operation to write an additional FPI for the same page as part of a WAL record describing how live tuples were frozen. In effect, VACUUM writes slightly more WAL in the short term with the aim of ultimately needing to write much less WAL in the long term.

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, at least until the page is modified). A separate bit tracks whether all of the tuples are frozen.

The visibility map serves two purposes.

First, VACUUM itself can skip such pages on the next run, since there is nothing to clean up. Even aggressive VACUUMs can skip pages that are both all-visible and all-frozen.

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.

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/abort status and (if track_commit_timestamp is enabled) timestamp of all transactions back to the datfrozenxid horizon (the earliest datfrozenxid among all databases in the 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.

MultiXact ID status information storage uses two separate underlying SLRU storage areas: pg_multixact/members, and pg_multixact/offsets. There is no simple formula to determine the storage overhead per MultiXact ID, since in general MultiXact IDs have a variable number of member XIDs. Note, however, that if pg_multixact/members exceeds 2GB, then the effective value of autovacuum_multixact_freeze_max_age used by VACUUM will be lower, resulting in more frequent aggressive mode VACUUMs.

Truncation of transaction status information is only possible at the end of VACUUMs that advance the earliest relfrozenxid (in the case of pg_xact and pg_commit_ts), or the earliest relminmxid (in the case of pg_multixact/members and pg_multixact/offsets) among all tables in the entire database (assuming that its the database with the earliest datfrozenxid and datminmxid in the entire cluster).

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.