PostgreSQL Autovacuum Tuning Calculator

Enter your table's row count, average row size and write rate, plus your current autovacuum settings, and this calculator works out exactly when autovacuum will fire, how many dead tuples and how much bloat the table carries at that moment, how fast a vacuum pass can actually run under cost-based throttling, and whether cleanup keeps up with your writes at all. Pick a table profile and it generates the per-table ALTER TABLE ... SET storage parameters to fix it, along with the pg_stat_user_tables and pg_stat_progress_vacuum queries to verify the result on a live server. Every intermediate number is shown so you can check the arithmetic. Everything runs entirely in your browser — no connection details and no data are sent anywhere.

Table

Table nameLive rowsAvg row size (bytes)UPDATEs / secDELETEs / secINSERTs / sec

Current autovacuum thresholds

vacuum_scale_factorvacuum_thresholdanalyze_scale_factoranalyze_thresholdinsert_scale_factorinsert_threshold

I/O budget

vacuum_cost_limitvacuum_cost_delay (ms)max_workersnaptime (s)Buffer cache hit %

Table profile

Analysis

CURRENT BEHAVIOUR
-----------------
Table                       50,000,000 live rows, ~9.31 GB heap (1,220,704 pages of 8 kB)
Dead tuples produced        320.0/s  (300.0 UPDATE + 20.0 DELETE)
Rows modified for ANALYZE   420.0/s  (INSERT counts here too)

Trigger points  (PostgreSQL: threshold + scale_factor x reltuples)
  autovacuum VACUUM         n_dead_tup > 10,000,050   -> fires every 8.7 h
  autovacuum ANALYZE        n_mod_since_analyze > 5,000,050   -> fires every 3.3 h
  insert-triggered VACUUM   n_ins_since_vacuum > 10,001,000   -> fires every 1.2 days

At the VACUUM trigger point the table carries about 10,000,050 dead tuples
= 1.86 GB of dead space, i.e. ~20.0% bloat before cleanup even starts.

THROTTLING (cost-based delay)
-----------------------------
Effective cost per page     5.10  (page_hit 1 / page_miss 2 / page_dirty 20, at 90% cache hit)
Scan rate                   ~19,608 pages/s = 153.2 MB/s
Time to vacuum whole table  1.0 min
Dead tuples created during  19,922 while that run is in progress
Verdict                     autovacuum keeps up with the write rate at these settings.

RECOMMENDATION
--------------
Profile                     Large table (millions of rows)
New VACUUM trigger          n_dead_tup > 500,000  (~1.00% bloat)
New VACUUM frequency        every 26.0 min  (was 8.7 h)
New full-table pass         12.5 s  (was 1.0 min)
Verdict                     with these settings autovacuum keeps up.

Note: autovacuum_vacuum_cost_limit is shared across all 3 workers,
so each concurrently running worker gets roughly 333 cost units per round.
autovacuum_naptime is 60s, so no table is checked more often than that.

Per-table tuning (ALTER TABLE)

-- Per-table autovacuum overrides for public.events
-- These are storage parameters: they take effect immediately, no restart, no reload.
ALTER TABLE public.events SET (
  autovacuum_vacuum_scale_factor        = 0,
  autovacuum_vacuum_threshold           = 500000,
  autovacuum_analyze_scale_factor       = 0,
  autovacuum_analyze_threshold          = 250000,
  autovacuum_vacuum_insert_scale_factor = 0,
  autovacuum_vacuum_insert_threshold    = 1000000,
  autovacuum_vacuum_cost_limit          = 1000,
  autovacuum_vacuum_cost_delay          = 2
);

-- Verify what actually got stored:
SELECT relname, reloptions
FROM   pg_class
WHERE  oid = 'public.events'::regclass;

-- Revert everything back to the global defaults:
-- ALTER TABLE public.events RESET (
--   autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold,
--   autovacuum_analyze_scale_factor, autovacuum_analyze_threshold,
--   autovacuum_vacuum_insert_scale_factor, autovacuum_vacuum_insert_threshold,
--   autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay
-- );

-- TOAST table of the same relation is tuned separately, e.g.:
-- ALTER TABLE public.events SET (toast.autovacuum_vacuum_scale_factor = 0);

Monitoring queries

-- 1. Is this table actually accumulating dead tuples?
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       n_mod_since_analyze,
       n_ins_since_vacuum,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count,
       autoanalyze_count
FROM   pg_stat_user_tables
WHERE  relid = 'public.events'::regclass;

-- 2. Top 20 tables by dead tuples across the whole database
SELECT schemaname || '.' || relname AS table_name,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       pg_size_pretty(pg_total_relation_size(relid))                     AS total_size,
       last_autovacuum
FROM   pg_stat_user_tables
WHERE  n_dead_tup > 1000
ORDER  BY n_dead_tup DESC
LIMIT  20;

-- 3. What is autovacuum doing right now, and how far along is it?
SELECT p.pid,
       p.datname,
       p.relid::regclass                                   AS table_name,
       p.phase,
       pg_size_pretty(p.heap_blks_total * 8192::bigint)     AS heap_total,
       pg_size_pretty(p.heap_blks_scanned * 8192::bigint)   AS heap_scanned,
       round(100.0 * p.heap_blks_scanned
             / NULLIF(p.heap_blks_total, 0), 1)             AS pct_scanned,
       p.index_vacuum_count,
       now() - a.xact_start                                 AS running_for
FROM   pg_stat_progress_vacuum p
JOIN   pg_stat_activity a USING (pid);

-- 4. Which tables have per-table overrides already?
SELECT c.oid::regclass AS table_name, c.reloptions
FROM   pg_class c
WHERE  c.reloptions IS NOT NULL
  AND  array_to_string(c.reloptions, ',') LIKE '%autovacuum%'
ORDER  BY 1;

-- 5. Transaction-ID age: the reason autovacuum can never be turned off
SELECT c.oid::regclass                    AS table_name,
       age(c.relfrozenxid)                AS xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age
FROM   pg_class c
WHERE  c.relkind IN ('r', 'm')
ORDER  BY age(c.relfrozenxid) DESC
LIMIT  10;

Notes

• autovacuum_vacuum_scale_factor is a fraction of the table, so the default 0.2 means a 50-million-row table waits for 10 million dead tuples before it is vacuumed. That is why large tables should use a flat autovacuum_vacuum_threshold with the scale factor set to 0.
• Every UPDATE creates a dead tuple even when the new version fits on the same page (a HOT update). HOT only avoids the index writes; the old row version still has to be reclaimed by vacuum.
• autovacuum_vacuum_cost_limit is divided among the running workers, not given to each of them. Raising autovacuum_max_workers without raising the cost limit makes each worker slower, not the cleanup faster.
• Setting autovacuum_vacuum_cost_delay to 0 removes throttling entirely. That is sometimes right for a maintenance window, but on a busy system it lets vacuum saturate your I/O and hurt query latency.
• Insert-only tables need vacuum too. Since PostgreSQL 13, autovacuum_vacuum_insert_threshold triggers a vacuum on inserts so pages get frozen and marked all-visible, which is what makes index-only scans work.
• Never turn autovacuum off. If transaction IDs are not frozen the database eventually refuses new writes to protect against wraparound, and the recovery is a long single-user VACUUM FREEZE.
• The numbers here are estimates from your inputs, not measurements. Confirm against pg_stat_user_tables and pg_stat_progress_vacuum on the real server before committing to a setting.

Do more than postgresql autovacuum tuning calculator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Fill in the table facts: name, live row count, average row size, and the UPDATE, DELETE and INSERT rates per second that the table sees at peak.
Enter the autovacuum settings currently in effect (SHOW autovacuum_vacuum_scale_factor, and the reloptions on the table) plus the cost limit, cost delay and worker count.
Choose a table profile to get suggested overrides, copy the generated ALTER TABLE statement, then run the monitoring queries to confirm dead tuples actually stop piling up.

Frequently asked questions

When does autovacuum trigger on a PostgreSQL table?

Autovacuum vacuums a table once n_dead_tup exceeds autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples, and analyzes it once n_mod_since_analyze exceeds autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples. With the defaults (threshold 50, scale factor 0.2) a table needs roughly 20% of its rows dead before cleanup starts. Since PostgreSQL 13 there is also an insert-driven trigger, autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor × reltuples, so append-only tables still get frozen and marked all-visible. The launcher only wakes up every autovacuum_naptime seconds, so that is the finest granularity you get.

What autovacuum settings should I use for a very large table?

Set autovacuum_vacuum_scale_factor to 0 and use a flat autovacuum_vacuum_threshold instead, so the trigger point stops scaling with table size. A common target is about 1% of the table: on 50 million rows that is a threshold of roughly 500,000 dead tuples rather than the 10 million the default 0.2 scale factor implies. Then raise autovacuum_vacuum_cost_limit (1000–3000 is typical on SSD) and keep autovacuum_vacuum_cost_delay at 2 ms so a pass can actually finish before the next one is due. Apply all of it per table with ALTER TABLE ... SET rather than globally.

Why is autovacuum running constantly but the table is still bloated?

The usual cause is that vacuum cannot remove the dead tuples: a long-running transaction, an idle-in-transaction session, an abandoned replication slot or a stale prepared transaction holds back the xmin horizon, so the dead rows are still visible to somebody. Check pg_stat_activity for old xact_start values, pg_replication_slots for inactive slots and pg_prepared_xacts. The other cause is throttling — the cost limit is too low for the write rate, so each pass is slower than the rate at which new dead tuples appear. Chat2DB makes this easy to watch: track dead tuples, vacuum progress and blocking sessions with AI-assisted SQL at https://app.chat2db.ai or download it from https://chat2db.ai/download.