MVCC and Bloat Causes
Understand multi-version concurrency control, why dead tuples accumulate, and how long transactions cause bloat.
MVCC and Bloat Causes is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is MVCC?
Multi-Version Concurrency Control. Instead of locking, PostgreSQL keeps multiple versions of a row. Readers see a consistent snapshot; writers create new versions without blocking readers.
How an UPDATE Works
An UPDATE doesn't change the row in place:
- Mark the old row version as "dead" at transaction T
- Write a new version
- Other transactions see whichever version their snapshot allows
Why Bloat
Dead versions accumulate. The table grows even if its row count is stable. Without cleanup, queries scan progressively more dead rows.
When VACUUM Reclaims Space
VACUUM marks dead rows as reusable (within the table file). It does NOT shrink files unless they're fully empty at the end. VACUUM FULL rewrites the table — exclusive lock, slow.
Autovacuum
PostgreSQL runs autovacuum in the background. It triggers when dead rows pass a threshold:
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
-- vacuum when dead_rows > 50 + 0.2 * total_rowsBloat-Causing Workloads
- Heavy UPDATE traffic on small / hot tables
- Big DELETE batches (need vacuum to free space)
- Long-running transactions block vacuum (hold snapshots)
- Idle-in-transaction sessions accumulate dead rows on busy tables
Diagnosing Bloat
The pgstattuple extension gives exact figures:
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('orders');
-- table_len, tuple_count, dead_tuple_count, free_space, etc.
SELECT * FROM pgstatindex('orders_user_id_idx');Long Transactions Block Vacuum
VACUUM can only clean rows older than the oldest active transaction. A 4-hour idle-in-transaction session means 4 hours of unclaimed dead rows.
SELECT pid, state, xact_start, NOW() - xact_start AS duration
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY duration DESC NULLS LAST;Wraparound Protection
Transaction IDs are 32-bit. If autovacuum can't keep up, the cluster faces "wraparound" and goes into safety mode (forced VACUUM). Monitor:
SELECT datname, age(datfrozenxid) FROM pg_database
ORDER BY age(datfrozenxid) DESC;Logical Deletion ≠ Physical
DELETE marks rows dead; the space is reclaimable only by VACUUM. Bulk DELETEs followed by no vacuum leave huge dead-row pools.
Hot Updates
If you update only non-indexed columns and a free spot exists on the same page, PostgreSQL does a HOT (Heap-Only Tuple) update — no index modification, less bloat.
Reducing Bloat
- Keep transactions short
- Avoid wide UPDATEs on indexed columns (HOT can't kick in)
- Tune autovacuum aggressively on hot tables
- Use pg_repack to rewrite without long locks
Recap
MVCC enables concurrency at the cost of dead row buildup.
- VACUUM cleans dead rows
- Autovacuum is essential — don't disable it
- Long transactions block cleanup
- Diagnose with pgstattuple
Quick Check
Why doesn't an UPDATE shrink the table even when one column changes?
Frequently asked questions
Is the “MVCC and Bloat Causes” lesson free?
Yes — the full text of “MVCC and Bloat Causes” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “MVCC and Bloat Causes”?
Understand multi-version concurrency control, why dead tuples accumulate, and how long transactions cause bloat. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “MVCC and Bloat Causes” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.