Dead Tuples, Table Bloat, and Long-Running Queries: What PostgreSQL Is Not Telling You

Every UPDATE and DELETE in PostgreSQL creates a dead tuple — a row version that is no longer visible to any transaction but still occupies disk space until a VACUUM reclaims it. This is not a bug; it is the core of MVCC. The problem is that autovacuum, the automatic cleanup process, does not always keep up. When it falls behind, your tables bloat, your indexes degrade, and your queries quietly get slower. Nothing errors. The database just gets fat and slow.

What Are Dead Tuples?

PostgreSQL uses Multi-Version Concurrency Control (MVCC). When you run UPDATE orders SET status = 'shipped' WHERE id = 42, Postgres does not overwrite the existing row. It creates a new row version and marks the old one as dead — invisible to new transactions, but still on disk. DELETE does the same: the row is marked dead, not removed. The space is only reclaimed when VACUUM runs.

You can see the accumulation directly:

SELECT
  schemaname || '.' || relname AS table_name,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup + n_dead_tup > 0
       THEN ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2)
       ELSE 0 END AS dead_ratio,
  last_autovacuum,
  last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;

A dead_ratio above 10–15% on a table that sees heavy writes is a warning sign. Above 25%, you are likely already seeing query plans that scan more pages than necessary — the planner sees a table that is physically larger than its live row count suggests.

Bloat: When Dead Tuples Become Permanent

Dead tuples are temporary if vacuum runs. Bloat is what happens when vacuum does not run often enough, or when long-running transactions prevent it from reclaiming space. The dead tuples pile up, the table's physical file grows, and even after vacuum finally runs, the space is reclaimed for reuse but not returned to the OS. The table stays fat. An index on a bloated table also bloats — more pages to scan, more I/O per lookup, lower cache hit ratio.

You can estimate bloat without installing extensions:

SELECT
  schemaname || '.' || relname AS table_name,
  pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(relname)) AS total_bytes,
  pg_size_pretty(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(relname))) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
       THEN pg_total_relation_size(relid) / n_live_tup
       ELSE 0 END AS avg_row_bytes
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

A table with 100,000 live rows averaging 200 bytes each should be roughly 20 MB. If total_bytes shows 120 MB, that is 100 MB of bloat — six times the expected size. Every sequential scan on that table reads six times more pages than it should.

Which Tables Consume the Most Disk?

Not all tables are equal. A handful of large tables typically dominate disk usage, and they are the ones where bloat hurts the most. The query above already sorts by total_bytes DESC, but for a complete picture including indexes and TOAST:

SELECT
  n.nspname AS schema_name,
  c.relname AS table_name,
  pg_total_relation_size(c.oid) AS total_size_bytes,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size_pretty,
  COALESCE(s.n_live_tup, 0) AS row_count,
  CASE WHEN COALESCE(s.n_live_tup, 0) > 0
       THEN pg_total_relation_size(c.oid) / s.n_live_tup
       ELSE 0 END AS avg_row_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 50;

This is exactly the query PG Monitoring's agent runs on every collection cycle. The top 50 tables by total size (including indexes and TOAST) are pushed to the SaaS, where they appear in the dashboard ranked by disk consumption. You see which tables are growing, which are bloated, and which are both.

Which Tables Are Written the Most?

Write-heavy tables are the ones that accumulate dead tuples fastest. PostgreSQL tracks updates and deletes per table in pg_stat_user_tables:

SELECT
  schemaname || '.' || relname AS table_name,
  n_tup_upd AS updates,
  n_tup_hot_upd AS hot_updates,
  CASE WHEN n_tup_upd > 0
       THEN ROUND(100.0 * n_tup_hot_upd / n_tup_upd, 2)
       ELSE 100 END AS hot_ratio,
  n_tup_del AS deletes,
  (n_tup_upd + n_tup_del) AS total_activity
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC
LIMIT 15;

The hot_ratio column is critical. HOT (Heap-Only Tuple) updates do not touch index entries — they are cheap and do not create index bloat. When hot_ratio drops below 50%, it means most updates are touching indexes too, which means index bloat accumulates faster and autovacuum has more work to do. A common cause: a column that is frequently updated and is part of an index. If you can remove that column from the index (or use a partial index), HOT updates go back up and bloat pressure drops.

Long-Running Queries: Configurable Thresholds

Dead tuples and bloat are slow killers. Long-running queries are the acute symptom — the query that holds a snapshot, blocks vacuum from reclaiming dead tuples, and causes the bloat in the first place. PostgreSQL exposes active queries in pg_stat_activity:

SELECT
  pid,
  usename,
  application_name,
  state,
  EXTRACT(EPOCH FROM (NOW() - query_start)) AS duration_sec,
  LEFT(query, 4000) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '3 seconds'
  AND pid <> pg_backend_pid()
ORDER BY query_start
LIMIT 10;

The INTERVAL '3 seconds' threshold is arbitrary — you choose what "long" means for your workload. PG Monitoring's agent collects queries above 3 seconds by default, and the SaaS lets you configure alert thresholds per instance:

• Warning: fires when the count of long-running queries exceeds 5 (pile-up detection).
• Critical: fires at 15+ concurrent long queries.
• Single-query alert: fires when one query runs longer than 30 minutes (configurable via LONG_QUERY_ALERT_SECONDS). This opens a dedicated incident with the PID, user, application, and query text — and auto-resolves when the query finishes.

The threshold is per-organization and per-instance, so a reporting database that routinely runs 60-second analytical queries does not trigger the same alerts as an OLTP database where 3 seconds is already a problem.

The Feedback Loop

These three problems are connected:

1. A long-running query holds a snapshot.
2. Vacuum cannot reclaim dead tuples from before that snapshot.
3. Dead tuples accumulate → table bloats → sequential scans get slower.
4. Slower scans mean queries run longer → more snapshots held → more bloat.

Breaking the loop requires seeing all three signals together: which tables have the most dead tuples, which are the largest on disk, which are written the most, and which queries are running long enough to block vacuum. PG Monitoring collects all four on every cycle and correlates them in a single dashboard, so you do not have to run four separate queries and mentally join the results.

What to Do About It

• Autovacuum tuning: if last_autovacuum is more than a day old on a write-heavy table, lower autovacuum_vacuum_scale_factor for that table (e.g. ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05) instead of the default 0.2).
• HOT updates: if hot_ratio is low, check whether an indexed column is being updated unnecessarily. Removing it from the index can dramatically reduce bloat.
• Long queries: if a single query is blocking vacuum, consider killing it (pg_terminate_backend(pid)) or adding a statement timeout to the application's connection string.
• Severe bloat: if a table is already 5× its expected size, VACUUM FULL or pg_repack is needed to physically shrink it. VACUUM FULL locks the table; pg_repack does not.