On 2/15/23 22:57, Mikhail Balayan wrote: > 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?
