6

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
jberryman
  • 481
  • 1
  • 5
  • 11

3 Answers3

4

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.

Anentropic
  • 548
  • 1
  • 5
  • 11
1

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

Ashiq Ahamed
  • 351
  • 2
  • 8
0

It seems last_autovacuum is the time vacuuming completed, because: (0) timestamps are sometimes really close together: 2023-07-16 08:21:19.247452+00 2023-07-16 08:21:19.495501+00 (1) according to the docs one autovacuum is run on each database each autovacuum_naptime seconds. https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM

rihad
  • 1