VACUUM, autovacuum and bloat in PostgreSQL

← Back to the section

You've noticed that a table takes up 10 GB on disk, even though the real data in it is three times smaller. Or PostgreSQL suddenly starts slowing down for no obvious reason. Most likely the culprit is bloat — accumulated dead tuples. Let's look at why this happens and how to deal with it.

Why rows aren't deleted immediately

PostgreSQL uses an approach called MVCC (Multi-Version Concurrency Control). The idea is this: when you run an UPDATE or DELETE, the old row isn't erased right away. It's marked as "dead" and stays in the table file.

Why? Because at that moment another transaction may be reading the old version of the data — and must see it consistently. As long as even one open transaction can still see the old row, PostgreSQL isn't allowed to remove it.

As a result, after intensive work with data, dead tuples accumulate in tables. They are exactly what inflates the table size — this is bloat.

What VACUUM does

VACUUM is a cleanup command. It walks through the table and does three things:

Reclaims space from dead tuples. The space isn't returned to the operating system — the table file doesn't shrink. Instead, the freed pages are recorded in the FSM (Free Space Map), and new rows are written there, so the file doesn't keep growing. One exception: completely empty pages at the end of the file are truncated under a brief exclusive lock.

Updates the visibility map. This is a map of pages where all rows are guaranteed to be visible to every transaction. It's needed for an efficient Index Only Scan — without it the planner has to reach into the table itself even for an index-only query.

Prevents XID wraparound. More on this in a separate section below.

VACUUM doesn't block reads and writes — it works with a SHARE UPDATE EXCLUSIVE lock. Only DDL commands like ALTER TABLE will have to wait.

What this looks like on a model

The mechanics are visible without a database. A page here is six slots for row versions: UPDATE marks the old version dead and appends the new one to a free slot; cleanup frees dead versions nobody can see.

live example

public class VacuumDemo {

    static final int[] slot = new int[6];
    static final long[] deletedBy = new long[6];
    static long xid = 100;

    public static void main(String[] args) {
        for (int row = 1; row <= 4; row++) {
            insert(row);
        }
        update(2);
        update(4);
        System.out.println("after UPDATE of rows 2 and 4:  " + page());
        System.out.println("VACUUM while transaction 101 is open: freed " + vacuum(101) + "  " + page());
        System.out.println("VACUUM after it has finished:         freed " + vacuum(xid + 1) + "  " + page());
        insert(5);
        System.out.println("after INSERT of row 5:         " + page());
        System.out.println("slots in the file, before and after: " + slot.length);
    }

    static void insert(int row) {
        int free = 0;
        while (slot[free] != 0) {
            free++;
        }
        slot[free] = row;
    }

    static void update(int row) {
        long now = ++xid;
        for (int i = 0; i < slot.length; i++) {
            if (slot[i] == row && deletedBy[i] == 0) {
                deletedBy[i] = now;
            }
        }
        insert(row);
    }

    static int vacuum(long horizon) {
        int freed = 0;
        for (int i = 0; i < slot.length; i++) {
            if (deletedBy[i] != 0 && deletedBy[i] < horizon) {
                slot[i] = 0;
                deletedBy[i] = 0;
                freed++;
            }
        }
        return freed;
    }

    static String page() {
        StringBuilder page = new StringBuilder();
        for (int i = 0; i < slot.length; i++) {
            page.append(slot[i] == 0 ? "[  ·]" : deletedBy[i] == 0 ? "[ " + slot[i] + " ]" : "[ " + slot[i] + "†]");
        }
        return page.toString();
    }
}

The two cleanup runs are the point. While transaction 101 is open nothing is freed: it could still see the deleted versions — that is how a long transaction piles up bloat. Once it finishes, both dead slots are freed and the next insert takes one of them: the file did not grow.

Three forms of the command

-- Plain VACUUM: reclaims dead tuples, doesn't lock the table
VACUUM order_doc;

-- With analyze: also refreshes statistics for the planner
VACUUM ANALYZE order_doc;

-- Full table rewrite: returns space to the OS, but locks everything
VACUUM FULL order_doc;

VACUUM and VACUUM ANALYZE are safe to run at any time.

VACUUM FULL is a fundamentally different operation. It fully rewrites the table from scratch and takes an ACCESS EXCLUSIVE lock while doing so. This means: while VACUUM FULL is running, the table is unavailable for both reads and writes. On large tables this can take hours. In production it's almost never used.

A non-blocking alternative to VACUUM FULL is the pg_repack utility (or pg_squeeze). It rebuilds the table in the background without getting in the way of the application:

pg_repack -d mydb -t order_doc

VACUUM ANALYZE is worth running manually after a bulk UPDATE or DELETE — don't wait for the automation to kick in: the planner can work with stale statistics for a long time.

autovacuum — automatic cleanup

PostgreSQL runs VACUUM automatically through the autovacuum daemon. It watches every table and triggers cleanup once enough dead tuples have accumulated.

The default trigger threshold:

dead tuples > 50 + 0.2 × number of live rows

On a table of one million rows autovacuum will kick in at ~200,000 dead tuples. For most tables that's enough.

But for large and busy tables the default 20% is too much. The setting can be configured right on the table:

ALTER TABLE order_doc SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.05
);

Now autovacuum will trigger at ~50,000 dead tuples instead of 200,000 — there will be less bloat.

How to detect bloat

A quick look through the system statistics:

live example

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 10;

What to look at:

• dead_pct > 20% — the table is a candidate for a manual VACUUM.
• last_autovacuum long ago (more than a day on a hot table) — autovacuum isn't keeping up.

For a precise picture there's the pgstattuple extension:

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('order_doc');
-- dead_tuple_percent > 30% — the table is clearly bloated

Indexes bloat too — separately from tables. This is fixed with the REINDEX CONCURRENTLY command (available since PostgreSQL 12) — it doesn't lock the table while rebuilding.

fillfactor — room for updates

By default PostgreSQL fills pages with data all the way (fillfactor = 100). On an UPDATE the updated row most often doesn't fit on the same page and is written to a new one — and an extra entry appears in the indexes.

If a table is updated heavily, it makes sense to leave some room on the pages:

ALTER TABLE order_doc SET (fillfactor = 85);
VACUUM FULL order_doc;  -- applies the new fillfactor (once, in a maintenance window)

With fillfactor = 85 pages are filled only up to 85%. When an UPDATE arrives, the updated row often fits on the same page — this is called a HOT update (Heap-Only Tuple). The index isn't touched in that case, so the load is lower.

When autovacuum can't keep up

There are several situations where autovacuum is working, but dead tuples still keep accumulating.

A long transaction. MVCC forbids deleting rows that even one open transaction can see. If you have a transaction hanging for hours — bloat will grow regardless of autovacuum.

live example

SELECT pid,
       age(now(), xact_start) AS xact_age,
       state,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 10;

If xact_age is more than 5 minutes — it's worth figuring out what's going on there. Typical causes: an unclosed session in the code, a stuck background process.

A good practice is to set the timeout idle_in_transaction_session_timeout = '30s': it automatically closes transactions that were started but are doing nothing.

A stuck replication slot. If a replica or subscriber has fallen far behind, PostgreSQL holds on to the WAL files until they've been read, and the disk fills up. The slot on its own doesn't stop cleanup; that only happens when hot_standby_feedback is enabled on the replica: then it asks the primary to hold back the row versions its own queries still need.

Prepared transactions. The PREPARE TRANSACTION command creates "dangling" transactions that live until an explicit COMMIT PREPARED or ROLLBACK PREPARED.

live example

SELECT * FROM pg_prepared_xacts;

If something is stuck in there — it blocks cleanup the same way a long ordinary transaction does.

autovacuum is turned off. Sometimes it's disabled before a bulk data load and people forget to turn it back on.

live example

SELECT name, setting FROM pg_settings WHERE name = 'autovacuum';

А так — таблицы, которым автовакуум настроили отдельно:

live example

SELECT relname, reloptions
FROM pg_class
WHERE relkind = 'r' AND reloptions::text LIKE '%autovacuum%';

XID wraparound — a critical situation

PostgreSQL has a 32-bit transaction counter (XID). When it exhausts its ~2.1 billion values and "wraps around," the database can confuse old and new transactions. To prevent this, VACUUM "freezes" old rows — marks them as visible to everyone forever.

To check how close you are to the danger mark:

live example

SELECT datname,
       age(datfrozenxid)         AS xid_age,
       2147483647 - age(datfrozenxid) AS xids_left
FROM pg_database;

If xid_age is more than 1.5 billion — autovacuum isn't keeping up, and you need to intervene manually. First PostgreSQL writes increasingly insistent warnings to the log. If they are ignored to the end, writes are not slowed down — they stop: no new transaction can start until cleanup has run. Reads still work, but for the application this is an outage.

In short

• PostgreSQL doesn't delete rows immediately: UPDATE and DELETE leave old versions behind (MVCC), and bloat grows out of them.
• VACUUM reclaims space inside the file, updates the visibility map and freezes old rows against wraparound; the file doesn't shrink, only an empty tail is cut.
• VACUUM FULL does return space to the OS but locks the table completely: in production use pg_repack.
• autovacuum fires at "50 + 0.2 × live rows"; on large tables lower the scale_factor to 0.05.
• Cleanup is held back by long transactions, prepared transactions and a lagging replica with hot_standby_feedback — look for those before tuning settings.
• fillfactor 80–90 gives HOT updates on heavily updated tables; watch age(datfrozenxid) separately — it means writes stop, not slow down.

What to read next

• Indexes in PostgreSQL — how Index Only Scan and the visibility map work.
• EXPLAIN ANALYZE — where the Heap Fetches that VACUUM cures come from in a plan.
• Transaction isolation levels — MVCC and row visibility.
• Monitoring PostgreSQL — pg_stat_user_tables and other metrics.