Re: MaxOffsetNumber for Table AMs

On Mon, May 3, 2021 at 8:03 AM Robert Haas <robertmhaas(at)gmail(dot)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