MVCC and bloat: why UPDATE isn't what you think

Up to here you worked with statistics — what the planner knows about your data. Now you switch to a physical problem: the space your data occupies on disk grows faster than the actual content. A table with 1 million live rows can physically weigh as if it had 4 million, and nobody notices until the queries become inexplicably slow.

The cause is MVCC, the concurrency model PostgreSQL uses. When you do an UPDATE, PostgreSQL doesn't modify the row in-place — it creates a new version of the row and marks the old version as "dead." The same with DELETE: it doesn't free the space, it just marks the row as dead. Those dead rows are bloat: space occupied by data nobody is going to read anymore but that's still there.

In this capsule you're going to understand why MVCC works this way (it's not a bug, it's a deliberate feature), how bloat grows, and how to detect it before it takes down your database. Capsule 06 teaches you to prevent it with tuned autovacuum; capsule 07 teaches you to repair it with pg_repack. But all of that requires that you first understand what it exactly is.

What MVCC does

MVCC stands for "Multi-Version Concurrency Control." The idea is simple: when a transaction modifies a row, it doesn't overwrite the current version; it creates a new parallel version. The two coexist for a while.

Why? So that readers and writers don't block each other. Without MVCC, when someone updates a row, readers have to wait (or read inconsistent data). With MVCC:

• The writer creates the new version.
• Readers that started before the UPDATE keep seeing the old version (consistency for their transaction).
• Readers that start afterward see the new version.
• Nobody waits for anybody.

This is the heart of why PostgreSQL scales well on mixed workloads (many concurrent reads with writes). But the cost is accumulated bloat: the old versions stay physically in the table until VACUUM (or autovacuum) cleans them up.

A row's lifecycle

You're going to see it step by step. Create a simple table with tracking of physical IDs:

DROP TABLE IF EXISTS demo;
CREATE TABLE demo (
    id INTEGER PRIMARY KEY,
    value TEXT
);

INSERT INTO demo (id, value) VALUES (1, 'original');

-- See the ctid (physical identifier of the tuple)
SELECT ctid, id, value FROM demo;
-- ctid  | id | value
-- (0,1) |  1 | original

ctid = (0, 1) means "page 0, slot 1" — the physical position of the row on disk.

Now update it:

UPDATE demo SET value = 'updated_v1' WHERE id = 1;
SELECT ctid, id, value FROM demo;
-- ctid  | id | value
-- (0,2) |  1 | updated_v1

ctid changed to (0, 2). The old version at (0, 1) is still physically there but no longer visible — it's a dead tuple.

Another update:

UPDATE demo SET value = 'updated_v2' WHERE id = 1;
UPDATE demo SET value = 'updated_v3' WHERE id = 1;
SELECT ctid, id, value FROM demo;
-- ctid  | id | value
-- (0,4) |  1 | updated_v3

Now there are 3 dead tuples at (0, 1), (0, 2), (0, 3), plus the live one at (0, 4). The physical space occupied is 4 times the space of the actual content.

Those dead tuples:

• Don't show up in normal SELECTs (filtered by visibility).
• Are physically in the page, occupying space.
• Seq Scan "reads" them (passes over them) — more pages to traverse = a slower scan.
• Index Scan too: the index can have pointers to dead tuples (HOT updates help, but not in every case).

VACUUM is the operation that cleans this up: it marks the space as reusable. It doesn't release it to the operating system (the table still weighs the same on disk) but it lets future INSERTs reuse the space.

Detecting bloat

There are two levels of detection: fast with built-in stats, and deep with an extension.

Fast detection with pg_stat_user_tables

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC;

Typical output:

relname  | n_live_tup | n_dead_tup | dead_pct | last_vacuum | last_autovacuum
events   |    1234567 |     345678 |    21.87 | NULL        | 2026-05-08 03:14:22
orders   |     500000 |      89234 |    15.14 | NULL        | 2026-05-08 04:01:15
sessions |     200000 |     180000 |    47.37 | NULL        | 2026-05-07 22:30:11

dead_pct is the key metric. Rules of thumb:

• <5%: healthy, autovacuum is keeping up.
• 5-20%: acceptable for active tables, but monitor.
• 20-40%: bloat accumulating. Consider lowering autovacuum_vacuum_scale_factor per-table.
• >40%: severe bloat. After applying a fix, consider pg_repack to recover physical space.

n_live_tup and n_dead_tup are activity counters that PostgreSQL increments on the fly. They're approximations — for exact counts you need pgstattuple.

Precise detection with pgstattuple

For deep analysis you need an extension:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('orders');

Output:

table_len     | 234567 KB
tuple_count   | 500000
tuple_len     | 178500 KB
tuple_percent | 76.10
dead_tuple_count | 89234
dead_tuple_len   | 31200 KB
dead_tuple_percent | 13.30
free_space    | 5234 KB

Reading:

• table_len: total physical size of the table.
• tuple_percent: % of the space occupied by live tuples. If it drops below 80% on stable tables, there's significant bloat.
• dead_tuple_percent: % occupied by dead tuples. >15% is a sign of a problem.
• free_space: free space within pages (future INSERTs will use it).

pgstattuple scans the whole table — it's expensive on large tables. For recurring analysis use pgstattuple_approx, which is faster but estimated.

SELECT * FROM pgstattuple_approx('orders');

Why bloat degrades performance

Three concrete effects.

1. More pages to read

A table with 30% bloat occupies 1.43x more pages than its content. Seq Scan reads 1.43x more data. Index Scan can touch more pages too because the live rows are scattered among dead tuples.

"Clean" table: 1M rows, 18,432 pages → Seq Scan reads ~145MB
Table with 30% bloat: 1M live rows, 26,358 pages → Seq Scan reads ~210MB

2. Less effective cache

shared_buffers (PostgreSQL's cache) has a fixed size. If your tables have bloat, the same amount of live data occupies more pages, and fewer pages fit in the cache. Hit rate drops, disk reads go up.

3. Index Scan with dead pointers

Indexes have pointers to ctids. If a row was updated (new ctid), the index can keep pointing to the old ctid (depends on the type of update — HOT vs non-HOT). When Index Scan follows that pointer, it finds a dead tuple and has to keep searching. More silent work.

HOT updates (Heap-Only Tuple updates) optimize this case: if the updated column isn't indexed and the new version fits in the same page, PostgreSQL avoids updating the index. But it requires that there's free space in the page, which decreases with bloat. High bloat reduces HOT updates → more index updates → more work.

When bloat matters most

Not all tables are the same. Bloat is more problematic in these cases:

Tables with many writes and few reads

sessions, event_logs, temp_data — tables where each row is updated several times or deleted quickly. Bloat accumulates naturally.

Tables that grow and then shrink

Imagine a notifications table that grows to 10M rows and then you delete 80% (cleanup of read notifications). The physical space is still the peak (10M rows). Queries keep reading all those pages even though only 2M have live data.

Indexes with bloat

Indexes also accumulate bloat. When a row is updated non-HOT, the index can have old entries that VACUUM has to clean up. REINDEX CONCURRENTLY rebuilds an index without locking the table — useful when you see an index 2x larger than it should be.

SELECT
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Tables with UPDATEs on indexed columns

Every time you update a column that has an index, PostgreSQL must update the index (it can't use HOT). This creates bloat in the table and in the index. Tables with frequent updates on indexed columns are the ones that suffer most.

The UPDATE case that surprises everyone

A common pattern that creates massive bloat without anyone realizing it:

# Cron job that runs every 5 minutes
def update_user_last_seen():
    db.execute(
        "UPDATE users SET last_seen = NOW() WHERE id = ANY(:ids)",
        {"ids": active_user_ids}
    )

If you have 10,000 active users and the cron runs every 5 minutes, that's 2.88M updates per day. Each UPDATE creates a dead tuple. If last_seen is indexed (common), it also creates bloat in the index.

After a month: 86 million dead tuples. Over 10k live rows. The table weighs thousands of times more than its actual content.

Solution (beyond autovacuum tuning):

1. Consider whether last_seen needs to be exact to the second or whether bucketing to 5/15 minutes is acceptable.
2. Keep a separate table user_activity with fewer indexes and more aggressive autovacuum.
3. Track in Redis (see guide #10) and persist to PostgreSQL only periodically.

Design decisions that generate many UPDATEs on the same rows have a large operational impact. It's worth questioning them in code review.

Traps and common mistakes

1. Confusing "disk space" with "live rows".

SELECT pg_total_relation_size('orders') gives you the physical size. SELECT COUNT(*) FROM orders gives you live rows. If the table weighs 5GB and has 1M rows, it doesn't mean "5KB per row" — it could be 1KB per live row + 4GB of bloat.

2. Thinking that DELETE frees space.

DELETE only marks tuples as dead. The physical space stays occupied until VACUUM runs. If you do DELETE FROM orders WHERE created_at < '2020-01-01' expecting the table to shrink, it's not going to happen. You need VACUUM to reuse the space (not release it to the OS) or pg_repack to recover physical space.

3. Assuming VACUUM FULL is always the solution.

VACUUM FULL rewrites the table and releases space to the OS. But it takes an AccessExclusiveLock (locks everything, reads and writes) and can take hours on large tables. In production it's almost never the right answer. Capsule 07 covers pg_repack, which does the same thing online.

4. Not checking bloat in indexes, only in tables.

Indexes accumulate bloat too. A clean table with a bloated index still has the problem. Inspect both: pg_relation_size('table') and pg_relation_size('idx_table_x').

5. Configuring autovacuum to "very aggressive" globally to avoid bloat.

Aggressive autovacuum spends CPU and I/O. Globally it can affect the performance of normal queries. The right strategy is per-table: aggressive autovacuum only where you have high bloat. Capsule 06 covers this.

6. Using TRUNCATE when you only wanted to delete old rows.

TRUNCATE releases space to the OS instantly (it's very fast) but deletes the entire table and you can't do a selective DELETE. If your intention was to delete old records while keeping recent ones, you need DELETE + VACUUM. TRUNCATE is for "empty the whole table."

Exercise: measure and monitor bloat

Setup:

DROP TABLE IF EXISTS bloat_demo;
CREATE TABLE bloat_demo (
    id SERIAL PRIMARY KEY,
    name TEXT,
    counter INTEGER DEFAULT 0
);

CREATE INDEX idx_bloat_name ON bloat_demo(name);

-- Insert 10000 rows
INSERT INTO bloat_demo (name)
SELECT 'user_' || generate_series FROM generate_series(1, 10000);

ANALYZE bloat_demo;

Step 1: measure the initial state.

SELECT
    pg_size_pretty(pg_total_relation_size('bloat_demo')) AS total,
    pg_size_pretty(pg_relation_size('bloat_demo')) AS table_only;

SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables WHERE relname = 'bloat_demo';

Write down: what size is it? How many live/dead tuples?

Step 2: create bloat artificially.

-- Update all the rows 5 times
UPDATE bloat_demo SET counter = counter + 1;
UPDATE bloat_demo SET counter = counter + 1;
UPDATE bloat_demo SET counter = counter + 1;
UPDATE bloat_demo SET counter = counter + 1;
UPDATE bloat_demo SET counter = counter + 1;

Step 3: measure again.

SELECT
    pg_size_pretty(pg_total_relation_size('bloat_demo')) AS total,
    pg_size_pretty(pg_relation_size('bloat_demo')) AS table_only;

SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables WHERE relname = 'bloat_demo';

Question: did the table grow? How many dead tuples?

Step 4: run VACUUM (not VACUUM FULL).

VACUUM bloat_demo;

SELECT
    pg_size_pretty(pg_total_relation_size('bloat_demo')) AS total,
    pg_size_pretty(pg_relation_size('bloat_demo')) AS table_only;

SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables WHERE relname = 'bloat_demo';

Question: did the physical size go down? Or did only the dead tuples counter get updated?

Step 5: run VACUUM FULL and compare.

VACUUM FULL bloat_demo;

SELECT pg_size_pretty(pg_total_relation_size('bloat_demo')) AS total;

Question: now did the physical size go down? What's the key difference between VACUUM and VACUUM FULL?

total      | table_only
752 KB     | 432 KB

n_live_tup | n_dead_tup
     10000 |          0

total      | table_only
3528 KB    | 2592 KB    <-- grew ~6x

n_live_tup | n_dead_tup
     10000 |      50000

total      | table_only
3528 KB    | 2592 KB    <-- same physical size

n_live_tup | n_dead_tup
     10000 |          0   <-- dead tuples cleaned up

total      | table_only
752 KB     | 432 KB    <-- back to the initial size

Summary and next step

What you learned:

• MVCC is why UPDATE and DELETE don't free space immediately — they create dead versions that coexist with the live ones.
• Bloat is the space accumulated by dead tuples. It degrades performance via more pages to read, less effective cache, and dead pointers in indexes.
• Detect it with pg_stat_user_tables (fast, approximate) or pgstattuple (exact, more expensive).
• VACUUM cleans up dead tuples without releasing physical space. VACUUM FULL rewrites the table and releases space, but requires an exclusive lock.
• Tables with many UPDATEs on indexed columns are the ones that suffer bloat most.
• Indexes also accumulate bloat. REINDEX CONCURRENTLY rebuilds them without a lock.

Before moving on, you should be able to:

• Explain why UPDATE x SET y = z creates a dead tuple.
• Detect tables with high bloat in your database using pg_stat_user_tables.
• Know that VACUUM ≠ VACUUM FULL and why the difference matters in production.
• Identify code patterns that generate massive bloat (cron jobs with frequent UPDATEs).

In the next capsule you're going to understand autovacuum: the system PostgreSQL uses to clean up bloat automatically. You're going to learn why the defaults are inadequate for large tables, which parameters to tune (autovacuum_vacuum_scale_factor, autovacuum_naptime), and why the right answer is almost always to adjust per-table and not globally.

Resources

1. PostgreSQL Docs — Concurrency Control — official reference for MVCC.
2. PostgreSQL Docs — VACUUM — reference for the command.
3. PostgreSQL Docs — pgstattuple — extension for precise bloat analysis.
4. Bruce Momjian — "MVCC Unmasked" — a deep talk on MVCC.
5. Hubert "depesz" Lubaczewski — On Bloat — analysis with real cases.
6. Citus Data — Postgres Bloat Detection — an operational detection guide.
7. pg_repack documentation — a preview of capsule 07's topic.

Capsule 05 of 08 — Module 7 — Database Performance & Query Tuning Guide