Postgres VACUUM Command Generator

Compose the right PostgreSQL VACUUM command instead of guessing at options. Pick plain VACUUM, VACUUM ANALYZE, VACUUM FULL or ANALYZE, scope it to one table or the whole database, and toggle VERBOSE, FREEZE, SKIP_LOCKED, PARALLEL workers, INDEX_CLEANUP and TRUNCATE behaviour. The generator emits the SQL command and the equivalent vacuumdb shell command, plus ready-to-run monitoring queries (vacuum progress, dead-tuple counts, transaction-ID wraparound headroom) and per-table autovacuum tuning DDL so manual vacuums become rare. It also warns you when an option is dangerous — like the exclusive lock VACUUM FULL takes. Runs entirely in your browser.

ModeScopeTable

VERBOSEFREEZESKIP_LOCKEDPARALLEL workersIndex cleanupTRUNCATE false (keep trailing pages)

Generated command

VACUUM (VERBOSE, ANALYZE) public.orders;

Shell equivalent (vacuumdb)

# Equivalent from the shell:
vacuumdb --dbname=mydb --table='public.orders' --analyze --verbose

Monitoring queries

-- Watch a running VACUUM (one row per worker):
SELECT p.pid, p.phase, p.heap_blks_scanned, p.heap_blks_total,
       round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS pct_done,
       a.query_start, a.query
FROM   pg_stat_progress_vacuum p
JOIN   pg_stat_activity a USING (pid);

-- Dead tuples and last (auto)vacuum per table:
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  20;

-- Tables closest to the anti-wraparound threshold:
SELECT c.relname, age(c.relfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint - age(c.relfrozenxid) AS xids_left
FROM   pg_class c
WHERE  c.relkind = 'r'
ORDER  BY age(c.relfrozenxid) DESC
LIMIT  10;

Per-table autovacuum tuning

-- Per-table autovacuum tuning (so you rarely need manual VACUUM):
-- Default triggers at 20% dead tuples; large tables should trigger earlier.
ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor  = 0.05,  -- vacuum at 5% dead tuples
  autovacuum_analyze_scale_factor = 0.02,  -- analyze at 2% changed tuples
  autovacuum_vacuum_cost_delay    = 2      -- ms; lower = vacuum works faster
);

-- Check what a table currently has set:
SELECT relname, reloptions FROM pg_class WHERE relname = 'orders';

-- Reset to defaults:
-- ALTER TABLE public.orders RESET (autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor, autovacuum_vacuum_cost_delay);

Show autovacuum tuning DDL

Notes

• Plain VACUUM does not lock out reads or writes and does not shrink the file for space in the middle of the table — it marks dead tuples reusable. Only trailing empty pages are returned to the OS.
• VACUUM cannot remove tuples still visible to any open transaction — long-running transactions and stale replication slots make dead-tuple counts stay high no matter how often you vacuum.
• ANALYZE refreshes planner statistics. If a query got slow after a big data change, ANALYZE is usually the fix, not VACUUM.

Do more than postgres vacuum command generator — 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

Choose the mode — VACUUM, VACUUM ANALYZE, VACUUM FULL or ANALYZE — and whether it targets one table or the whole database.
Toggle options like VERBOSE, FREEZE, SKIP_LOCKED, PARALLEL workers or INDEX_CLEANUP, and read the warnings the tool raises for risky combinations.
Copy the generated command, use the monitoring queries to watch progress and dead tuples, and apply the autovacuum tuning DDL to stop the bloat from coming back.

Frequently asked questions

What does VACUUM actually do in PostgreSQL?

PostgreSQL never overwrites rows in place: UPDATE and DELETE leave dead tuples behind. VACUUM scans the table, marks that dead space reusable for future writes, updates the visibility map, and freezes old transaction IDs to prevent wraparound. Plain VACUUM runs alongside normal reads and writes and does not return space to the operating system (except trailing empty pages). VACUUM ANALYZE additionally refreshes the planner statistics that query plans depend on.

When should I use VACUUM FULL?

Almost never on a live system. VACUUM FULL rewrites the entire table into a new file, which does return disk space to the OS, but it holds an ACCESS EXCLUSIVE lock the whole time — every query on the table blocks — and needs enough free disk for a full copy. It is reasonable after deleting most of a table's rows during a maintenance window. Otherwise fix autovacuum so bloat never accumulates, or use pg_repack, which compacts the table with only brief locks.

Why is autovacuum not keeping up with my table?

The default trigger is 20% dead tuples (autovacuum_vacuum_scale_factor = 0.2), which on a 100-million-row table means 20 million dead rows before autovacuum even starts, and cost-based throttling then makes it run slowly. Lower the scale factor per table (e.g. 0.05), reduce autovacuum_vacuum_cost_delay, and check for long-running transactions or abandoned replication slots that prevent dead tuples from being removed at all. To run these diagnostics against a live database, Chat2DB is a free AI database client — download it at https://chat2db.ai/download or use https://app.chat2db.ai in the browser.