I am vacuuming a very large (718GB) table containing a lot of dead tuples and 4 indexes.
VACUUM ANALYSE VERBOSE bigtable;
This vacuum has been running for over 11 hours (!) now and I am trying to estimate the remaining time for it to complete, or, at least understand in which "phase" the vacuum process is: scanning heap? cleaning indexes? vacuuming heap? I am referring to the phases described in Vacuum Progress Reporting, introduced in 9.6.
Since I am running Postgres 9.5, I do not have access to this nifty feature. I am trying to make sense of the "verbose" vacuum messages, which look like this:
INFO: scanned index "bigtable_timestamp" to remove 11184548 row versions DETAIL: CPU 77.05s/336.97u sec elapsed 745.75 sec INFO: scanned index "bigtable_trip_id" to remove 11184548 row versions DETAIL: CPU 87.86s/371.96u sec elapsed 846.21 sec INFO: "bigtable": removed 11184548 row versions in 157984 pages DETAIL: CPU 3.50s/1.93u sec elapsed 22.42 sec INFO: scanned index "bigtable_pkey" to remove 11184526 row versions DETAIL: CPU 72.27s/330.03u sec elapsed 704.25 sec INFO: scanned index "bigtable_step_id" to remove 11184526 row versions DETAIL: CPU 90.21s/370.86u sec elapsed 850.30 sec
The above sequence of messages has repeated itself for the past 11 hours, with no end in sight.
Is there any way to tell, based on those messages, in which phase the vaccum is now?
I've also tried to look at the
bytes-read of the vacuum process on Linux, as suggested in this answer, and compare it to the total_bytes of the table and indexes, but this didn't work for me. The process has already read 5.1TB (5101054656512) from the disk although the table including all indexes is estimated at "only" 718GB (