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.

VACUUM often marks certain pages 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 typically also removed (actually, xmax is set to the special XID value 0, also known as InvalidTransactionId). See Section 73.6.1.1 for further background information.

Once frozen, heap pages are “self-contained”. All of the page's rows can be read by every transaction, without any transaction ever needing to consult externally stored transaction status metadata (most notably, transaction commit/abort status information from pg_xact won't ever be required).

It can be useful for VACUUM to put off some of the work of freezing, but freezing cannot be put off indefinitely. 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 freezing by VACUUM is somehow impeded (in a database that continues to allocate new transaction IDs), the system will eventually refuse to allocate new transaction IDs. This generally only happens in extreme cases where the system has been misconfigured.

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.

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. Since MultiXact IDs are stored in the xmax field of heap rows (and have an analogous dependency on external transaction status information), they may also need to be removed 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 (not in units of XID). vacuum_multixact_freeze_min_age typically has only a minimal impact on how many pages are frozen, partly because VACUUM usually prefers to remove MultiXact IDs proactively based on low-level considerations around the cost of freezing. vacuum_multixact_freeze_min_age forces VACUUM to process MultiXact IDs in certain rare cases where the implementation would not ordinarily do so.

Managing the added WAL volume from freezing over time is an important consideration for VACUUM. This 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 be marked all-frozen in the visibility map right away.

VACUUM also triggers freezing of pages in cases where it already proved necessary to write out an FPI (full page image) alongside a WAL record generated while removing dead tuples (see Section 30.1 for background information about how FPIs provide torn page protection). This “freeze on an FPI write” mechanism is designed to lower the absolute volume of WAL written over time by VACUUM, across multiple VACUUM operations against the same table. The mechanism often prevents future VACUUM operations from having to write a second FPI for the same page much later on. 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.

VACUUM may not be able to freeze every tuple's xmin in relatively rare cases. The criteria that determines basic 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). In extreme cases a long-running transaction can hold back every VACUUM's removable cutoff for so long that the system is eventually forced to activate xidStopLimit mode protections.

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.