SQL Academy · Lesson

VACUUM, autovacuum, vacuum_cost_delay

Tune autovacuum thresholds, vacuum_cost_delay, and parallel VACUUM for big tables.

VACUUM, autovacuum, vacuum_cost_delay is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Three VACUUM Modes

VACUUM (lazy) — reclaim dead rows, update free space map, no exclusive lock
VACUUM FULL — rewrite the table; exclusive lock; releases disk back to OS
VACUUM ANALYZE — vacuum + refresh planner statistics in one go

Running VACUUM Manually

Sometimes useful even with autovacuum:

VACUUM (VERBOSE) orders;
VACUUM (VERBOSE, ANALYZE) orders;
VACUUM (PARALLEL 4) orders;       -- parallel index cleanup (PG 13+)

Autovacuum Process

A background launcher spawns workers that vacuum tables exceeding thresholds. By default a handful of workers handle the cluster.

Per-Table Tuning

Hot tables benefit from aggressive autovacuum:

ALTER TABLE busy_table SET (
  autovacuum_vacuum_scale_factor = 0.05,    -- 5% dead vs default 20%
  autovacuum_vacuum_threshold    = 1000,
  autovacuum_analyze_scale_factor = 0.05
);

vacuum_cost_delay

Limits VACUUM I/O so it doesn't hurt foreground queries. PG 12+ default is 2ms — usually fine. For SSDs you can lower it:

autovacuum_vacuum_cost_delay = 2ms
-- Lower delay = faster vacuum, more I/O usage.
-- Set per-table for hot tables that need faster cleanup.

Parallel Vacuum

VACUUM parallelises index cleanup since PG 13:

VACUUM (PARALLEL 4) big_table;
-- Each index cleaned by a parallel worker (up to max_parallel_maintenance_workers).

VACUUM FULL: When and Why

Rewrites the table from scratch — releases space to the OS. Acquires ACCESS EXCLUSIVE. Use only as a last resort, or with pg_repack instead.

VACUUM FULL orders;
-- Locks orders; rewrites; on huge tables this is hours of downtime.

pg_repack: Online Alternative

Rewrites a table online — no full lock. Same effect as VACUUM FULL but production-friendly:

pg_repack -d mydb -t orders
-- Builds a shadow table, sync, swaps in.

Monitoring Autovacuum

See last vacuum/analyze times and dead-tuple counts:

SELECT relname,
       n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

When Autovacuum Falls Behind

Symptoms:

Bloat grows
n_dead_tup high in pg_stat_user_tables
Query plans degrade

Fixes: increase autovacuum_max_workers, lower per-table scale factor, fix long transactions that block vacuum.

Vacuum Logging

For tuning, log slow vacuums:

log_autovacuum_min_duration = '500ms'
-- Logs every autovacuum action that took longer than 500ms.

FREEZE

VACUUM occasionally "freezes" old tuples to prevent xid wraparound. Aggressive autovacuum keeps freeze ages low and prevents emergency wraparound events.

Recap

Autovacuum is essential; tune per workload.

Per-table autovacuum settings for hot tables
VACUUM FULL is last resort — prefer pg_repack
Monitor n_dead_tup and last_autovacuum
Fix long transactions that block cleanup

Quick Check

What's the safest way to reclaim disk space on a hot production table without long-blocking lock?

Frequently asked questions

Is the “VACUUM, autovacuum, vacuum_cost_delay” lesson free?

Yes — the full text of “VACUUM, autovacuum, vacuum_cost_delay” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “VACUUM, autovacuum, vacuum_cost_delay”?

Tune autovacuum thresholds, vacuum_cost_delay, and parallel VACUUM for big tables. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “VACUUM, autovacuum, vacuum_cost_delay” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy