PostgreSQL storage internals

Why does my Postgres database get slow for no reason?

You just deployed the same code that ran fine in staging. Queries that took 200ms suddenly take 5 seconds. Your database CPU spikes to 90%. You check the queries — they haven't changed. You check the data size — no new rows. Yet every sequential scan now requires scanning 40% more disk pages than yesterday.

The answer lives inside Postgres itself, in how it stores rows, how it cleans them up, and how it decides between scanning an index or the table. This is about MVCC, dead tuples, and autovacuum — the three forces that secretly control your database's speed.

Part 1: MVCC — Why dead tuples exist

PostgreSQL never overwrites a row in place. When you run an UPDATE, it doesn't change the row — it marks the old row as dead and writes a new row version to disk. This is MVCC: Multi-Version Concurrency Control.

Why? Because Postgres gives every query a snapshot of the data from the moment it started. A long-running report query that takes 30 minutes needs to see a consistent view of the table — even if 1,000 UPDATEs happen during the query. If Postgres overwrote rows in place, that report would see torn data. MVCC lets readers and writers work without locking each other.

The tradeoff: old row versions don't get deleted immediately. They sit on disk, invisible to new queries but still consuming space. These are dead tuples.

Play with the visualizer below. Watch what happens to live and dead tuple counts as you INSERT, UPDATE, and DELETE. Then press VACUUM to reclaim the space.

Understanding the simulator

• Live tuple: the current, valid version of a row. Queries return these. They have xmax=0 (nothing supersedes them yet).
• Dead tuple: a row version that has been updated or deleted but the space hasn't been reclaimed. Invisible to new queries but still on disk.
• Bloat ratio: (dead tuples / total tuples). Higher than 50% and your table is noticeably inefficient.
• VACUUM: Postgres' garbage collector. It scans the table, marks dead tuples as free space, and allows future INSERTs to reuse that space.

In production, you never run VACUUM manually — autovacuum does it automatically in the background. But autovacuum can fall behind on high-churn tables.

Part 2: Autovacuum — The janitor that can't keep up

Autovacuum wakes up periodically and looks for tables that need cleaning. It uses a formula to decide when to clean:

On a 100-row table, autovacuum waits for 70 dead tuples. On a 10 million row table, it waits for 2,000,050 dead tuples. This default is terrible for large tables.

What makes it worse: autovacuum has only 3 workers by default and it's throttled to avoid hammering your disk. If your UPDATE rate is high enough, autovacuum can't keep up — dead tuples pile up faster than they're cleaned.

Run the simulation below. Try increasing the UPDATE rate while keeping the default scale factor. Watch autovacuum trigger, but dead tuples still climb. This is production table bloat.

Fixing autovacuum in production

When you see postgresql.dead_tuples climbing and postgresql.autovacuum.running constantly 1, autovacuum is losing. The fix is per-table tuning:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 1
);

This makes autovacuum trigger at 1% bloat (not 20%) and backs off less aggressively. On high-churn tables like events or audit_log, this is often necessary.

Monitor postgresql.autovacuum.running, postgresql.dead_tuples, and the ratio (bloat_pct). Set up alerts: if bloat_pct > 30% for 5 minutes, page on-call.

Part 3: Why your index gets ignored

Dead tuples don't just waste space — they also slow down index performance. But there's another reason Postgres ignores your index entirely: the query planner's cost model.

Postgres doesn't guess whether an index is faster. It calculates. For every query, it estimates the cost of:

• Sequential Scan: read every page of the table sequentially. Fast for small selectivity (many matching rows). No random I/O.
• Index Scan: use the index to find matching rows, then fetch them from the heap. Fast for high selectivity (few matching rows). Lots of random I/O.

The planner picks whichever has lower cost. The issue: on HDD, random I/O is ~4× slower than sequential I/O. On SSD, it's ~1.1×. If your server has the wrong random_page_cost setting, the planner makes the wrong choice.

Even worse: if your table has high bloat (40% dead tuples), sequential scans read more pages than necessary. This can flip the planner's decision — it switches to index scans even when sequential scan would be faster if the table weren't bloated.

Use the visualizer below. Set a large table and low selectivity (e.g., 500k rows, 0.5% match). Watch the planner choose index scan. Now increase selectivity to 2% — the planner flips to sequential scan. At what point does it flip? That's your crossover selectivity.

The cost model in real queries

Run EXPLAIN on a slow query. You'll see:

EXPLAIN SELECT * FROM users WHERE status = 'active';

Seq Scan on users  (cost=0.00..45000.00 rows=50000)
  Filter: (status = 'active')

That cost=0.00..45000.00 is Postgres saying: "Full scan is going to cost about 45,000 units." The planner weighed this against the index scan alternative and chose the lower number.

If your index isn't being used, EXPLAIN tells you why. Either:

1. The selectivity is too high — too many rows match, so the index fetch cost is worse than sequential scan
2. The table is so bloated that sequential scan reads nearly the entire table anyway
3. Your random_page_cost is wrong for your storage (should be ~1.1 for SSD, 4.0 for HDD)

The fix is almost never "add an index." It's usually: run VACUUM, tune autovacuum, or fix your query selectivity.

Putting it together: a production incident

Your metrics show:

• postgresql.dead_tuples on the orders table: 45 million (climbing)
• postgresql.autovacuum.running: constantly 1
• Query times: 500ms → 8 seconds in the last hour

Here's what happened:

1. Batch job started updating 500k rows/min. Updates create dead tuples.
2. Autovacuum triggered (45M > threshold) but can't keep up with the update rate.
3. Dead tuples pile up. Bloat reaches 35%.
4. Sequential scans now read 35% more pages than necessary.
5. Your application's index scan on user_id now looks more expensive relative to sequential scan (because the table is bigger).
6. Planner flips to sequential scan. You're now doing full table scans instead of index scans.
7. 8 second queries.

The fix:

-- Quick fix: manual vacuum
VACUUM ANALYZE orders;

-- Long-term: tune autovacuum for this table
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_delay = 0
);

Queries drop back to 200ms. Root cause: table structure didn't change, queries didn't change, data didn't change — but invisible disk bloat made the planner's best guess wrong.

What to monitor

In your Dashboard metrics, track:

• postgresql.dead_tuples per table — alert if climbing above baseline
• postgresql.autovacuum.running — constantly 1 is a red flag
• Bloat ratio (dead / (dead + live)) — alert if >30%
• postgresql.table.hot_updates — low ratio means lots of index thrashing on updates
• Query times per table — a slow query might just be high bloat, not bad code

When you see a correlation between rising dead tuples and slower queries, the answer isn't usually more CPU or more connections. It's autovacuum tuning.

One more thing

The default Postgres tuning is conservative — it prioritizes not breaking things over performance. For a fresh install on modest hardware, it's fine. But any table that gets more than 1000 updates/minute needs custom settings.

Check your autovacuum_max_workers (default: 3). In Patroni clusters with many tables, only 3 workers means some tables don't get cleaned at all. Consider raising it to 6+ for busy clusters.

And measure your random_page_cost. If you're on SSD, the default 4.0 is costing you index usage. Set it to 1.1 and re-ANALYZE:

ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();
ANALYZE;  -- rebuild planner stats

Your index usage will improve immediately.

Summary

PostgreSQL is fast when its storage is clean. MVCC is incredible for concurrency, but it creates dead tuples. Autovacuum cleans them, but it's tuned conservatively. When you hit scale, autovacuum can't keep up. Bloat builds. The planner's cost model — which is rock solid when data is clean — now makes suboptimal choices. Your queries slow down for no apparent reason.

The fix isn't more hardware. It's understanding what's happening and tuning three things: autovacuum_vacuum_scale_factor, autovacuum_vacuum_cost_delay, and random_page_cost.

Monitor dead tuples. Watch autovacuum. Understand the cost model. Your database will thank you.