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.
Select those never-vacuumed tables with IS NULL, never with = NULL. The equality form returns no rows and no error, because comparing anything to NULL yields NULL rather than true, and a row whose condition is NULL is discarded exactly like one that is false.
Everything below was run on PostgreSQL 17.10 on 2026-08-09. The script that reproduces it is linked at the end.
Table of Contents
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. Both were timed and had their lock modes sampled from a third session on a 489 MB table, if you want the difference measured rather than described.
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=1So 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, and takes with it the counters autovacuum schedules its work from — what a reset costs, and what it leaves alone. 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 on the cumulative statistics system 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.
Check this yourself. Every command and every block of output on this page is reproduced by /verify/find-last-run-auto-vacuum-and-analyze.sh. Download it and run it: it creates its own scratch files, prints one line per claim, cleans up after itself, and exits non-zero if any claim here turns out to be wrong. If it disagrees with this page, the page is wrong.
