PostgreSQL MVCC and Vacuum: Why Your 10GB Table Uses 50GB

Your PostgreSQL table uses five times its expected disk space because of something called "dead tuples."

We'll get into that later on, but for now, the only thing you should know is that these are old row versions left behind by every UPDATE or DELETE operation. These rows are no longer visible to any transaction.

But first things first.

I always say PostgreSQL is, in a word, elegant because it relies on an elegant system called Multi-Version Concurrency Control (MVCC). This system basically ensures that readers never block writers and writers never block readers.

However, there's a hidden cost here. This system creates outdated rows that will keep eating up your storage, and, before you know it, you'll end up with a 10GB table that requires five times the disk space. It'll be fully bloated and much slower.

I know. It sounds confusing.

So, if you're having trouble understanding how these internal systems really work, your tables will eventually start to bloat, and your database queries will begin to slow down.

But fear not. I'm here to help.

Where Do These Hidden Rows Come From?

Hidden rows are created because an UPDATE in PostgreSQL does not modify a row in its original place. Instead, it writes an entirely new version of the row and marks the old version as dead.

Similarly, a DELETE operation simply marks the existing row as dead without writing a new version at all.

Every time a row version is created or modified, it carries two specific transaction identifiers (IDs) to track its lifecycle:

• xmin: This identifies the specific transaction that created this version of the row.
• xmax: This identifies the transaction that deleted or superseded this version. If the row is still live, this value will stay at zero.

Here's a neat diagram that puts this into perspective.

So, to explain further, when you run a query, PostgreSQL compares these transaction values against your current transaction's snapshot to determine exactly which row versions you are allowed to see.

This snapshot is essentially a frozen picture of the database at one specific moment. The rules that generate this snapshot depend on your transaction isolation level, which dictates whether you see the data exactly as it was when your statement began, or if you can see other users' commits while you are still reading.

What Are Dead Tuples?

Old row versions that are completely invisible to every active transaction are officially classified as dead tuples.

This design creates a hidden, but massive, implication. Even a basic UPDATE operation, such as UPDATE users SET last_login = NOW() WHERE id = 1, creates a dead tuple.

If this UPDATE runs every time a user logs in, and you process 10,000 logins (or more) per minute, you are generating 10,000 dead tuples per minute on that single table.

Without aggressive vacuum tuning, this will cause the table to bloat incredibly fast.

Here's how to check dead tuple counts.

-- Check dead tuple counts
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

How Does the Database Clean Up this Mess?

The database cleans up the mess using a process called VACUUM, which reclaims the disk space occupied by dead tuples so it can be used again.

VACUUM operates in two distinct modes, and they behave very differently from one another.

What is Regular VACUUM?

Regular VACUUM is the standard cleanup method, and it works like this:

• It marks the disk space taken up by dead tuples as reusable for future data within the same table.
• It does not return the recovered space to the server's operating system.
• It runs quietly alongside your normal read and write operations because it does not hold exclusive locks.
• It updates the visibility map, which is crucial because index-only scans depend on it.
• It updates the free space map, allowing new data inserts to easily find and reuse the reclaimed space.

What is VACUUM FULL?

VACUUM FULL, on the other hand, is a much heavier operation:

• It rewrites the entire table from scratch and physically compacts the data.
• It successfully returns the reclaimed space to the operating system.
• It holds a strict, exclusive lock on the table for the entire duration of the process.
• Because of this lock, it is essentially unusable on a production table during regular business hours.

Okay. That's all fine and dandy. However, in practical, everyday use, you will almost always rely on regular VACUUM.

You should reserve VACUUM FULL strictly for scheduled maintenance windows on severely bloated tables.

If you ever need an alternative to VACUUM FULL, you should look into pg_repack.

This is a helpful extension that completely rewrites a table without holding an exclusive lock for the full duration of the operation. It is an absolute lifesaver on production systems where you desperately need to reclaim your disk space but simply cannot afford any downtime.

Why Do Open Transactions Stall the Cleanup Process?

Open transactions stall the cleanup process because VACUUM is only allowed to reclaim dead tuples that are completely invisible to all currently active transactions.

Did you think there wasn't a catch?

Here's an example. If a transaction was opened two hours ago and stays open, every single dead tuple created across the database during those two hours is protected and safe from being vacuumed.

The oldest active transaction ID establishes a strict baseline, or floor, and the VACUUM process cannot touch any data newer than that baseline. This is actually one of the most common causes of unexplained database bloat.

The usual culprits behind this issue include:

• Idle transactions: This happens when a developer opens psql (the PostgreSQL interactive terminal), runs a BEGIN command to start a transaction, and simply walks away. That single open transaction pins back the entire vacuum horizon.
• Long-running reports: A heavy dashboard query that takes 30 minutes to run will hold the vacuum process back for that entire 30-minute window.
• Connection pool leaks: This occurs when an application successfully opens transactions but fails to close them when it encounters an error path.

Here's how to find long-running transactions that are blocking the VACUUM operation.

-- Find long-running transactions blocking vacuum
SELECT pid, usename, state, query_start,
       age(backend_xid) AS xid_age,
       now() - query_start AS query_duration
FROM pg_stat_activity
WHERE state != 'idle'
  AND backend_xid IS NOT NULL
ORDER BY query_start ASC
LIMIT 10;

To prevent this from happening, you should set the idle_in_transaction_session_timeout parameter to automatically kill idle transactions after a specific threshold. Set it to five minutes, for example. That single adjustment will prevent the majority of accidental VACUUM stalls.

How Does Autovacuum Decide When to Run?

Autovacuum decides when to run based on a specific formula that triggers the cleanup only when a table's dead tuples exceed a base threshold combined with a set percentage of its live rows.

So, autovacuum is PostgreSQL's built-in background process that automatically runs the VACUUM operation for you.

It relies on this exact calculation:

The threshold equals the autovacuum_vacuum_threshold plus the autovacuum_vacuum_scale_factor multiplied by the number of live tuples (n_live_tup).

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup

Using the system's default settings, which are a base threshold of 50 and a scale factor of 0.2, a table containing 10,000 rows will only be vacuumed after it accumulates 2,050 dead tuples (50 plus 0.2 times 10,000).

However, there is a major scaling problem when we use these percentage-based thresholds.

A massive table with 100 million rows will not trigger a vacuum until it holds 20 million dead tuples. This means that hundreds of gigabytes of wasted bloat can just sit uselessly on your disk before the system decides to do anything about it.

How Do You Tune Autovacuum for High-Write Tables?

You can tune autovacuum for high-write tables by manually intervening to lower the scale factor and threshold, which forces the cleanup process to run much more aggressively.

Because the default settings are quite conservative, tables that receive a heavy volume of writes need these custom, manual adjustments to stay healthy.

Here's how to set autovacuum to activate after 1% instead of 20%.

ALTER TABLE hot_table SET (
    autovacuum_vacuum_scale_factor = 0.01,    -- 1% instead of 20%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.005
);

Beyond the basic scale factor and threshold, there are three other important parameters you should know about:

• autovacuum_max_workers: This defaults to 3. You should raise this number when many large tables are competing for vacuum time. Because each worker can only vacuum one table at a time, having just 3 workers for 50 bloated tables creates a massive backlog.
• autovacuum_vacuum_cost_delay: This dictates exactly how aggressively the vacuum runs. Lower numbers mean a more aggressive cleanup. The default is 2 milliseconds (ms), but you should try setting it to 0 for workloads with high write activity. This is the single most impactful parameter you can change here.
• autovacuum_naptime: This determines how often the launcher wakes up to look for new work. The default is 1 minute.

Should Every Table Use the Same Vacuum Strategy?

No, every table should not use the same strategy because tables with drastically different write rates require entirely different tuning approaches.

In other words, not all tables are created equal. You need to find your hottest tables, the ones taking the highest number of writes per second, and tune those individually.

Meanwhile, tables with low write rates can comfortably stick to the default database settings.

I recommend a highly practical approach, and that is to query your statistics (pg_stat_user_tables) on a weekly basis, pull the top 10 tables with the most dead tuples (n_dead_tup), and apply per-table tuning for any table that consistently shows up on that list.

What is the Worst-Case Scenario for Unmanaged Transaction IDs?

The worst-case scenario is a catastrophic failure known as "transaction ID wraparound," where PostgreSQL completely shuts down to prevent data corruption because it can no longer tell whether a row was created in the past or in the future.

This happens because PostgreSQL uses 32-bit transaction identifiers, which inevitably wrap around after roughly 4 billion transactions. Without regular vacuuming, old transaction IDs become ambiguous.

VACUUM prevents this disaster by "freezing" old transaction IDs, permanently marking them as definitively in the past. The autovacuum_freeze_max_age setting, which defaults to 200 million transactions, is the specific trigger for this aggressive anti-wraparound vacuum.

Here's how to monitor wraparound risk.

-- Monitor wraparound risk
SELECT datname,
       age(datfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age') AS freeze_max_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

If your transaction ID age (xid_age) approaches 2 billion, you have a very serious problem on your hands, and you must monitor this metric closely.

This anti-wraparound vacuum is fierce, it cannot be interrupted, and it consumes serious Input/Output (I/O) and Central Processing Unit (CPU) resources.

If it unexpectedly starts on a high-traffic table during peak business hours, your system will feel the impact. The only way to avoid this is to keep your regular vacuum running frequently enough that the emergency anti-wraparound process never even has to trigger.

Do Indexes Suffer From the Same Bloating Problem?

Yes, they do. Indexes suffer from the exact same bloating problem because when a table row is marked as dead, the corresponding index entries pointing to it are not removed immediately.

As a result, indexes accumulate these dead entries over time, which noticeably increases query scan times and wastes valuable disk space.

Regular VACUUM does clean up the obsolete index entries that point to dead table rows, but if it cannot keep up the pace with your write volume, the index bloat will simply keep growing.

The REINDEX command can rebuild an index from scratch, but it holds a strict lock.

Fortunately, REINDEX CONCURRENTLY (available since PostgreSQL version 12) allows you to rebuild an index without blocking incoming writes.

Here's how to estimate index bloat.

-- Estimate index bloat
SELECT
    schemaname, tablename, indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS index_scans
FROM pg_stat_user_indexes
JOIN pg_index ON pg_index.indexrelid = pg_stat_user_indexes.indexrelid
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

If an index is much larger than you would expect for the number of live rows it holds, it is probably bloated.

Compare it directly against the table's size. An index that is larger than its own table is a very strong warning signal. Once this bloat starts costing you query time, use the EXPLAIN ANALYZE command to confirm the issue.

You should look for sequential scans on tables that ought to be using an index, and check the Buffers: output to spot artificially inflated I/O activity.

How Do You Keep an Eye on Database Bloat?

You keep an eye on database bloat by regularly running specific queries that track dead tuples, monitor autovacuum performance, and compare actual table sizes against estimated live data sizes.

These practical queries will allow you to track bloat accurately and ensure that your background cleanup work is successfully keeping up with the database's demands.

Here's how to check tables that are most in need of VACUUM.

SELECT schemaname, relname, last_vacuum, last_autovacuum,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Here's how you check if autovacuum is keeping up.

SELECT relname,
       last_autovacuum,
       autovacuum_count,
       n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Finally, here's how to check table bloat and compare the actual size to the estimated live data size.

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       n_live_tup,
       n_dead_tup,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

What Are the Key Takeaways for Managing PostgreSQL Bloat?

The major key takeaway is that table bloat is merely a symptom, and fixing the root cause requires you to make sure that your vacuum process runs frequently and fast enough for your specific workload.

Keep these final points in mind:

• MVCC creates dead tuples on every single update and delete by design.
• VACUUM reclaims this dead tuple space. Autovacuum does it automatically, but the defaults are often too conservative.
• Long-running transactions pin the vacuum horizon. Set idle_in_transaction_session_timeout to prevent accidental stalls.
• Tune autovacuum aggressively for high-write tables by lowering the scale factor, increasing the maximum workers, and reducing the cost delay.
• Closely monitor dead tuple counts and transaction ID age. Preventing transaction ID wraparound is critical.
• Only use heavy tools like VACUUM FULL, pg_repack, or REINDEX CONCURRENTLY during safe maintenance windows.