Skip to content
DBAExperts
PostgreSQL Operations

How to run VACUUM and ANALYZE in PostgreSQL and tune autovacuum

PostgreSQL does not physically delete updated or removed rows right away: it leaves dead versions that VACUUM cleans up. ANALYZE keeps the statistics the planner uses to choose good plans.

  1. 1

    Run a maintenance VACUUM and ANALYZE

    VACUUM marks the space from dead rows as reusable; ANALYZE updates the statistics. A regular VACUUM does not block writes.

    VACUUM ANALYZE clientes;
  2. 2

    Check how many dead rows each table has

    pg_stat_user_tables shows n_dead_tup and the last time autovacuum ran. Lots of dead rows indicate autovacuum is falling behind.

    SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
  3. 3

    Tune autovacuum on write-heavy tables

    For very active tables it is better to lower the scale factor so autovacuum runs more often, rather than changing the global setting.

    ALTER TABLE pedidos SET (autovacuum_vacuum_scale_factor = 0.05);
  4. 4

    Reclaim disk space with VACUUM FULL (carefully)

    VACUUM FULL does return space to the system, but it rewrites the table and takes an exclusive lock: no one can read or write while it runs.

    VACUUM FULL clientes;

// common mistake

Disabling autovacuum to gain performance is a classic mistake: without it, tables accumulate bloat and, worse, transaction wraparound approaches, which can halt the entire database. It is almost never worth turning off.

// when it's worth an expert

When there is chronic bloat, VACUUM FULL is not an option because of the lock, or you are approaching wraparound risk, maintenance requires planning. At dba.mx we tune autovacuum per table and use tools like pg_repack to reclaim space without stopping operations, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.