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.

Steps to vacuum and analyze tables in PostgreSQL:

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         |        500 |          0 | 2025-12-29 09:06:45.073849+00 |                 | 2025-12-29 09:06:45.074866+00 | 2025-12-29 08:36:26.18679+00
 users          |         50 |          0 |                               |                 |                               | 
 events_default |          1 |          0 |                               |                 |                               | 
 events_2025_02 |          1 |          0 |                               |                 |                               | 
 events         |          0 |          0 |                               |                 |                               | 
 events_2025_03 |          0 |          0 |                               |                 |                               | 
(6 rows)

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

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:  finished vacuuming "appdb.public.orders": index scans: 0
pages: 0 removed, 5 remain, 5 scanned (100.00% of total)
tuples: 0 removed, 500 remain, 0 are dead but not yet removable
##### snipped #####
INFO:  analyzing "public.orders"
INFO:  "orders": scanned 5 of 5 pages, containing 500 live rows and 0 dead rows; 500 rows in sample, 500 estimated total rows
VACUUM

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

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

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

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.

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, 500 nonremovable row versions in 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
##### snipped #####
VACUUM

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

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  |        500 |          0 | 2025-12-29 09:08:14.215358+00 |                 | 2025-12-29 09:08:14.360567+00 | 2025-12-29 08:36:26.18679+00
(1 row)

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

Related: How to tune autovacuum in PostgreSQL