The Desi Architect
Notes / Docker Sandboxes / PostgreSQL MVCC Dead Tuple Bloat & Long-Running Transaction Freezing
Docker Sandboxes · Tier 2: Data & Scale · Lab 28

PostgreSQL MVCC Dead Tuple Bloat & Long-Running Transaction Freezing

Watch a table of 1,000 rows grow to 18 times its size without gaining a single row. A long-running transaction stops VACUUM from cleaning up dead row versions. See the proof in the VACUUM output, see what plain VACUUM and VACUUM FULL each give back, and see which timeout would have prevented it.

Time35 minutes
StackPostgreSQL 16Ports 15446
MemoryAbout 80 MB
Before thisLab 05SQL Transaction Isolation Levels &
Download lab (.zip) View steps
Verified runWindows, Mac, LinuxBash and PowerShell
  1. 1PrepareWhat you will learn
  2. 2PredictGuess before you run
  3. 3RunStep by step
  4. 4ObserveWhat you should see
  5. 5Break itChange one thing
  6. 6Clean upLeave nothing running
On this page
  1. 00 / What You Will Learn
  2. 01 / Why It Matters
  3. 02 / What We Are Building
  4. 03 / Words You Need
  5. 04 / Predict First
  6. 05 / Step by Step
  7. 06 / What You Should See
  8. 07 / Look Inside
  9. 08 / Break It, Fix It
  10. 09 / In Real Projects
  11. 10 / Interview Corner
  12. 11 / Clean Up

00 /What You Will Learn

By the end you will be able to
  • Explain MVCC and why an UPDATE leaves a dead tuple.
  • Explain what the oldest snapshot (xmin) does to VACUUM.
  • Read VACUUM (VERBOSE) and its "dead but not yet removable" line.
  • Tell VACUUM (space reused) from VACUUM FULL (disk returned, table locked).
  • Say which timeout stops an idle transaction and which one is needed for a long active query.

Before you start

Finish Lab 05 (SQL Transactions and Isolation) first. You should know what a transaction and a snapshot are. Docker Desktop must be running.