Dead Tuples, Bloat Formation, Measuring Bloat, and Repack/Rewrite Tradeoffs

Learning outcomes

ServiceHub deletes historical staging rows and repeatedly updates queue records. The logical row count falls, yet the table file does not fall proportionally. That is not automatically corruption: MVCC produces dead tuple versions, VACUUM makes space reusable, and physical file shrinkage is a separate operation with different locking and rewrite costs.

1. Bloat is not one catalog column

An updated/deleted tuple can remain physically present until it is no longer visible to any relevant snapshot and cleanup can reclaim it. After ordinary VACUUM, that space is generally available for later tuples in the same table, even if the operating-system file remains large. Therefore “file size minus live data” can include useful reserve/free space, alignment/page overhead, dead versions, and other structures—not one universally correct bloat number.

2. Create controlled churn

DROP TABLE IF EXISTS app.ch08_churn;CREATE TABLE app.ch08_churn (    churn_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    status text NOT NULL,    payload text NOT NULL);INSERT INTO app.ch08_churn(status, payload)SELECT 'active', repeat(md5(g::text), 8)FROM generate_series(1, 20000) AS g;ANALYZE app.ch08_churn;SELECT count(*) AS live_rows,       pg_size_pretty(pg_relation_size('app.ch08_churn'::regclass)) AS heap_main,       pg_size_pretty(pg_indexes_size('app.ch08_churn'::regclass)) AS indexes;

UPDATE app.ch08_churnSET status = 'processed', payload = payload || '-v2'WHERE churn_id % 2 = 0;UPDATE app.ch08_churnSET payload = payload || '-v3'WHERE churn_id % 3 = 0;DELETE FROM app.ch08_churnWHERE churn_id <= 12000;ANALYZE app.ch08_churn;

3. Compare logical state, estimated dead tuples, and physical size

SELECT count(*) AS visible_rowsFROM app.ch08_churn;SELECT n_live_tup, n_dead_tup, last_vacuum, last_autovacuum,       vacuum_count, autovacuum_countFROM pg_stat_user_tablesWHERE relid = 'app.ch08_churn'::regclass;SELECT pg_size_pretty(pg_relation_size('app.ch08_churn'::regclass)) AS heap_main,       pg_size_pretty(pg_table_size('app.ch08_churn'::regclass)) AS table_total,       pg_size_pretty(pg_indexes_size('app.ch08_churn'::regclass)) AS indexes;

The 8,000 remaining rows are exact for this snapshot. n_dead_tup is statistical evidence, not an exact byte count. Physical sizes show allocated files, not the amount that a future insert can reuse.

4. Optional supplied pgstattuple evidence

pgstattuple is supplied with PostgreSQL but is an extension, not core SQL. Install it only in the disposable/local database if available. PostgreSQL 18 restricts execution by default to superusers or roles with pg_stat_scan_tables privileges.

CREATE EXTENSION IF NOT EXISTS pgstattuple;SELECT table_len, tuple_count, dead_tuple_count,       round(dead_tuple_percent::numeric, 2) AS dead_pct,       free_space,       round(free_percent::numeric, 2) AS free_pctFROM pgstattuple('app.ch08_churn'::regclass);SELECT *FROM pgstattuple_approx('app.ch08_churn'::regclass);

The exact function scans the relation and can be expensive on large production tables. The approximate form can use visibility/free-space metadata to skip work. Neither should become a high-frequency query without a cost decision.

5. Ordinary VACUUM: reclaim for reuse first

VACUUM (VERBOSE, ANALYZE) app.ch08_churn;SELECT n_live_tup, n_dead_tup, last_vacuumFROM pg_stat_user_tablesWHERE relid = 'app.ch08_churn'::regclass;SELECT pg_size_pretty(pg_relation_size('app.ch08_churn'::regclass)) AS heap_main_after_vacuum;

The dead-tuple estimate should fall after successful cleanup on this quiet table. The main file may remain similar because ordinary VACUUM primarily makes dead space reusable. It can truncate empty pages at the physical end when conditions allow, so do not teach “VACUUM can never shrink a file” either.

6. VACUUM FULL and CLUSTER are rewrites, not stronger ordinary VACUUM

VACUUM FULL rewrites the table into a new compact file and requires an ACCESS EXCLUSIVE lock. It also needs extra disk space while the new copy is built. CLUSTER rewrites a table according to an index, also taking strong locking; the physical ordering is a one-time result, not automatically preserved by future writes.

SELECT pg_relation_size('app.ch08_churn'::regclass) AS before_full;VACUUM (FULL, ANALYZE) app.ch08_churn;SELECT pg_relation_size('app.ch08_churn'::regclass) AS after_full;

After deleting most rows, after_full will commonly be smaller, but do not encode an exact reduction. On a real table, preflight lock tolerance, concurrent transactions, replication/WAL capacity, temporary disk headroom, recovery objectives and change window.

-- Optional in this disposable lab: physically order by the primary-key index.CLUSTER app.ch08_churn USING ch08_churn_pkey;ANALYZE app.ch08_churn;

7. External repack-style tools solve a different operational problem

Third-party tools such as pg_repack can rebuild tables/indexes with a smaller blocking window than an offline rewrite by coordinating shadow structures and catch-up changes. They are not PostgreSQL core, are not required by this course, and introduce extension/library/version/privilege/WAL/disk/HA compatibility requirements. Treat them as change-management software, not a magic “online VACUUM FULL” command.

8. Heap bloat and index bloat are related but not identical

Every non-HOT update can leave obsolete index entries as well as heap tuple versions. Plain VACUUM can clean dead index entries according to its index-cleanup decisions, but a table with a compact heap can still have an index whose structure is larger than desirable. Conversely, rebuilding an index does not compact the heap.

SELECT pg_size_pretty(pg_relation_size('app.ch08_churn'::regclass)) AS heap_main,       pg_size_pretty(pg_table_size('app.ch08_churn'::regclass)) AS table_plus_toast,       pg_size_pretty(pg_indexes_size('app.ch08_churn'::regclass)) AS all_indexes,       pg_size_pretty(pg_total_relation_size('app.ch08_churn'::regclass)) AS total;SELECT indexrelid::regclass AS index_name, idx_scanFROM pg_stat_user_indexesWHERE relid = 'app.ch08_churn'::regclassORDER BY indexrelid::regclass::text;

Use index-specific diagnostics before choosing REINDEX. A table rewrite and an index rebuild solve different physical problems, even if a rewrite may also rebuild associated indexes as part of the operation.

9. A long snapshot can make “VACUUM is not working” the wrong diagnosis

VACUUM cannot remove a version that could still be visible to an old snapshot. Before escalating maintenance, look for long transactions and idle-in-transaction sessions, then understand why they exist. Killing sessions blindly can violate application work; the point is to correlate cleanup horizon with transaction behavior.

SELECT pid, usename, application_name, state,       xact_start, backend_xmin, wait_event_type, wait_eventFROM pg_stat_activityWHERE datname = current_database()  AND xact_start IS NOT NULLORDER BY xact_start;

Chapter 09 will go deeper into vacuum horizons, freezing and wraparound. Here the production lesson is simply that storage symptoms can originate in concurrency/session lifecycle rather than an undersized maintenance command.

10. Observe rewrite progress and budget temporary resources

On a sufficiently large table, a second session can observe VACUUM FULL or CLUSTER progress through pg_stat_progress_cluster. The tiny lab may finish before you can observe it. In production, a rewrite also needs temporary disk headroom and can generate substantial WAL; replicas and archive pipelines must absorb that work.

SELECT pid, relid::regclass AS relation, command, phase,       heap_blks_total, heap_blks_scanned, heap_tuples_scanned, heap_tuples_writtenFROM pg_stat_progress_clusterWHERE relid = 'app.ch08_churn'::regclass;

A progress view answers “what phase/how far is this operation?” It does not prove the maintenance window, replication lag, or disk capacity is safe. Those require system-level monitoring and preflight estimates.

11. Wrong approach: schedule VACUUM FULL for every large table

A recurring rewrite can hide the real cause: autovacuum thresholds that do not fit the workload, long-running snapshots blocking cleanup, bursty delete patterns, unnecessary indexes, poorly chosen fillfactor, or application lifecycle that repeatedly grows then empties the table. Routine autovacuum/plain VACUUM is the normal mechanism; rewrites are exceptional maintenance decisions.

SELECT relname, n_live_tup, n_dead_tup,       last_autovacuum, autovacuum_count,       last_autoanalyze, autoanalyze_countFROM pg_stat_user_tablesWHERE relid = 'app.ch08_churn'::regclass;SELECT pid, usename, state, xact_start, backend_xminFROM pg_stat_activityWHERE datname = current_database()  AND xact_start IS NOT NULLORDER BY xact_start;

12. Cleanup and checks

DROP TABLE IF EXISTS app.ch08_churn;-- Do not DROP EXTENSION pgstattuple if another lesson/operator uses it.

13. Production judgment and bridge

Separate “cleanup is keeping up,” “table has reusable space,” and “file must be physically shrunk” into different questions. Measure before rewriting. Lesson 5 brings the chapter together by watching the same small heap through insert, HOT update, delete and VACUUM with pageinspect, while repeatedly checking that physical diagnostics never outrank SQL-visible correctness.

Authoritative references

• PostgreSQL 18 — Routine Vacuuming
• PostgreSQL 18 — VACUUM
• PostgreSQL 18 — CLUSTER
• PostgreSQL 18 — pgstattuple
• PostgreSQL 18 — Monitoring Statistics
• PostgreSQL 18 — Progress Reporting