On Mon, May 3, 2021 at 8:03 AM Robert Haas <robertmh...@gmail.com> wrote: > It's reasonable to wonder. I think it depends on whether the problem > is bloat or just general slowness. To the extent that the problem is > bloat, bottom-index deletion will help a lot, but it's not going to > help with slowness because, as you say, we still have to dirty the > pages. And I am pretty confident that slowness is a very significant > part of the problem here.
It's all going to depend on workload of course -- we'll need to wait and see what users still complain about with Postgres 14 to really have some idea. You only freshly dirty those leaf pages that weren't already dirty, and the costs will be much more linear, so it's a complicated question. Here is a more modest statement that might be more convincing: The *relative* importance of making something like HOT more robust to things like long-running xacts has increased now that we have bottom-up index deletion. We could improve things here by adding something like zheap, which allows a HOT chain to mostly live in UNDO, and therefore pretty much become arbitrarily long. This seems plausible because users will accept that UPDATEs that modify one or more indexed columns kinda suck, as long as there is never any truly pathological performance. Whereas users will not easily accept that HOT (or something like it) doesn't quite work well enough to make relation sizes stable when they avoid updating indexed columns. I don't think that even the absence of UPDATEs that logically modify indexes and the absence of long running transactions (that hold up cleanup) is sufficient to make HOT work well enough to keep table sizes stable over time. Minor inefficiencies (e.g. LP_DEAD line pointer bloat) will tend to aggregate over time, leading to heap fragmentation. -- Peter Geoghegan
