pg_stat_all_tables.last_vacuum not always correct.

Started by Kathleen Emersonover 7 years ago2 messagesgeneral
Jump to latest
#1Kathleen Emerson
heavylivestock@gmail.com

I ran `VACUUM VERBOSE` on 10-28. After the VACUUM completed, I ran the
query

`SELECT relname, last_vacuum FROM pg_stat_all_tables ORDER BY 2;`

This query returned:

relname | last_vacuum
-------------------------+-------------------------------
<table> | 2018-10-24 11:15:31.943684+00
<table2> | 2018-10-24 11:15:31.963803+00
<table3> | 2018-10-26 07:24:06.877427+00
<table4> | 2018-10-26 07:24:06.884089+00
<table5> | 2018-10-26 07:24:06.926874+00
<table6> | 2018-10-26 07:24:06.927982+00
...

I took some of these tables and grep'd the VERBOSE logs for them, getting
results like:

INFO: vacuuming "<table>"
...
INFO: "<table>": found 0 removable, 198 nonremovable row versions in 28
out of 104513 pages

INFO: vacuuming "<table2>"
INFO: index "<table2>" now contains 1816 row versions in 7 pages
INFO: "<table2>": found 0 removable, 6 nonremovable row versions in 1 out
of 325 pages

INFO: vacuuming "<table3>"
...
INFO: "<table3>": found 0 removable, 1 nonremovable row versions in 1 out
of 10924 pages

INFO: vacuuming "<table4>"
...
INFO: "<table4>": found 0 removable, 21 nonremovable row versions in 4 out
of 4 pages

INFO: vacuuming "<table5>"
INFO: "<table5>": found 0 removable, 2 nonremovable row versions in 1 out
of 412 pages

INFO: vacuuming "<table6>"
...
INFO: "<table6>": found 0 removable, 0 nonremovable row versions in 0 out
of 0 pages

So, at least according to the VERBOSE logs, it seems like these tables
_were_ vacuumed. Why the discrepancy?

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Kathleen Emerson (#1)
Re: pg_stat_all_tables.last_vacuum not always correct.

Kathleen Emerson <heavylivestock@gmail.com> writes:

I ran `VACUUM VERBOSE` on 10-28. After the VACUUM completed, I ran the
query
`SELECT relname, last_vacuum FROM pg_stat_all_tables ORDER BY 2;`
[ and saw old timestamps for some ]
So, at least according to the VERBOSE logs, it seems like these tables
_were_ vacuumed. Why the discrepancy?

The pg_stats mechanism is designed to drop messages when under sufficient
load, so if there was a whole lot going on besides the VACUUM, maybe
that would explain it.

regards, tom lane