I'm looking into issues with our autovacuum settings and trying to understand last_autoanalyze and last_autovacuum from the pg_stat_all_tables.

I understand autovacuum to work in an incremental way, compacting and cleaning up dead tuples in batches, then sleeping for a moment, etc. So what does last_autovacuum mean? Is it possible that autovacuum could be mostly working but that that timestamp might never get updated (e.g. because a tuple could not be removed)?

Many of the timestamps I see for our tables are old, even though I'm fairly sure I observed a running autovacuum thread on some of the tables.

EDIT: If I'm unclear, my question is: does last_autovacuum mean...

  • the time at which an incremental autovacuum process last finished up a chunk of work on this table and slept
  • the time an autovacuum process worked its way to the very end of a table
  • the above, but only if there were no tuples that could not be removed
  • something else
  • Could you clarify a bit your question ? The last_autovacuum is last time at which this table was vacuumed by the autovacuum daemon.
    – Luan Huynh
    May 21 '18 at 14:16

I also wanted to know this, so I dug through the Postgres src code a bit to try and find out...


pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,


result = tabentry->autovac_vacuum_timestamp;


TimestampTz autovac_vacuum_timestamp;   /* autovacuum initiated */

So, if I followed the code correctly, it appears that last_autovacuum timestamp is when the process started, not when it finished.


The last_autovacuum is nothing but the last time at which the autovacuum daemon done the vacuuming process on tables

The pg_stat_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about accesses to that specific table

  • the last_autovacuum will be update it timestamp only if dead tuples are removed for the respected table during vacuuming

For more information kindly refere postgresql stats collector page https://www.postgresql.org/docs/9.6/static/monitoring-stats.html

  • 2
    So if last_autovacuum field is null, that's because a) autovacuum process always decides that no tuples have to be removed from the table, AND/OR b) autovacuum process can't finish removing dead tuples. Am I right?
    – EAmez
    Aug 8 '19 at 13:02
  • 1
    If the time of autovacuum is long - is it the time of beginning of operation or the end of operation? Apr 20 '20 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.