PostgreSQL Bloat: Guide to MVCC, VACUUM, and Reclaiming Space

Understanding PostgreSQL Bloat

When you delete half your table, the file doesn't shrink. After 100,000 updates, the table doubles in size even though no new data was added. This happens because of how PostgreSQL handles updates and deletes internally.

How PostgreSQL Handles Updates

PostgreSQL does not overwrite a row when you update or delete it. Instead, it keeps the old version marked as dead and writes a brand-new version next to it. Both exist on disk at the same time.

This behavior comes from Multi-Version Concurrency Control (MVCC). In traditional databases, reading a row while it's being written would lock it, preventing access. MVCC solves this by keeping the old version of a row available for readers while the new version is being written. If someone is updating a row and another person tries to read it, the old version is still accessible.

The downside is that these dead versions (dead tuples) accumulate on every delete and update, taking up disk space. This is called bloat.

Setting Up the Environment

Start the PostgreSQL environment by clicking the Start PostgreSQL button. Then open VS Code and the integrated terminal by clicking the three lines, then Terminal, then New Terminal. Maximize the terminal for more space.

Verify PostgreSQL is running:

sudo systemctl status postgresql

Connect to the database:

sudo -u postgres psql app_db

Disabling Autovacuum

Before measuring bloat, disable autovacuum to see the effect of manual operations:

ALTER TABLE activity_log SET (autovacuum_enabled = false);

Checking Initial Table State

The activity_log table is write-heavy with a lot of data. Check the row count:

SELECT count(*) FROM activity_log;

You should see 100,000 rows.

Use the built-in pg_stat_user_tables to see live and dead tuples:

SELECT n_live_tup, n_dead_tup, last_autovacuum 
FROM pg_stat_user_tables 
WHERE relname = 'activity_log';

The result shows 100,000 live tuples and zero dead tuples.

Check the table size:

SELECT pg_size_pretty(pg_total_relation_size('activity_log'));

The size is 7,344 kilobytes.

Updating All Rows

Update every row in the table:

UPDATE activity_log SET action = 'updated';

Now check the statistics again:

SELECT n_live_tup, n_dead_tup, last_autovacuum 
FROM pg_stat_user_tables 
WHERE relname = 'activity_log';

You'll see 100,000 dead tuples and 100,000 live tuples. The table size has doubled to 14 megabytes.

Deleting Half the Rows

Delete all rows with even IDs:

DELETE FROM activity_log WHERE id % 2 = 0;

Check the statistics:

SELECT n_live_tup, n_dead_tup, last_autovacuum 
FROM pg_stat_user_tables 
WHERE relname = 'activity_log';

Live count dropped to about 50,000, but dead tuples increased further.

Get a detailed view of wasted space:

SELECT 
    n_dead_tup,
    round(100 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_tuple_percent,
    round(100 * (pg_total_relation_size('activity_log') - pg_relation_size('activity_log')) / pg_total_relation_size('activity_log'), 2) AS free_percent
FROM pg_stat_user_tables 
WHERE relname = 'activity_log';

Dead tuple percent is 23 and free percent is 46 – pure wasted space.

Vacuuming

Run a standard vacuum:

VACUUM activity_log;

Check the statistics again – dead tuples are gone. But the table size hasn't changed. Vacuum marks dead space as reusable, so the next insert will fill those gaps instead of growing the file. The space is not returned to the operating system.

To reclaim disk space, run a full vacuum:

VACUUM FULL activity_log;

Now check the size:

SELECT pg_size_pretty(pg_total_relation_size('activity_log'));

The size now reflects only the live data. Vacuum full rewrites the entire table from scratch and reclaims disk space, but it locks the table while running. No reads or writes are allowed during this time. Use it sparingly in production, only when bloat is severe enough to justify the lock.

Autovacuum Configuration

PostgreSQL has autovacuum enabled by default, which triggers vacuum when a certain threshold of dead tuples accumulates – typically 20% of the row count.

View autovacuum settings:

SELECT name, setting 
FROM pg_settings 
WHERE name LIKE 'autovacuum%' 
ORDER BY name;

Two key settings:

• autovacuum_vacuum_scale_factor – 0.2 (20%). If dead tuples exceed 20% of the table, autovacuum kicks in.
• autovacuum_analyze_scale_factor – 0.1 (10%). If inserts or changes exceed 10%, analyze runs on the table.

If your table generates bloat faster than the default threshold, lower the scale factor from 0.2 to 0.1 or whatever suits your workload. This tuning is something you'll handle in production based on your specific needs.