Vacuum

Snowflake for Developers/Postgres/Vacuum

On this page

Vacuum

Postgres

Elizabeth Christensen

MVCC and Dead Tuples

When someone asks why their Postgres table is 10x larger than the data it actually holds, the answer almost always starts with MVCC.

Postgres uses Multi-Version Concurrency Control so readers and writers don't block each other. Every UPDATE writes a brand-new row version, and every DELETE marks a row as invisible instead of removing it. Transactions that started before the change can still see the old version, and nobody waits on a lock.

The catch is that those old row versions, called dead tuples, stick around until vacuum cleans them up.

Here's how they pile up:

-- 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 dead tuples
DELETE FROM sessions WHERE expires_at < now();
-- Those rows are invisible but still consuming space

Here's a query we use all the time to see where dead tuples are accumulating:

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;

Example output on a busy system:

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

Look at sessions. At 37.5% dead tuples, more than a third of that table's physical storage is invisible garbage, and every sequential scan has to read through it.

Dead tuples cost you in a few concrete ways. The table file grows even though the live data doesn't. Sequential scans get slower because Postgres still reads dead tuples off disk. And indexes keep pointing at dead tuples until vacuum removes those entries, so indexes bloat too.

How Vacuum Works

Regular VACUUM is Postgres's garbage collector. It scans a table, finds dead tuples that no running transaction can still see, and marks that space as reusable for future inserts and updates. It does NOT hand space back to the operating system (apart from truncating fully empty pages at the very end of the table). The file stays roughly the same size, but the space inside it becomes available again.

What regular VACUUM does and doesn't do:

It doesn't take a blocking lock, so reads and writes keep going
It doesn't shrink the file; reclaimed space gets reused internally
It freezes old transaction IDs, which prevents wraparound (more on that below)
It updates the visibility map, which is what makes index-only scans work

-- Run vacuum on a specific table
VACUUM orders;

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

-- See what vacuum is doing
VACUUM VERBOSE orders;

The VERBOSE output tells you what happened. The exact format changes a bit between versions, but it looks something like this:

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
         CPU: user: 0.18 s, system: 0.05 s, elapsed: 0.42 s

Vacuum found and reclaimed 12,045 dead tuples across 8,823 pages. The table is 9,150 pages total, so vacuum scanned most of it. If you see a big number for "cannot be removed yet," something (usually a long-running transaction) is holding back the xmin horizon.

HOT Updates: Reducing the Vacuum Workload

Postgres has an optimization called HOT (Heap Only Tuple) updates. When an UPDATE changes only non-indexed columns and the new tuple fits on the same heap page, Postgres skips creating new index entries. The old tuple gets a redirect pointer to the new one.

HOT matters because those old versions can be pruned during normal page access, without waiting for a full vacuum pass. You can make room for HOT updates by setting a table's fill factor below 100:

-- 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

A hot_pct above 70% is great. Fill factor only affects newly written pages, so existing data won't change until it gets rewritten. For update-heavy tables where you're mostly changing non-indexed columns, a fillfactor of 70 to 90 can cut vacuum work and index bloat a lot.

Autovacuum Configuration

How often should vacuum run? It depends, which is why Postgres has autovacuum: a background launcher that kicks off vacuum workers automatically based on table activity. Leave it on. Turning it off is how people end up reading the wraparound section of this page in a panic.

The Trigger Formula

Autovacuum vacuums a table when:

dead_tuples > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * n_live_tup)

With the defaults (threshold = 50, scale_factor = 0.2):

Table Size (live rows)	Vacuum Triggers After Dead Tuples
1,000	250
10,000	2,050
100,000	20,050
1,000,000	200,050
10,000,000	2,000,050

For a 10M-row table, autovacuum waits until 2 million dead tuples pile up. That's often too late, and the table has already bloated.

Postgres 18 added autovacuum_vacuum_max_threshold (default 100,000,000), which caps that calculated number so a billion-row table doesn't wait for 200 million dead tuples. It helps on very large tables, but at the default it won't change anything for the 10M-row example above. You'll still want per-table tuning for big, busy tables.

Insert-only tables get their own trigger too (autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor, added in Postgres 13), so append-heavy tables still get frozen and have their visibility map maintained.

Tuning for High-Churn Tables

For large, frequently updated tables, lower the scale factor:

-- 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 and Workers

Autovacuum throttles itself so it doesn't flood your I/O. Here are the settings to look at:

-- Show current autovacuum 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.

(Trimmed. You'll see more rows than this, including the insert and freeze settings.)

Changing autovacuum_max_workers used to require a restart. Postgres 18 added autovacuum_worker_slots, which reserves worker slots at startup, so you can now raise autovacuum_max_workers up to that number with a reload. Keep in mind the cost limit is shared across running workers, so more workers without a higher cost limit just means each one goes slower.

On fast storage (SSDs, NVMe), you can usually let autovacuum work harder:

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

Setting cost_delay to 0 removes the sleep between vacuum I/O operations. Vacuum finishes faster but uses more I/O bandwidth. On modern SSDs that's usually fine. On spinning disks, be more conservative.

Postgres 18's asynchronous I/O (io_method, with io_workers controlling the I/O worker pool when it's set to worker) also speeds up vacuum's reads, since vacuum is one of the operations that can use read streams.

Coming in Postgres 19 (currently at release candidate, not GA yet): parallel autovacuum, so autovacuum can use parallel workers to process a table's indexes the way a manual VACUUM (PARALLEL n) already can. That's a real help for big tables with lots of indexes.

Snowflake Postgres runs autovacuum with sensible defaults for its instance sizes, and you can still set per-table storage parameters like the ones above. You can watch vacuum activity and dead tuple counts with the same queries on this page or through Snowflake Postgres Insights.

VACUUM FULL vs Regular VACUUM

This is where people get confused. Regular VACUUM marks dead space as reusable inside the table file. VACUUM FULL rewrites the whole table into a new file, compacting it and releasing space to the OS.

	Regular VACUUM	VACUUM FULL
Locks table?	No (concurrent reads/writes OK)	Yes, ACCESS EXCLUSIVE lock
Shrinks file on disk?	No	Yes
How long?	Seconds to minutes	Minutes to hours (proportional to table size)
Safe for production?	Yes, run it constantly	Rarely, it blocks all access
Space requirement	Minimal	Needs free disk space roughly equal to the table size

-- 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

Most production systems never need VACUUM FULL. Regular vacuum keeps up with normal workloads. Consider it only when a table has extreme bloat (50%+ dead space) from something like a one-time bulk delete, you've confirmed with pgstattuple that the bloat is real and not a statistics artifact, and you can tolerate that table being unavailable while it runs.

-- 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

That 42.3% free_pct means vacuum already reclaimed the space internally and Postgres will reuse it. This table doesn't need VACUUM FULL. Note that pgstattuple reads the whole table, so be careful running it on very large tables during peak hours.

Alternatives to VACUUM FULL

For tables that really do need physical compaction without downtime, the long-standing answer is pg_repack, an extension plus a command-line client:

# pg_repack: online table rewrite without holding an exclusive lock for the whole run
# (run from the shell; the pg_repack extension must be installed in the database)
pg_repack --table orders -d mydb

pg_repack builds a new copy of the table in the background, replays changes made in the meantime, then takes a brief exclusive lock to swap the new table in. Much safer for production than VACUUM FULL.

Postgres 19 (release candidate now, not GA yet) adds a built-in REPACK command, including REPACK CONCURRENTLY for an online rebuild. Once you're on 19, that should cover most of the cases where you'd reach for pg_repack or VACUUM FULL today, with no extension to install.

Transaction ID Wraparound

This is the scenario that keeps DBAs up at night. Postgres assigns a 32-bit transaction ID (XID) to every transaction that writes. That's about 4.2 billion IDs, compared in a circle: roughly 2 billion are "in the past" and 2 billion are "in the future" relative to any given XID.

If a table has unfrozen XIDs approaching 2 billion transactions old, they'd wrap around and suddenly look like they're "in the future," which would make committed data invisible. Postgres won't let that happen. As it gets close, it logs warnings, and if it gets within a few million XIDs of the limit it stops assigning new transaction IDs. That means no more writes until you vacuum the affected tables. Reads still work, but for most applications that's an outage.

This isn't theoretical. It happens in production when vacuum can't keep up or something is blocking it.

How Vacuum Prevents Wraparound

During vacuum, Postgres freezes old tuples: it marks them as frozen, meaning "definitely in the past, visible to everyone." Frozen tuples no longer depend on the XID comparison, so they're safe from 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

These are rough guidelines, not official limits, apart from the defaults called out:

xid_age	Situation
< 150M	Normal. Autovacuum handles this fine
150M to 200M	Approaching the aggressive vacuum threshold
> 200M	`autovacuum_freeze_max_age` (default) triggers anti-wraparound autovacuum
> 1B	Something is blocking freezing. Investigate now
~2B	Postgres stops assigning new XIDs

When a table's age(relfrozenxid) exceeds autovacuum_freeze_max_age (default 200 million), Postgres launches an anti-wraparound autovacuum on it, even if autovacuum is otherwise disabled for that table. It's an aggressive vacuum that has to visit every page that isn't already all-frozen. It costs more than a regular autovacuum, and it won't give up its lock to other operations the way normal autovacuum does, so this is often when people first notice vacuum blocking a DDL statement.

-- Find tables closest to triggering aggressive vacuum
SELECT
  oid::regclass AS table_name,
  age(relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind IN ('r', 'm', 't')
  AND age(relfrozenxid) > 100000000
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

What Blocks Vacuum from Freezing

The most common cause of wraparound emergencies is long-running transactions. Vacuum can't remove dead tuples or freeze rows newer than the oldest snapshot still in use anywhere in the system. Abandoned replication slots and old prepared transactions hold the horizon back the same way, so check those too.

-- 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
ORDER BY xact_start ASC
LIMIT 5;

Look for sessions with state = 'idle in transaction'. A connection that opened a transaction hours (or days) ago and never committed can single-handedly stop vacuum from doing its job across the whole database. Setting idle_in_transaction_session_timeout is cheap insurance.

Monitoring Vacuum Activity

In production you mostly don't run vacuum by hand. You watch autovacuum and step in when it falls behind. These are the queries that matter.

Currently Running Vacuums

-- What's vacuum doing right now?
SELECT
  p.pid,
  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,
  a.query
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON a.pid = p.pid;

  pid  | datname |  table_name   |     phase      | heap_blks_total | heap_blks_scanned | pct_done |              query
-------+---------+---------------+----------------+-----------------+-------------------+----------+----------------------------------
 14523 | mydb    | public.events | scanning heap  |         1203450 |            892100 |     74.1 | autovacuum: VACUUM public.events

The query column tells you whether it's a regular autovacuum or one running "(to prevent wraparound)".

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

Signal	Severity	Action
`dead_pct` > 20% on large tables	Medium	Check that autovacuum is running, lower the scale factor
Autovacuum running constantly but not keeping up	High	Raise the cost limit, lower cost_delay, add workers
`age(datfrozenxid)` > 500M	High	Look for long-running transactions and stale slots, run a manual `VACUUM (FREEZE)`
`age(datfrozenxid)` > 1B	Critical	Cancel whatever is blocking and vacuum the oldest tables immediately
Table size growing while live rows stay flat	Medium	Bloat is accumulating, check vacuum effectiveness

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;

This gives you one view of which tables need attention and why. For broader monitoring, see the Monitoring and Observability guide.

Conclusion

Dead tuples are a normal side effect of how Postgres does concurrency, not a bug. They just need maintenance. Our advice: leave autovacuum on and tune it per table for your biggest, busiest tables. Treat VACUUM FULL as a last resort (and look at REPACK once you're on Postgres 19). Keep an eye on XID age and long-running transactions so wraparound never becomes your problem.

Related Resources

Snowflake Engineering Blog: Postgres Vacuum Explained

Autovacuum, bloat, and tuning strategies in more depth.

PostgreSQL Documentation: Routine Vacuuming

Official Postgres docs on autovacuum configuration and maintenance.

Crunchy Data Blog: HOT Updates and Fill Factor

Reduce vacuum workload with HOT updates and fill factor tuning.

Snowflake Postgres: Monitoring and Observability Guide

Monitor vacuum activity and table bloat with Snowflake Postgres Insights.

Updated Oct 27, 2026

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances