What VACUUM and ANALYZE Do in PostgreSQL

PostgreSQL uses multiversion concurrency control, or MVCC, to let transactions read consistent data while other transactions change it. When a transaction updates or deletes a row, PostgreSQL keeps the old row version until no active transaction needs it. PostgreSQL calls that old version a dead tuple.

VACUUM clears dead tuples so PostgreSQL can reuse their space. It also freezes old row versions so PostgreSQL can reuse transaction IDs without confusing old and new transactions. ANALYZE collects statistics about table contents. PostgreSQL’s query planner uses these statistics to estimate how many rows a query will return and choose how to run it. The commands handle different maintenance needs, and VACUUM ANALYZE runs both on the selected tables.

VACUUM clears out obsolete row versions

When a transaction deletes a row or replaces it with an updated version, PostgreSQL does not immediately remove the old version from the table. Once no active transaction can see that version, VACUUM can reclaim it. This regular cleanup keeps dead tuples from building up and slowing table scans. PostgreSQL’s routine vacuuming documentation describes this work and its role in database maintenance.

A regular VACUUM generally makes reclaimed space available for reuse inside the same table. For example, later inserts or updates can use that space instead of extending the table file. The file therefore may not shrink, and the operating system may not show more free disk space afterward. PostgreSQL returns space to the operating system only in limited circumstances with regular vacuuming, such as when it can remove unused pages from the end of a table. The VACUUM command documentation explains the command’s behavior.

VACUUM FULL takes a different approach: PostgreSQL rewrites the table into a new file without the unused space, then replaces the old file. This can return space to the operating system, but it takes longer, needs extra disk space while the rewrite runs, and locks the table against other access. Use it only when reclaiming space from the table file justifies that disruption.

Vacuuming also supports two less visible tasks. PostgreSQL updates a visibility map, which records whether each table page contains only row versions visible to all transactions. An index-only scan answers a query using an index without reading the table, and this map helps PostgreSQL know when it can do that. It also freezes sufficiently old row versions so PostgreSQL can safely reuse transaction IDs. Because transaction IDs wrap around, PostgreSQL needs this maintenance to prevent old and new transactions from becoming indistinguishable.

ANALYZE helps PostgreSQL choose query plans

ANALYZE collects statistics about the values in a table’s columns and stores them for the query planner. The planner uses those statistics to estimate how many table rows a query will return, then chooses an execution plan, such as which index to use. If a table’s contents change substantially, outdated statistics can lead the planner to choose an inefficient plan.

For large tables, ANALYZE examines a random sample rather than every row. Its statistics are therefore estimates, and repeated runs can produce small differences. Those changes sometimes lead the planner to select a different plan. PostgreSQL can adjust how much detail it collects, but more detailed statistics require more work. The ANALYZE command documentation describes sampling and the command’s read-lock behavior.

ANALYZE takes a read lock that allows queries and data changes to continue while it runs. After a large data load or other substantial change, running ANALYZE helps refresh estimates before the planner relies on stale statistics.

Autovacuum handles routine maintenance

PostgreSQL’s autovacuum daemon runs vacuuming and analysis automatically by default. It decides when to act based on table activity and configured thresholds, rather than following a fixed calendar schedule. The routine maintenance documentation explains how autovacuum handles dead tuples, updates statistics, and freezes old row versions.

Operators should leave autovacuum enabled unless they have a specific reason to change its behavior. When a large or frequently changing table falls behind, inspect that table’s maintenance needs and autovacuum settings rather than assuming a universal schedule. For a one-time combined maintenance command, PostgreSQL supports:

VACUUM (ANALYZE) table_name;

This runs vacuuming and then collects statistics for the selected table. Regular VACUUM and ANALYZE can run alongside ordinary database activity; VACUUM FULL has the stronger locking and disk-space costs described above.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com