Databases · 3 min read

Why your Postgres table is bigger than its data

MVCC never updates a row in place. Every UPDATE leaves a dead tuple behind, and VACUUM is the only thing that cleans it up. Here is how to see the bloat and keep it in check.

On this page

You run SELECT count(*) on a table, do the maths on row size, and the table on disk is three times bigger than it should be. Nothing is broken. That is MVCC doing its job, and autovacuum not quite keeping up.

Updates are really inserts

Postgres uses multi-version concurrency control. Each row version (a tuple) carries two transaction IDs: xmin, the transaction that created it, and xmax, the one that deleted or replaced it.

  • An UPDATE writes a new tuple and sets xmax on the old one
  • A DELETE only sets xmax; the bytes stay on the page
  • Readers pick the version visible to their snapshot, so nobody blocks
CREATE TABLE t (id int PRIMARY KEY, v text);
INSERT INTO t VALUES (1, 'a');
SELECT ctid, xmin, xmax, * FROM t;   -- (0,1)
UPDATE t SET v = 'b' WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM t;   -- (0,2): a new tuple

The ctid moved from (0,1) to (0,2). The first tuple is still on page 0, just invisible to new snapshots. It is now a dead tuple.

Seeing the dead tuples

pg_stat_user_tables tracks live and dead tuples per table, and when vacuum last ran.

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

A hot queue or session table with 40% dead tuples is common. Sequential scans read every one of those dead bytes, and indexes keep pointing at them until they are cleaned.

What VACUUM actually does

  • Plain VACUUM marks dead tuples’ space as free for reuse and updates the visibility map
  • It does not shrink the file; new rows fill the holes instead
  • VACUUM FULL rewrites the table compactly, but holds an ACCESS EXCLUSIVE lock
  • Tools like pg_repack rebuild online when you really need the disk back

Vacuum also freezes old transaction IDs. Skip it long enough and you approach transaction ID wraparound, which is the one vacuum problem that can stop writes.

Tuning autovacuum for hot tables

Autovacuum triggers when dead tuples exceed threshold + scale_factor × rows. The default scale factor is 0.2, so a 50 million row table waits for 10 million dead rows.

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 1000,
  autovacuum_vacuum_cost_limit   = 2000
);

Per-table settings beat global ones: only the churn-heavy tables need aggressive vacuuming. Also watch for long-running transactions; an idle BEGIN from three hours ago keeps every newer dead tuple alive, no matter how often vacuum runs.

SELECT pid, now() - xact_start AS age, state, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

Takeaways

  • Every UPDATE and DELETE leaves a dead tuple; bloat is the normal state, not a bug
  • Check n_dead_tup in pg_stat_user_tables before blaming the planner
  • Lower autovacuum_vacuum_scale_factor on large, hot tables
  • Kill idle-in-transaction sessions; they pin dead tuples for everyone

Keep reading