Postgresql – Postgres VACUUM VERBOSE information

postgresqlvacuum

I am using Postgres with psycopg2 and sometimes when I run VACUUM VERBOSE table_name; it gives me a line giving the oldest xmin and sometimes it does not. Is there a way to make sure that it always provides it?

Here is the output one that where it didn't give me the value:

INFO:  vacuuming "public.homeless_by_coc"

INFO:  scanned index "homeless_by_coc_pkey" to remove 86530 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "homeless_by_coc": removed 86530 row versions in 1280 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "homeless_by_coc_pkey" now contains 0 row versions in 239 pages
DETAIL:  86530 index row versions were removed.
236 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "homeless_by_coc": found 86530 removable, 0 nonremovable row versions in 1280 out of 1280 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.02u sec elapsed 0.03 sec.

INFO:  "homeless_by_coc": truncated 1280 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.01 sec.

Here is one time where it did give it to me:

INFO:  vacuuming "public.homeless_by_coc"

INFO:  scanned index "homeless_by_coc_pkey" to remove 86530 row versions
DETAIL:  CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s

INFO:  "homeless_by_coc": removed 86530 row versions in 1280 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

INFO:  index "homeless_by_coc_pkey" now contains 0 row versions in 239 pages
DETAIL:  86530 index row versions were removed.
236 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  "homeless_by_coc": found 86530 removable, 0 nonremovable row versions in 1280 out of 1280 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 16410
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.03 s.

INFO:  "homeless_by_coc": truncated 1280 to 0 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

You will notice that the second time there is the following line:

DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 16410

Best Answer

This does not appear to be a conditional. The extra information was added in v10 with commit 9eb344faf5, and is always printed in that version and up for a VACUUM VERBOSE (it apparently is not printed for a CLUSTER, which does seem undesirable).

If you want consistent behavior, you need to upgrade all your database instances to the same version of PostgreSQL.

Related Question