How to find when PostgreSQL Vacuum and Analyze were last run?

To find when your PostgreSQL tables were last vacuumed and analyzed, connect with psql -U [username] [database_name] and run this query against pg_stat_user_tables:

SELECT schemaname, relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY COALESCE(last_autovacuum, last_vacuum) NULLS FIRST;

A NULL means that operation has never run on that table since the counters were last reset — which is not the same thing as “the table is fine”. The rest of this page explains what these operations do, and covers three behaviours that regularly mislead people reading this view.

Everything below was run on PostgreSQL 17.10 on 2026-08-09. The script that reproduces it is linked at the end.

What is the purpose of PostgreSQL Vacuum?

PostgreSQL’s VACUUM is a maintenance operation designed to reclaim storage occupied by expired tuples (rows) in a table, which become obsolete as a result of update and delete operations. Without a periodic VACUUM, these outdated rows continue to consume disk space and can lead to performance problems.

VACUUM removes those expired tuples and frees the space for reuse. It also advances the table’s frozen transaction ID horizon, which is what keeps the database from eventually shutting down to protect against transaction ID wraparound.

Two variants exist. Standard VACUUM frees space for reuse inside the table but does not return it to the operating system. VACUUM FULL compacts the table by writing a complete new copy of it, reclaiming more space, at the cost of holding an ACCESS EXCLUSIVE lock for the duration — nothing can read or write the table while it runs.

What is the purpose of PostgreSQL Analyze?

ANALYZE collects statistics about the contents of tables, which the query planner uses to choose execution plans. As rows are inserted, updated, and deleted, the distribution of data shifts, and a plan that was optimal last month may no longer be.

ANALYZE can run on its own or as part of VACUUM ANALYZE. It samples a subset of the table and records values such as the number of distinct values in a column, the most common values, and the correlation between physical row order and indexed column order. These statistics live in pg_statistic (readable through the pg_stats view) and are what the planner actually consults.

Three things this view will not tell you

VACUUM FULL does not update last_vacuum. This is the one that catches people out. VACUUM FULL is implemented as a table rewrite rather than as a normal vacuum, and it leaves both the timestamp and the counter alone. Measured on 17.10:

before_full: 2026-08-09 14:57:08.14013-05 cnt=1
VACUUM
after_full:  2026-08-09 14:57:08.14013-05 cnt=1

So a table can have just been fully rewritten and still look like it has not been vacuumed since yesterday. If you are auditing maintenance, do not treat a stale last_vacuum as proof that nothing ran.

These are cumulative statistics, and they get reset. Calling SELECT pg_stat_reset(); sets every one of these timestamps back to NULL. If the whole view suddenly looks like a brand new database, someone (or some monitoring tool) probably reset the counters rather than the maintenance having stopped.

They do not survive a crash. The PostgreSQL documentation states that on a clean shutdown “a permanent copy of the statistics data is stored in the pg_stat subdirectory”, but that after an unclean shutdown — an immediate stop, a crash, a restore from base backup, or point-in-time recovery — “all statistics counters are reset”. Both halves reproduce here: after pg_ctl -m fast restart the timestamps were intact; after pg_ctl -m immediate stop and a restart they were NULL.

Running VACUUM manually

To vacuum a single table and refresh its planner statistics at the same time:

VACUUM (VERBOSE, ANALYZE) table_name;

Or VACUUM; for every table in the current database. You need to be a superuser, the database owner, or the table owner — or hold the pg_maintain role, which was added in PostgreSQL 16 specifically so that maintenance can be delegated without handing out ownership.

If you are trying to work out whether autovacuum is keeping up rather than when it last ran, n_dead_tup and n_mod_since_analyze in the same view are the more useful columns: they show the backlog that autovacuum’s thresholds are actually measured against.

Reproducing this

The script verify-find-last-run-auto-vacuum-and-analyze.sh creates its own temporary PostgreSQL cluster, exercises each claim on this page, prints PASS or FAIL for each, and deletes the cluster when it exits. On PostgreSQL 17.10 it reports 18 passed, 0 failed. It does not touch any existing database on your machine.