MVCC and Dead Tuples

-- A simple UPDATE creates a dead tuple
UPDATE orders SET status = 'shipped' WHERE order_id = 42;
-- The old row (status = 'pending') is now dead but still physically on disk

-- A DELETE also creates a dead tuple
DELETE FROM sessions WHERE expires_at < now();
-- Those rows are invisible but still consuming space

SELECT
  schemaname || '.' || relname AS table_name,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum,
  last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 15;

      table_name       | n_live_tup | n_dead_tup | dead_pct |      last_autovacuum
-----------------------+------------+------------+----------+---------------------------
 public.events         |   4823901  |    892410  |   15.6   | 2025-01-15 03:22:14+00
 public.sessions       |    312044  |    187220  |   37.5   | 2025-01-15 03:18:01+00
 public.audit_log      |   1205890  |     54320  |    4.3   | 2025-01-15 02:45:33+00
 public.orders         |    890122  |     12045  |    1.3   | 2025-01-15 03:20:55+00

How Vacuum Works

-- Run vacuum on a specific table
VACUUM orders;

-- Vacuum and update planner statistics in one pass
VACUUM ANALYZE orders;

-- See exactly what vacuum is doing
VACUUM VERBOSE orders;

INFO:  vacuuming "public.orders"
INFO:  table "orders": found 12045 removable, 890122 nonremovable row versions
       in 8823 out of 9150 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 4582901
         12045 rows are dead but not yet removable
         CPU: user: 0.18 s, system: 0.05 s, elapsed: 0.42 s

HOT Updates: Reducing the Vacuum Workload

-- Reserve 20% of each page for HOT updates
ALTER TABLE orders SET (fillfactor = 80);

-- Check HOT update ratio for your tables
SELECT
  schemaname || '.' || relname AS table_name,
  n_tup_upd,
  n_tup_hot_upd,
  round(n_tup_hot_upd::numeric / nullif(n_tup_upd, 0) * 100, 1) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 100
ORDER BY n_tup_upd DESC
LIMIT 10;

     table_name     | n_tup_upd | n_tup_hot_upd | hot_pct
--------------------+-----------+---------------+---------
 public.sessions    |   2841022 |       2105600 |   74.1
 public.orders      |    654890 |        412300 |   63.0
 public.users       |    123456 |         98700 |   79.9

Autovacuum Configuration

The Trigger Formula

dead_tuples > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * n_live_tup)

Tuning for High-Churn Tables

-- Trigger vacuum after 1% dead tuples instead of 20%
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000
);

-- For a table with 10M rows, this triggers at ~101,000 dead tuples
-- instead of the default ~2,000,050

Cost-Based Throttling

-- Show current autovacuum cost settings
SELECT name, setting, short_desc
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;

              name                | setting |                 short_desc
----------------------------------+---------+---------------------------------------------
 autovacuum                       | on      | Starts the autovacuum subprocess.
 autovacuum_max_workers           | 3       | Sets the maximum number of autovacuum processes.
 autovacuum_naptime               | 60      | Time between autovacuum runs.
 autovacuum_vacuum_cost_delay     | 2       | Cost delay for autovacuum (ms).
 autovacuum_vacuum_cost_limit     | -1      | Cost limit for autovacuum (-1 uses vacuum_cost_limit).
 autovacuum_vacuum_scale_factor   | 0.2     | Fraction of table size before autovacuum.
 autovacuum_vacuum_threshold      | 50      | Minimum number of dead tuples before autovacuum.

-- Per-table: let autovacuum work harder on this table
ALTER TABLE events SET (
  autovacuum_vacuum_cost_delay = 0,
  autovacuum_vacuum_cost_limit = 10000
);

aside positive Snowflake Postgres tunes autovacuum automatically based on your workload patterns. The platform monitors table bloat, dead tuple ratios, and transaction ID age — adjusting vacuum aggressiveness without manual intervention. You can observe vacuum activity through Snowflake Postgres Insights.

VACUUM FULL vs Regular VACUUM

-- Regular vacuum: safe, non-blocking
VACUUM orders;

-- VACUUM FULL: blocks everything, use only when desperate
VACUUM FULL orders;
-- All queries against 'orders' will block until this completes!

When to Consider VACUUM FULL

-- Check actual bloat with pgstattuple
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  table_len,
  tuple_count,
  dead_tuple_count,
  round(dead_tuple_len::numeric / table_len * 100, 1) AS dead_pct,
  round(free_space::numeric / table_len * 100, 1) AS free_pct
FROM pgstattuple('orders');

 table_len  | tuple_count | dead_tuple_count | dead_pct | free_pct
------------+-------------+------------------+----------+----------
 7495680000 |      890122 |            12045 |      0.1 |     42.3

Alternatives to VACUUM FULL

-- pg_repack: online table rewrite without exclusive locks
-- (run from command line, not SQL — requires extension installed)
-- pg_repack --table orders --no-superuser-check -d mydb

Transaction ID Wraparound

How Vacuum Prevents Wraparound

-- Check transaction ID age for each database
SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  round(age(datfrozenxid)::numeric / 2000000000 * 100, 1) AS pct_to_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

  datname   |  xid_age   | pct_to_wraparound
------------+------------+-------------------
 analytics  |  189234012 |               9.5
 production |   45002341 |               2.3
 template1  |   45002300 |               2.3

The Danger Thresholds

-- Find tables closest to triggering aggressive vacuum
SELECT
  schemaname || '.' || relname AS table_name,
  age(relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind = 'r'
  AND age(relfrozenxid) > 100000000
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

What Blocks Vacuum from Freezing

-- Find long-running transactions that may block vacuum
SELECT
  pid,
  now() - xact_start AS xact_duration,
  state,
  left(query, 60) AS query_preview
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
ORDER BY xact_start ASC
LIMIT 5;

Monitoring Vacuum Activity

Currently Running Vacuums

-- What's vacuum doing right now?
SELECT
  p.pid,
  a.query,
  p.datname,
  p.relid::regclass AS table_name,
  p.phase,
  p.heap_blks_total,
  p.heap_blks_scanned,
  round(p.heap_blks_scanned::numeric / nullif(p.heap_blks_total, 0) * 100, 1) AS pct_done,
  p.num_dead_tuples
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON a.pid = p.pid;

  pid  |     table_name     |     phase      | heap_blks_total | heap_blks_scanned | pct_done | num_dead_tuples
-------+--------------------+----------------+-----------------+-------------------+----------+-----------------
 14523 | public.events      | scanning heap  |         1203450 |            892100 |     74.1 |          234501

Tables That Need Attention

-- Tables where autovacuum is falling behind
SELECT
  schemaname || '.' || relname AS table_name,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze,
  autovacuum_count,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
  AND round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) > 10
ORDER BY n_dead_tup DESC;

When to Worry

A Comprehensive Health Check

-- One query to rule them all: overall vacuum health
WITH vacuum_stats AS (
  SELECT
    schemaname || '.' || relname AS table_name,
    pg_total_relation_size(relid) AS total_bytes,
    n_live_tup,
    n_dead_tup,
    n_tup_upd + n_tup_del AS churn,
    last_autovacuum,
    autovacuum_count,
    age(relfrozenxid) AS xid_age
  FROM pg_stat_user_tables
  JOIN pg_class ON pg_class.oid = relid
  WHERE n_live_tup > 1000
)
SELECT
  table_name,
  pg_size_pretty(total_bytes) AS size,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  churn,
  autovacuum_count AS vacuums,
  xid_age,
  CASE
    WHEN round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) > 20 THEN 'BLOATED'
    WHEN xid_age > 500000000 THEN 'XID_WARNING'
    WHEN last_autovacuum IS NULL AND n_dead_tup > 10000 THEN 'NEVER_VACUUMED'
    ELSE 'OK'
  END AS status
FROM vacuum_stats
ORDER BY
  CASE
    WHEN round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) > 20 THEN 1
    WHEN xid_age > 500000000 THEN 2
    ELSE 3
  END,
  n_dead_tup DESC
LIMIT 20;

Conclusion

Related Resources

---

---

---