On Mon, Mar 8, 2021 at 10:57 AM Robert Haas <robertmh...@gmail.com> wrote: > Yes, I agree that it's good to postpone this to a future release, and > that thinking through the consequences is not so easy.
The current plan is to commit something like Masahiko's skip_index_vacuum.patch for Postgres 14. The latest version of that patch (a reduced-scope version of Masahiko's patch without any changes to MaxHeapTuplesPerPage) is available from: https://postgr.es/m/CAD21AoAtZb4+HJT_8RoOXvu4HM-Zd4HKS3YSMCH6+-W=bdy...@mail.gmail.com The idea is to "unify the vacuum_cleanup_index_scale_factor feature from Postgres 11 with the INDEX_CLEANUP feature from Postgres 12". This is the broader plan to make that "unification" happen for Postgres 14: https://postgr.es/m/CAH2-WzkYaDdbWOEwSSmC65FzF_jRLq-cxrYtt-2+ASoA156X=w...@mail.gmail.com So, as I said, any change to MaxHeapTuplesPerPage is now out of scope for Postgres 14. > One possible > consequence that I'm concerned about is sequential scan performance. > For an index scan, you just jump to the line pointer you want and then > go get the tuple, but a sequential scan has to loop over all the line > pointers on the page, and skipping a lot of dead ones can't be > completely free. A small increase in MaxHeapTuplesPerPage probably > wouldn't matter, but the proposed increase of almost 10x (291 -> 2042) > is a bit scary. I agree. Maybe the real problem here is that MaxHeapTuplesPerPage is a generic constant. Perhaps it should be something that can vary by table, according to practical table-level considerations such as projected tuple width given the "shape" of tuples for that table, etc. Certain DB systems that use bitmap indexes extensively allow this to be configured per-table. If you need to encode a bunch of TIDs as bitmaps, you first need some trivial mapping from TIDs to integers (before you even build the bitmap, much less compress it). So even without VACUUM there is a trade-off to be made. It is *roughly* comparable to the trade-off you make when deciding on a page size. What I really want to do for Postgres 14 is to establish the principle that index vacuuming is theoretically optional -- in all cases. There will be immediate practical benefits, too. I think it's important to remove the artificial behavioral differences between cases where there are 0 dead tuples and cases where there is only 1. My guess is that 99%+ append-only tables are far more common than 100% append-only tables in practice. > It's also a little hard to believe that letting almost > 50% of the total space on the page get chewed up by the line pointer > array is going to be optimal. If that happens to every page while the > amount of data stays the same, the table must almost double in size. > That's got to be bad. I think that we should be prepared for a large diversity of conditions within a given table. It follows that we should try to be adaptive. The reduced-scope patch currently tracks LP_DEAD line pointers at the heap page level, and then applies a count of heap blocks with one or more LP_DEAD line pointers (could be existing or just pruned by this VACUUM) to determine a count of heap pages. This is used to determine a threshold at which index vacuuming should be forced. Currently we have a multiplier constant called SKIP_VACUUM_PAGES_RATIO, which is 0.01 -- 1% of heap blocks. Of course, it's possible that LP_DEAD line pointers will be very concentrated, in which case we're more aggressive about skipping index vacuuming (if you think of it in terms of dead TIDs instead of heap blocks we're aggressive, that is). The other extreme exists too: LP_DEAD line pointers may instead be spread diffusively across all heap pages, in which case we are unlikely to ever skip index vacuuming outside of cases like anti-wraparound vacuum or insert-driven vacuum to set VM bits. The next iteration of the high-level "granular vacuum" project (which will presumably target Postgres 15) should probably involve more complicated, qualitative judgements about LP_DEAD line pointers in the heap. Likewise it should care about individual needs of indexes, which is something that Masahiko experimented with in earlier drafts of the patch on this thread. The needs of each index can be quite different with bottom-up index deletion. We may in fact end up adding a new, moderately complicated cost model -- it may have to be modelled as an optimization problem. In short, I think that thinking more about the logical state of the database during VACUUM is likely to pay-off ("heap blocks vs dead tuples" is one part of that). VACUUM should be a little more qualitative, and a little less quantitative. The fact that we currently don't stuff like that (unless bottom-up index deletion counts) is not an inherent limitation of the design of VACUUM. I'm not entirely sure how far it can be pushed, but it seems quite promising. > The whole thing would be more appealing if there > were some way to exert exponentially increasing back-pressure on the > length of the line pointer array - that is, make it so that the longer > the array is already, the less willing we are to extend it further. > But I don't really see how to do that. There are also related problems in the FSM, which just doesn't care enough about preserving the original layout of tables over time. See for example the recent "non-HOT update not looking at FSM for large tuple update" thread. I believe that the aggregate effect of inefficiencies like that are a real problem for us. The basic design of the FSM hasn't been touched in over 10 years. There are non-linear effects in play, in all likelihood. "Rare" harmful events (e.g. silly space reuse in the heap, unnecessary page splits from version churn) will tend to cause irreversible damage to locality of access if allowed to occur at all. So we need to recognize those heap pages where it's possible to preserve a kind of pristine state over time. Heap pages that are subject to constant churn from updates exist -- most apps have some of those. But they're also usually a small minority of all heap pages, even within the same heap relation. -- Peter Geoghegan
