PostgreSQL VACUUM basics

Topic: Databases core

VACUUM reclaims dead tuple space and updates visibility for the planner. Run VACUUM ANALYZE after bulk changes. Rely on autovacuum; tune if needed. Use this when you see bloat or stale stats.

Intent: How-to

Quick answer

  • VACUUM marks dead space reusable. VACUUM FULL returns disk but locks table. Prefer regular VACUUM.
  • VACUUM ANALYZE updates planner stats. Autovacuum does this by default. Tune autovacuum_vacuum_scale_factor if needed.
  • Monitor pg_stat_user_tables n_dead_tup and last_vacuum. Avoid long transactions that block vacuum.

Prerequisites

Steps

  1. Run VACUUM

    VACUUM table_name; or VACUUM ANALYZE table_name;. Non-blocking. Use VACUUM FULL only when necessary.

  2. Tune autovacuum

    Check pg_stat_user_tables. Adjust autovacuum_vacuum_scale_factor or per-table storage params.

  3. Monitor

    Watch n_dead_tup and last_vacuum. Schedule heavy VACUUM during low traffic.

Summary

Run VACUUM and VACUUM ANALYZE; rely on autovacuum; tune when needed.

Prerequisites

Steps

Step 1: Run VACUUM

VACUUM or VACUUM ANALYZE; avoid VACUUM FULL unless required.

Step 2: Tune autovacuum

Check stats; adjust scale factor or per-table settings.

Verification

  • last_vacuum recent; n_dead_tup low.

Troubleshooting

Bloat — Long transactions block vacuum; use VACUUM FULL or pg_repack in maintenance. Autovacuum not running — Check it is on and not blocked.

Next steps

Continue to