On Fri, Apr 8, 2022 at 1:29 PM Robert Haas <robertmh...@gmail.com> wrote: > Hmm. I wonder if we could teach the system to figure out which of > those things is happening. In the case that I'm worried about, when > we're considering growing the line pointer array, either the line > pointers will be dead or the line pointers will be used but the tuples > to which they point will be dead. In the case you describe here, there > should be very few dead tuples or line pointers in the page. Maybe > when the number of line pointers starts to get big, we refuse to add > more without checking the number of dead tuples and dead line pointers > and verifying that those numbers are still small. Or, uh, something.
It seems like the central idea is that we think in terms of "versions per logical row", even in low level code that traditionally hasn't made those kinds of distinctions. Ideally we could structure pruning a little bit more like a B-Tree page split, where there is an explicit "incoming tuple" that won't fit (without splitting the page, or maybe doing some kind of deletion). If the so-called incoming tuple that we'd rather like to fit on the page is an insert of an unrelated row, don't allow it (don't prune, give up). But if it's an update (especially a hot update), be much more permissive about allowing it, and/or going ahead with pruning in order to make sure it happens. I like Andres' idea of altering LP_REDIRECTs just to be able to use up lower line pointers first. Or preserving a few extra LP_UNUSED items on insert. Those seem promising to me. > One fly in the ointment is that if we refuse to expand the line > pointer array, we might extend the relation instead, which is another > kind of bloat and thus not great. But if the line pointer array is > somehow filling up with tons of dead tuples, we're going to have to > extend the relation anyway. I suspect that in some circumstances it's > better to just accept that outcome and hope that it leads to some > pages becoming empty, thus allowing their line pointer arrays to be > reset. I agree. Sometimes the problem is that we don't cut our losses when we should -- sometimes just accepting a limited downside is the right thing to do. Like with the FSM; we diligently use every last scrap of free space, without concern for the bigger picture. It's penny-wise, pound-foolish. -- Peter Geoghegan
