PostgreSQL VACUUM, MVCC, and Bloat Optimization Guide

• Introduction
• MVCC Internals
  • The Tuple Header: xmin, xmax, ctid
  • Transaction Snapshots and Visibility Decisions
  • How Dead Tuples Arise
  • HOT (Heap-Only Tuple) Updates
• VACUUM Mechanics in Depth
  • Standard VACUUM vs VACUUM FULL
  • Visibility Map and Free Space Map
  • The 3 Phases of VACUUM
  • Freeze Processing and XID Management
• Autovacuum Tuning Guide
  • Understanding the Key Parameters
  • Optimizing the Cost-based Delay
  • Worker Count and Per-Table Settings
  • A Tuning Strategy Specifically for Large Tables
  • Monitoring Autovacuum
• Detecting and Preventing Table Bloat
  • Comparing Bloat Measurement Methods
  • A Bloat Estimation Query
• Preventing XID Wraparound
  • The Limits of a 32-bit Transaction Counter
  • How Emergency Autovacuum Works
  • Monitoring XID Wraparound
• pg_repack: Removing Bloat Without Downtime
  • Why Use pg_repack Instead of VACUUM FULL
  • How pg_repack Works, and Its Constraints
  • Production Execution Procedure
• Failure Cases and Recovery Procedures
  • Case 1: An Old Replication Slot Blocking Autovacuum
  • Case 2: A Bulk UPDATE Causing a Bloat Explosion
  • Recovery Procedure Checklist
• Operational Cautions
  • Managing Long-Running Transactions
  • Cleaning Up Prepared Transactions
  • Essential Metrics for a Monitoring Dashboard
• References

Introduction

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to deliver high concurrency in which reads and writes do not block one another. But this architecture comes with a price you have to understand. Running an UPDATE or a DELETE does not remove the previous version of the tuple right away, and as these Dead Tuples pile up you get Bloat, where the table grows abnormally large.

In GitLab's well-known 2017 incident, table Bloat combined with inadequate autovacuum settings drove disk usage up sharply and took the service down for several hours. Accidents like this come from an insufficient understanding of the VACUUM mechanism and Autovacuum tuning.

In this article, we cover everything a production DBA needs to know: PostgreSQL's MVCC internals, how VACUUM works, Autovacuum tuning, preventing XID Wraparound, and removing Bloat without downtime using pg_repack.

MVCC Internals

The Tuple Header: xmin, xmax, ctid

Every row (tuple) in PostgreSQL carries hidden system columns. The key to MVCC is the transaction metadata stored in each tuple header.

Let us look at the actual xmin/xmax values of a row.

-- Query the tuple's hidden system columns
SELECT ctid, xmin, xmax, id, name
FROM users
WHERE id = 1;

-- Example result:
--  ctid  | xmin  | xmax | id |  name
-- -------+-------+------+----+--------
--  (0,1) | 12345 |    0 |  1 | Alice

Run an UPDATE and you can see that xmax is set and a new tuple is created.

-- Comparison before and after UPDATE
BEGIN;
UPDATE users SET name = 'Bob' WHERE id = 1;

-- Check within the same transaction (requires the pageinspect extension)
SELECT t_xmin, t_xmax, t_ctid, t_data
FROM heap_page_items(get_raw_page('users', 0))
WHERE t_xmin IS NOT NULL;

-- Result: the original tuple's xmax is set to the current transaction ID
-- and a new tuple is created at a separate location
COMMIT;

Transaction Snapshots and Visibility Decisions

Each transaction acquires a snapshot at the moment it starts. The snapshot contains the list of all currently active transactions, and PostgreSQL uses that information to decide the visibility of each tuple.

The visibility rules, put briefly, are as follows.

• xmin is committed and xmax is 0: a valid tuple (visible)
• xmin is committed and xmax is committed too: a deleted tuple (not visible, a Dead Tuple)
• xmin is still in progress: not visible to other transactions
• xmin was aborted (ABORT): not visible to any transaction

How Dead Tuples Arise

In PostgreSQL an UPDATE works internally as a DELETE plus an INSERT. This is a fundamentally different approach from Oracle or MySQL (InnoDB).

1. DELETE: writes the current transaction ID into the existing tuple's xmax. It does not delete anything physically.
2. UPDATE: sets the existing tuple's xmax (a logical delete) and INSERTs a new version of the tuple at a separate location.
3. Dead Tuple accumulation: after the commit, a previous-version tuple that is no longer visible from any snapshot becomes a Dead Tuple.

These Dead Tuples keep occupying disk space until VACUUM cleans them up.

HOT (Heap-Only Tuple) Updates

The HOT update, introduced in PostgreSQL 8.3, is the key optimization that eases the Dead Tuple problem. When no indexed column changes and the new tuple fits on the same page, the index update is skipped and a linked list is formed within the heap alone.

The conditions for a HOT update are as follows.

• The columns being changed must not be indexed
• The new tuple has to be placed on the same heap page
• The table needs enough free space from its fillfactor

The HOT update ratio is a key performance indicator. You can check it with the following query.

-- Query Dead Tuple status and the HOT update ratio
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio_pct,
    n_tup_hot_upd,
    n_tup_upd,
    ROUND(n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0) * 100, 2) AS hot_update_ratio_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

If the HOT update ratio is low, adjusting fillfactor is effective.

-- Set fillfactor to 80% to make room for HOT updates
ALTER TABLE orders SET (fillfactor = 80);
-- Applying this to existing data requires VACUUM FULL or pg_repack

VACUUM Mechanics in Depth

Standard VACUUM vs VACUUM FULL

Key point: Standard VACUUM only marks the space Dead Tuples occupied as "reusable"; it does not reduce the file size. Once a table has grown, it does not physically shrink without VACUUM FULL or pg_repack.

Visibility Map and Free Space Map

The Visibility Map (VM) uses 2 bits per heap page.

• all-visible bit: set when every tuple on the page is visible to all active transactions. Used for Index-Only Scan and for skipping VACUUM
• all-frozen bit: set when every tuple on the page is frozen and therefore permanently visible regardless of XID. Used for skipping Anti-wraparound VACUUM

The Free Space Map (FSM) tracks how much free space is available on each page, so that on an INSERT or UPDATE a suitable page for the new tuple can be found quickly.

The 3 Phases of VACUUM

VACUUM works internally in 3 phases.

1. Scan Phase: scans the whole table sequentially, collecting the TIDs (page number, offset) of Dead Tuples into an array sized by maintenance_work_mem. Pages marked all-visible in the Visibility Map are skipped.

2. Index Vacuum Phase: using the collected list of Dead Tuple TIDs, it walks every index on the table and removes the index entries pointing at those TIDs. The more indexes there are, the longer this phase takes.

3. Heap Vacuum Phase: it actually cleans the Dead Tuples out of the table heap and registers that space in the Free Space Map as reusable. If there are contiguous empty pages at the end of the table, the file is truncated and the space returned to the OS.

Freeze Processing and XID Management

Besides cleaning up Dead Tuples, VACUUM performs another important role: transaction ID Freeze. PostgreSQL's transaction ID (XID) is a 32-bit unsigned integer, so only about 2.1 billion of them are usable. If the XID wraps around, older data appears to have been "inserted by a future transaction" and you get the catastrophe of data disappearing.

To prevent that, VACUUM replaces the xmin of sufficiently old tuples with the special FrozenTransactionId (value 2). A frozen tuple is always treated as "in the past" in XID comparisons and so escapes the Wraparound risk.

Let us check the actual behavior with VACUUM VERBOSE.

-- Inspect the detailed behavior with VACUUM VERBOSE
VACUUM (VERBOSE, ANALYZE) orders;

-- Example output:
-- INFO:  vacuuming "public.orders"
-- INFO:  scanned index "orders_pkey" to remove 15234 row versions
-- INFO:  scanned index "idx_orders_user_id" to remove 15234 row versions
-- INFO:  table "orders": removed 15234 dead item identifiers in 892 pages
-- INFO:  table "orders": found 15234 removable, 2847561 nonremovable row versions
--        in 45213 out of 62874 pages
-- INFO:  table "orders": truncated 62874 to 51230 pages
-- DETAIL:  CPU: user: 2.15 s, system: 0.89 s, elapsed: 5.43 s

The pgstattuple extension lets you measure the actual physical state of a table precisely.

-- Precise bloat measurement with pgstattuple
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    table_len,
    tuple_count,
    tuple_len,
    tuple_percent,
    dead_tuple_count,
    dead_tuple_len,
    dead_tuple_percent,
    free_space,
    free_percent
FROM pgstattuple('orders');

-- Example result:
--  table_len   | 514850816
--  tuple_count | 2847561
--  tuple_len   | 398258540
--  tuple_percent | 77.35
--  dead_tuple_count | 0        -- 0 if run right after VACUUM
--  dead_tuple_len   | 0
--  dead_tuple_percent | 0
--  free_space   | 89456280
--  free_percent | 17.37       -- a high ratio here means a bloated state

Autovacuum Tuning Guide

Understanding the Key Parameters

Autovacuum is the process that runs VACUUM automatically in the background. The key to tuning it is controlling "when it should start" and "how fast or slow it should run".

Trigger condition: autovacuum starts when the number of Dead Tuples exceeds the following threshold.

Threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor x number of rows in the table

The defaults are threshold=50 and scale_factor=0.2. That is, a table with 10 million rows needs 2 million Dead Tuples before autovacuum starts. On large tables that default is far too loose.

Optimizing the Cost-based Delay

Autovacuum uses a cost-based delay to control I/O load.

PostgreSQL 17 lowered the default cost_delay to 2ms, but on a large OLTP system a more aggressive setting may be needed.

Worker Count and Per-Table Settings

The key is separating the global settings from per-table settings for large tables.

-- Recommended postgresql.conf settings (global)
-- autovacuum_max_workers = 5             -- raised from the default 3
-- autovacuum_vacuum_cost_delay = 2ms     -- the PostgreSQL 17 default
-- autovacuum_vacuum_cost_limit = 400     -- raised from the default 200
-- maintenance_work_mem = 1GB             -- allocate ample memory for VACUUM
-- autovacuum_naptime = 30s               -- shortened from the default 1 minute

-- Per-table autovacuum settings for a large table
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,     -- 1% (aggressive versus the default 20%)
    autovacuum_vacuum_threshold = 1000,         -- minimum of 1000
    autovacuum_analyze_scale_factor = 0.005,    -- analyze more often too
    autovacuum_vacuum_cost_delay = 1,           -- process faster (ms)
    autovacuum_vacuum_cost_limit = 600          -- allow more work
);

-- A log-style table (INSERT only, no UPDATE/DELETE)
ALTER TABLE audit_logs SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_freeze_max_age = 500000000,     -- manage freeze only
    autovacuum_enabled = true
);

A Tuning Strategy Specifically for Large Tables

On large tables of 100 million rows or more, apply the following strategy.

• Set scale_factor to 0.01 or lower: at the default 0.2, vacuum does not start until 20 million Dead Tuples have piled up
• Raise maintenance_work_mem to 1GB or more: enlarging the array that holds Dead Tuple TIDs prevents multiple passes
• Minimize the number of indexes: the more indexes there are, the longer VACUUM's index cleanup phase runs
• Set fillfactor to 80: this raises the HOT update ratio and suppresses Dead Tuple creation

Monitoring Autovacuum

-- Check the autovacuum processes currently running
SELECT
    pid,
    datname,
    relid::regclass AS table_name,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed,
    ROUND(100.0 * heap_blks_vacuumed / NULLIF(heap_blks_total, 0), 1) AS progress_pct,
    index_vacuum_count,
    num_dead_tuples
FROM pg_stat_progress_vacuum;

-- List of tables that need Autovacuum but have not been processed yet
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze,
    CURRENT_TIMESTAMP - COALESCE(last_autovacuum, '2000-01-01'::timestamp) AS since_last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Detecting and Preventing Table Bloat

Comparing Bloat Measurement Methods

In production, the recommended 2-stage approach is to build the everyday dashboard on the statistics estimate and, when something looks wrong, diagnose precisely with pgstattuple.

A Bloat Estimation Query

The following is a Bloat estimation query built on pg_class statistics. It runs without pgstattuple.

-- Table Bloat estimation query (statistics based, lock free)
WITH constants AS (
    SELECT
        current_setting('block_size')::numeric AS bs,
        23 AS hdr,   -- heap tuple header size
        8 AS ma      -- MAXALIGN
),
bloat_info AS (
    SELECT
        schemaname,
        tablename,
        cc.reltuples::bigint AS est_rows,
        cc.relpages::bigint AS real_pages,
        bs,
        CEIL((cc.reltuples * (datahdr + nullhdr + 4 + ma -
            CASE WHEN datahdr % ma = 0 THEN ma ELSE datahdr % ma END
        )) / (bs - 20)) AS est_pages
    FROM (
        SELECT
            schemaname,
            tablename,
            hdr + COALESCE(SUM(
                CASE WHEN staattnum IS NOT NULL
                    THEN (1 + stawidth) ELSE 0 END
            ), 0) AS datahdr,
            COALESCE(SUM(
                CASE WHEN staattnum IS NOT NULL AND stanullfrac > 0
                    THEN 1 ELSE 0 END
            ) / 8, 0) AS nullhdr,
            ma, bs, hdr
        FROM pg_stats
        CROSS JOIN constants
        LEFT JOIN pg_statistic ON schemaname = schemaname AND tablename = tablename
        GROUP BY schemaname, tablename, hdr, ma, bs
    ) AS sub
    JOIN pg_class cc ON cc.relname = sub.tablename
    JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = sub.schemaname
)
SELECT
    schemaname,
    tablename,
    real_pages,
    est_pages,
    CASE WHEN real_pages > 0
        THEN ROUND(100.0 * (real_pages - est_pages) / real_pages, 1)
        ELSE 0
    END AS bloat_pct,
    pg_size_pretty((real_pages - est_pages)::bigint * bs::bigint) AS wasted_size
FROM bloat_info
WHERE real_pages > est_pages + 10
ORDER BY (real_pages - est_pages) * bs DESC
LIMIT 20;

A Bloat ratio above 50% warrants a caution alert, and above 70% the recommendation is to run pg_repack immediately.

Preventing XID Wraparound

The Limits of a 32-bit Transaction Counter

PostgreSQL's transaction ID (XID) is a 32-bit unsigned integer, giving a maximum of about 4.2 billion (2 to the 32nd power) values. 3 of these are reserved, so in practice a wraparound occurs roughly every 2.1 billion transactions.

When an XID wraparound happens, transactions committed in the past are seen as being in the "future" and their data becomes invisible. The result is effectively the same as data loss, and to prevent it PostgreSQL forcibly stops all write activity as wraparound approaches (recovery is then only possible in Single-User Mode).

How Emergency Autovacuum Works

When autovacuum_freeze_max_age (default 200 million) is reached, PostgreSQL runs an Anti-Wraparound VACUUM ahead of any other autovacuum work. This special VACUUM ignores the cost-based delay and freezes old XIDs as fast as it can.

In the more dangerous situation of reaching vacuum_failsafe_age (default 1.6 billion, PostgreSQL 14 and above), it switches to a failsafe mode that skips index cleanup and performs only the freeze.

Monitoring XID Wraparound

-- Monitor XID consumption per database
SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    ROUND(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_towards_wraparound,
    current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age,
    CASE
        WHEN age(datfrozenxid) > 1500000000 THEN 'CRITICAL'
        WHEN age(datfrozenxid) > 1000000000 THEN 'WARNING'
        WHEN age(datfrozenxid) > 500000000  THEN 'CAUTION'
        ELSE 'OK'
    END AS status
FROM pg_database
WHERE datallowconn
ORDER BY age(datfrozenxid) DESC;

-- Check XID age per table (oldest first)
SELECT
    c.oid::regclass AS table_name,
    age(c.relfrozenxid) AS xid_age,
    pg_size_pretty(pg_table_size(c.oid)) AS table_size,
    ROUND(100.0 * age(c.relfrozenxid) /
        current_setting('autovacuum_freeze_max_age')::bigint, 1) AS pct_of_freeze_max
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

In an operational environment you should send an alert once xid_age passes 500 million, and take immediate action once it passes 1 billion.

pg_repack: Removing Bloat Without Downtime

Why Use pg_repack Instead of VACUUM FULL

VACUUM FULL takes an AccessExclusiveLock, blocking every read and write against the table. Running VACUUM FULL on a large table in production can take the service down for anything from minutes to hours.

pg_repack rebuilds the table in the background and takes a short lock (a few milliseconds) only at the very last moment, when it swaps them over. It is effectively the only way to remove Bloat without service interruption.

How pg_repack Works, and Its Constraints

pg_repack works internally as follows.

1. It creates a temporary table with the same structure as the target table
2. It installs triggers so that INSERT/UPDATE/DELETE on the original table are also applied to the temporary table
3. It copies the data from the original table into the temporary table
4. It synchronizes the changes that occurred during the copy
5. It takes a short AccessExclusiveLock and swaps the file names
6. It deletes the old file

The constraints require caution.

• A PRIMARY KEY or a UNIQUE NOT NULL index is required
• Additional disk space equal to the size of the original table is needed while it runs
• It cannot run at the same time as DDL work (ALTER TABLE and the like)
• Care is needed if the target table has a publication or triggers

Production Execution Procedure

# 1. Install pg_repack (on the server)
# Debian/Ubuntu
sudo apt-get install postgresql-17-repack

# RHEL/CentOS
sudo yum install pg_repack_17

# 2. Install the extension (in the database)
psql -d mydb -c "CREATE EXTENSION IF NOT EXISTS pg_repack;"

# 3. Check disk space (at least 2x the size of the target table is needed)
psql -d mydb -c "SELECT pg_size_pretty(pg_total_relation_size('orders'));"

# 4. Repack a single table (indexes included)
pg_repack -d mydb -t orders --no-superuser-check --wait-timeout=60

# 5. Repack only a specific index
pg_repack -d mydb -i idx_orders_created_at --no-superuser-check

# 6. Repack a whole schema (caution: takes a long time)
pg_repack -d mydb -s public --no-superuser-check --wait-timeout=120

# 7. Verify afterwards
psql -d mydb -c "
SELECT
    relname,
    pg_size_pretty(pg_table_size(oid)) AS table_size,
    pg_size_pretty(pg_indexes_size(oid)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relname = 'orders';
"

Failure Cases and Recovery Procedures

Case 1: An Old Replication Slot Blocking Autovacuum

If a Replication Slot used by logical replication is left inactive for a long time, VACUUM cannot clean up Dead Tuples created after the point that slot references. While the slot's restart_lsn stays in the past, Dead Tuples accumulate without limit.

The symptoms are as follows.

• autovacuum runs but n_dead_tup does not go down
• An inactive Slot shows up in pg_stat_replication
• WAL files are not removed either, and disk usage climbs sharply

The response procedure is as follows.

-- Check for inactive Replication Slots
SELECT
    slot_name,
    slot_type,
    active,
    age(xmin) AS slot_xid_age,
    age(catalog_xmin) AS catalog_xid_age,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_lag
FROM pg_replication_slots
WHERE NOT active;

-- Drop unnecessary Slots (run only after checking)
SELECT pg_drop_replication_slot('inactive_slot_name');

Case 2: A Bulk UPDATE Causing a Bloat Explosion

When a batch job UPDATEs tens of millions of rows at once, a huge number of Dead Tuples appears in an instant. If the next batch runs before Autovacuum has caught up, Bloat grows exponentially.

The prevention strategy is as follows.

• Split a bulk UPDATE into chunks of 10,000 to 100,000 rows and put a short pause between batches
• Run a manual VACUUM before and after the batch job
• Apply aggressive autovacuum settings to tables dedicated to batch work

Recovery Procedure Checklist

1. Check for and terminate long-running transactions
2. Check for and clean up inactive Replication Slots
3. Diagnose the current Bloat state precisely (pgstattuple)
4. Check free disk space (pg_repack needs 2x the table size)
5. Run pg_repack (a low-traffic window is recommended)
6. Review and adjust the Autovacuum parameters
7. Check that monitoring alerts are configured

Operational Cautions

Managing Long-Running Transactions

Long-running transactions are VACUUM's worst enemy. VACUUM cannot clean up Dead Tuples created after the snapshot of an open transaction.

-- Find transactions running for 5 minutes or more
SELECT
    pid,
    usename,
    state,
    age(backend_xid) AS xid_age,
    now() - xact_start AS xact_duration,
    now() - query_start AS query_duration,
    LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
    AND xact_start IS NOT NULL
    AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

-- Check for the idle in transaction state (the most dangerous)
SELECT
    pid,
    usename,
    state,
    now() - state_change AS idle_duration,
    LEFT(query, 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
    AND now() - state_change > interval '10 minutes';

The idle_in_transaction_session_timeout setting lets you terminate transactions that have been idle beyond a set time automatically.

Cleaning Up Prepared Transactions

A Prepared Transaction, used in 2-phase commit (Two-Phase Commit), causes exactly the same problem as a long transaction. While an old Prepared Transaction remains, VACUUM cannot clean up Dead Tuples.

-- Check for old Prepared Transactions
SELECT
    gid,
    prepared,
    owner,
    database,
    now() - prepared AS age
FROM pg_prepared_xacts
ORDER BY prepared;

Essential Metrics for a Monitoring Dashboard

The VACUUM-related metrics you must monitor in a production environment are as follows.

With a Prometheus + Grafana combination, the standard pattern is to collect these metrics using postgres_exporter and set up threshold-based alerts.

References

• Routine Vacuuming | PostgreSQL Docs
• VACUUM Command | PostgreSQL Docs
• Autovacuum Configuration | PostgreSQL 17
• MVCC Freezing | Postgres Professional
• Deep Dive PostgreSQL VACUUM | Google Cloud
• Tuning Autovacuum | CYBERTEC
• Never Run VACUUM FULL | pganalyze