How to vacuum and analyze tables in PostgreSQL

Regular VACUUM and ANALYZE maintenance keeps PostgreSQL fast by reclaiming space from dead rows and keeping planner statistics current. High-churn tables that fall behind on housekeeping often show symptoms as bloated storage, slower index scans, and sudden query plan regressions.

PostgreSQL uses MVCC, so updates and deletes leave old row versions behind as dead tuples until vacuuming marks that space reusable. ANALYZE samples table contents to update statistics (row counts, value distribution, correlation) that the query planner uses to pick indexes, join strategies, and parallelism.

The autovacuum worker normally runs vacuum/analyze automatically, but manual maintenance helps after bulk loads, mass deletes, or large data moves that outpace background cleanup. Regular VACUUM typically does not return disk space to the operating system; it makes free space available for reuse inside the table file, while VACUUM FULL rewrites the table, requires an exclusive lock, and can consume significant time and temporary disk space.

1. List tables with the most estimated dead tuples in the current database.

   $ sudo -u postgres psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"
     relname  | n_live_tup | n_dead_tup | last_vacuum |        last_autovacuum        |         last_analyze          |       last_autoanalyze
   -----------+------------+------------+-------------+-------------------------------+-------------------------------+-------------------------------
    orders    |     100000 |       5001 |             | 2025-12-24 13:45:09.129112+00 | 2025-12-24 13:49:10.692899+00 |
    events    |       5000 |          0 |             | 2025-12-24 13:45:09.130331+00 |                               | 2025-12-24 13:45:09.149926+00
    customers |       1000 |          0 |             |                               |                               | 2025-12-24 13:45:09.090383+00
   (3 rows)

   n_dead_tup is an estimate; treat it as a prioritization hint rather than an exact count.

2. Run VACUUM with VERBOSE and ANALYZE on a target table after major churn.

   $ sudo -u postgres psql -d appdb -c "VACUUM (VERBOSE, ANALYZE) public.orders;"
   INFO:  vacuuming "appdb.public.orders"
   INFO:  launched 1 parallel vacuum worker for index vacuuming (planned: 1)
   INFO:  finished vacuuming "appdb.public.orders": index scans: 1
   ##### snipped #####
   INFO:  analyzing "public.orders"
   INFO:  "orders": scanned 876 of 876 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows
   VACUUM

   Regular VACUUM uses a lightweight lock that allows normal reads and writes on the table.

3. Run ANALYZE on a table when only planner statistics need refreshing.

   $ sudo -u postgres psql -d appdb -c "ANALYZE public.orders;"
   ANALYZE

4. Run a database-wide VACUUM with ANALYZE after a restore or large batch change window.

   $ sudo -u postgres psql -d appdb -c "VACUUM (ANALYZE);"
   VACUUM

   Database-wide maintenance can be I/O heavy and long-running on large databases.

5. Use VACUUM FULL only during a maintenance window when on-disk space reclamation is required.

   $ sudo -u postgres psql -d appdb -c "VACUUM (FULL, VERBOSE, ANALYZE) public.orders;"
   INFO:  vacuuming "public.orders"
   INFO:  "public.orders": found 0 removable, 100000 nonremovable row versions in 876 pages
   ##### snipped #####
   VACUUM

   VACUUM FULL takes an ACCESS EXCLUSIVE lock on the table, blocking reads and writes until completion.

6. Confirm updated last_vacuum and last_analyze timestamps on the affected table.

   $ sudo -u postgres psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'orders';"
    relname | n_live_tup | n_dead_tup |          last_vacuum          |        last_autovacuum        |         last_analyze          | last_autoanalyze
   ---------+------------+------------+-------------------------------+-------------------------------+-------------------------------+------------------
    orders  |     100000 |          0 | 2025-12-24 13:49:21.499436+00 | 2025-12-24 13:45:09.129112+00 | 2025-12-24 13:49:21.772826+00 |
   (1 row)

7. Tune autovacuum settings for tables where dead tuples accumulate faster than cleanup.

   Related: How to tune autovacuum in PostgreSQL