Currently I am working a lot with cluster consist a few of big tables. About 2-3 TB. These tables are heavily updated, some rows are removed, new rows are inserted... Kind of typical OLTP workload.
Physical table size keeps mostly stable while regular VACUUM is working. It is fast enough to clean some place from removed rows.
But time to time "to prevent wraparound" comes. And it works like 8-9 days. During that time relation size starting to expand quickly. Freezing all blocks in such table takes a lot of time and bloat is generated much more quickly.
Of course after aggressive vacuum finishes table are not shrink back (some kind of repacking required). And even after repacking - relation shrinking causes all cluster to stuck for some time (due exclusive locking, see (1)).
So, I was thinking about it and I saw two possible solutions:
1. Track two block pointers for aggressive vacuum. One is to freeze all blocks and other is to perform regular vacuum on non-all-visible blocks. Second one is circular (could process table multiple times while first one is moving from start to end of the table). And some parameters to spread resources between pointers is required.
2. Separate "to prevent wraparound" from regular Vacuum to allow them run concurrently. But it seems to be much more work here.
Could you please share some thoughts on it? Is it worth to be implemented?
