PostgreSQL VACUUM and autovacuum — Stop the bloat and restore speed now

Your PostgreSQL database is getting slower. Queries that ran in milliseconds now take seconds. Disk space is filling up for no apparent reason. You've checked indexes, cache, hardware. The culprit is almost always the same: bloat from uncollected dead tuples. Here at Meteora Web we see it every time we're called for a performance audit. The solution is called VACUUM and autovacuum.

Why does a PostgreSQL database bloat and slow down?

PostgreSQL uses MVCC (Multi-Version Concurrency Control). Every time you update or delete a row, the old version remains in the data file until all transactions that could see it have finished. These obsolete records are called dead tuples. As they accumulate, they create bloat: the table grows on disk, scans become slower, indexes degrade. Without maintenance, even a table with few records can take gigabytes.

The cost of bloat is not just disk space. Every query scan must read pages full of dead tuples. The statistics analyzer works on dirty data, and the query planner makes wrong decisions. The result is a progressive performance degradation.

Real‑world example: A client with an e‑commerce catalog of 10,000 products had an orders table of 8 GB. After a VACUUM FULL, space dropped to 2.5 GB. Reporting queries went from 45 seconds to 1 second.

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(100 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_percent
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_percent DESC
LIMIT 10;

How does VACUUM differ from autovacuum in PostgreSQL?

VACUUM is the process that reclaims space occupied by dead tuples, updates statistics (if you use VACUUM ANALYZE), and marks pages as reusable. There are two main modes:

• Standard VACUUM (or VACUUM ANALYZE): does not block writes, but does not return space to the OS. Freed pages remain allocated to the table.
• VACUUM FULL: rewrites the entire table, compacts it, and returns space to the OS, but blocks writes during the operation. Use with caution.

Autovacuum is the built‑in daemon that runs VACUUM automatically based on configurable thresholds. It is enabled by default, but the out‑of‑the‑box settings are designed for average workloads. For high‑concurrency applications or heavy UPDATE/DELETE patterns, parameters must be tuned.

The practical difference is: you trigger manual VACUUM when you see the problem; autovacuum should do it automatically… if you configure it properly. We have seen dozens of servers where autovacuum is running but cannot keep up because the dead tuple threshold is too high or the vacuum cost is too low.

SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

Which autovacuum parameters should you configure for PostgreSQL?

The most critical parameters live in postgresql.conf or can be changed with ALTER SYSTEM. Here are the ones to tweak for a medium‑to‑high load database:

• autovacuum_vacuum_scale_factor + autovacuum_vacuum_threshold — Determine when vacuum kicks in. For small tables the fixed threshold matters; for large tables the scale factor prevents too‑frequent triggers. Default is threshold=50 and scale_factor=0.2 (20% of rows modified). On a table with millions of rows, 20% means hundreds of thousands of dead tuples before action.
• autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay — Control how much workload vacuum can generate without impacting other queries. Low values make vacuum slow and ineffective. On dedicated servers, raise cost_limit (e.g., to 1000) and lower delay (e.g., to 10ms).
• autovacuum_max_workers — Maximum number of concurrent vacuum processes. Default is 3; if you have many active tables, increase to 5‑6.

Practical tip: For a critical table you can set per‑table parameters with ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 1000);.

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_vacuum_threshold = 1000;
SELECT pg_reload_conf();

How to monitor VACUUM status and dead tuples in PostgreSQL?

You cannot manage what you don't measure. PostgreSQL provides several views to monitor maintenance effectiveness:

• pg_stat_user_tables — contains n_live_tup, n_dead_tup, last_autovacuum, last_vacuum.
• pg_stat_progress_vacuum — shows running vacuums with details (table, phase, percentage).
• pg_stat_all_tables — complete view including system tables.

A great early‑warning indicator is n_mod_since_analyze, which tells how many modifications happened since the last ANALYZE. If it exceeds the threshold, statistics are stale and the planner may misbehave.

We use a small script that sends email alerts when the dead/live ratio exceeds 10% on a significant table. You can find the official documentation at PostgreSQL VACUUM documentation.

SELECT relname, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST
LIMIT 10;

How to force a manual VACUUM when autovacuum is not enough?

There are situations where autovacuum cannot keep up: nightly batch jobs that generate millions of dead tuples in minutes, tables with heavy locks, or misconfiguration. In those cases a manual intervention is needed.

The operational sequence is:

1. VACUUM ANALYZE on the specific table — reclaims space, updates statistics, does not block.
2. VACUUM FULL if bloat is extreme and you can afford a short downtime (write‑lock).
3. REINDEX after VACUUM FULL, because indexes may remain fragmented.

Warning: VACUUM FULL rewrites the entire table – you need disk space equal to its current size. For multi‑GB tables, schedule a maintenance window.

Example of a controlled execution:

-- First, check the state
SELECT * FROM pg_stat_user_tables WHERE relname = 'orders';

-- Standard VACUUM + statistics
VACUUM (VERBOSE, ANALYZE) orders;

-- If FULL is needed (schedule during low traffic)
VACUUM (FULL, VERBOSE) orders;
REINDEX TABLE orders;

We recommend scheduling VACUUM FULL only on tables that explicitly need it, maybe via a nightly cron job. For everything else, a well‑configured autovacuum suffices.

In summary

• Check dead tuples now – if above 5‑10%, act.
• Tune autovacuum parameters – scale_factor, threshold, cost_limit, max_workers.
• Monitor regularly – last_autovacuum, last_analyze, n_mod_since_analyze.
• Use manual VACUUM only for extreme cases – prefer VACUUM ANALYZE to VACUUM FULL.
• Don't neglect statistics – run ANALYZE separately if VACUUM doesn't include it.

For a deeper dive into the entire PostgreSQL world, check out our Advanced PostgreSQL pillar guide.

Official reference: PostgreSQL Documentation – Routine Database Maintenance.