pg_stat_all_tables.last_vacuum not always correct.

From: Kathleen Emerson <heavylivestock(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: pg_stat_all_tables.last_vacuum not always correct.
Date: 2018-10-30 14:57:34
Message-ID: CAKsJiwqZ699iBG2iVJjb5VOWqbGVe4jHA4v16qVXauZH4Y5DOA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2018-10-30 15:21:36 Re: Portworx snapshots
Previous Message Олег Самойлов 2018-10-30 14:47:44 Is the centos repository for postgresql 10 is broken now?