PostgreSQL – When Does pg_stat_all_tables.last_autovacuum Update?

autovacuumpostgresql

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

Best Answer

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

https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211a88f43/src/backend/catalog/system_views.sql#L584

pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,

https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211a88f43/src/backend/utils/adt/pgstatfuncs.c#L267

result = tabentry->autovac_vacuum_timestamp;

https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211a88f43/src/include/pgstat.h#L740

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.

Related Question