PostgreSQL Deep Dive
by Sergey Okinchuk
Long TransactionsWhy Dead Tuples Exist

Chapter Seven · Vacuum, Bloat, and Freezing

Vacuum, Bloat, and Freezing

Twelve thousand rows in 900 MB, and nobody deleted anything. Five topics take Cartwheel's inventory table apart: what vacuum reclaims and what it never gives back, why the shipped autovacuum defaults were designed for a different database, the two schema decisions that decide whether an update costs one write or four, and the one Postgres failure mode that stops writes cluster-wide with no warning at all.

5 topics

Chapter 6 explained why Postgres keeps several versions of a row and what that buys: readers that never wait for writers, and the last box of strawberries sold exactly once. This chapter is the invoice. Every version the engine kept for a snapshot that has since gone away is still physically on disk, and something has to remove it — not as housekeeping, but as the half of the design that makes the other half affordable.

The evidence has been sitting in the schema since Chapter 1. inventory holds 12,000 live rows and occupies 900 MB, in a table that has never had a DELETE run against it. Three separate causes built that number, and each is a topic here: autovacuum running with defaults chosen for a much smaller database, a throttle calibrated for storage nobody buys any more, and a schema that made every single checkout write four times as much as it needed to. By the end of the chapter the table is 616 kB and the settings that got it there are attached to the table itself, where they survive a restart and a restore.

The last topic is different in kind from everything before it. Bloat is expensive and recoverable; freezing debt is a countdown that gives no signal until the cluster refuses writes for the whole database. It is the one number in this book worth waking someone for, and setting the alert takes five minutes. Chapter 8 then picks up the thread this chapter keeps pulling — that the indexes on a table are a permanent tax on every write and every vacuum, and that some of the ones on orders have never been used at all.

The life of one row version, and who acts at each step
Livethe version current snapshots see
→
Supersededan update writes a new version, the old one stays
→
Deadstill on disk once its snapshot is gone
→
Reclaimedvacuum, the half of the design that pays for the other half
→
Free spacewhat vacuum gives back, and where

Topics in This Chapter

Topic 33
Why Dead Tuples Exist
Vacuum's four jobs, the heap-then-indexes-then-heap shape of a run, and why the index pass dominates on a table with nine of them. What vacuum never does — including returning space to the filesystem — and how to read the log line that means the problem is not vacuum's at all.
Dead Tuples
Topic 34
Autovacuum: What It Does and When It Doesn't
The trigger formula, and what a 20% scale factor means on 40 million rows. Cost-based throttling on NVMe, why three workers is not a throughput setting, and the per-table configuration that is the interface Postgres actually intends you to use.
Autovacuum Tuning
Topic 35
HOT Updates and fillfactor
The two conditions that let an update skip every index on the table, and what it costs when either one fails. How fillfactor reserves the room, how to tell the two failure modes apart from three counters, and the index on on_hand that served no query and taxed every write.
Update Path
Topic 36
Measuring and Fixing Bloat
Estimate queries for the dashboard, pgstattuple before anything irreversible, and pgstatindex for the leaf density vacuum never improves. What VACUUM FULL and pg_repack each lock and need — and the cases where the right move is to leave the bloat alone.
Bloat Control
Topic 37
Transaction ID Wraparound
Why 32-bit ids are compared in a circle, what freezing marks and where the catalogue records it, and the anti-wraparound vacuum that runs whether or not autovacuum is enabled. The failsafe, the exact message that means writes have stopped, and the one query to alert on.
Wraparound