Hello,
I have a big table in the actively working system, in which nothing is written for a long time, and nothing is read from it. Table size is 15GB (data only), indexes 150GB.
Since the table does not change, after a while it crosses the autovacuum_freeze_max_age and an aggressive vacuum is triggered. And it would be OK, but vacuuming of the table takes a long time, despite the fact that exactly the same scan was made a few days before and almost all pages are marked as frozen, which is confirmed by data from the log:
automatic aggressive vacuum of table "appdbname.appschemaname.applications": index scans: 1
pages: 0 removed, 2013128 remain, 0 skipped due to pins, 2008230 skipped frozen
tuples: 2120 removed, 32616340 remain, 0 are dead but not yet removable, oldest xmin: 4111875427
buffer usage: 2005318781 hits, 19536511 misses, 23903 dirtied
avg read rate: 4.621 MB/s, avg write rate: 0.006 MB/s
system usage: CPU: user: 26398.27 s, system: 335.27 s, elapsed: 33029.00 s
That is, if I understand it correctly, it says that there were (and actually are) 2013128 pages of which 2008230 were skipped, which leaves 4898 blocks to be scanned. I.e. it seems that the allocated 1GB (autovacuum_work_mem) should be enough to handle that amount of blocks and to avoid multiple scans of the indexes.
But, based on buffer usage, one can see that a huge amount of data is read, greatly exceeding not only the number of remaining unfrozen blocks, but also the size of the table and indexes taken together: 2 billion blocks, more than 15TB.
Is this a bug in Postgresql or am I interpreting the log data wrong?
Just in case, I'm using Postgresql version: 11.11.
autovacuum_vacuum_cost_delay: 2ms
autovacuum_vacuum_cost_limit: 8000
Thank you.
BR,
Mikhael
