Re: Lowering the ever-growing heap->pd_lower

On Thu, 2 Dec 2021 at 11:17, Daniel Gustafsson <daniel(at)yesql(dot)se> wrote:
>
> This thread has stalled, and the request for benchmark/test has gone unanswered
> so I'm marking this patch Returned with Feedback. Please feel free to resubmit
> this patch if it is picked up again.

Well then, here we go. It took me some time to find the time and
examples, but here we are. Attached is v7 of the patchset, which is a
rebase on top of the latest release, with some updated comments.

Peter Geoghegan asked for good arguments for the two changes
implemented. Below are my arguments detailed, with adversarial loads
that show the problematic behaviour of the line pointer array that is
fixed with the patch.

Kind regards,

Matthias van de Meent

Truncating lp_array to 0 line pointers
===========================

On 32-bit pre-patch systems the heap grows without limit; post-patch
the relation doesn't grow beyond 16kiB (assuming no other sessions in
the database):

> -- setup
> CREATE TABLE tst (filler text);
> ALTER TABLE tst SET (autovacuum_enabled = off, fillfactor = 10); -- disable autovacuum, and trigger pruning more often
> ALTER TABLE tst ALTER COLUMN filler SET STORAGE PLAIN;
> INSERT INTO tst VALUES ('');
> -- processing load
> VACUUM tst; UPDATE tst SET filler = repeat('1', 8134); -- ~ max size heaptuple in MAXIMUM_ALIGNOF = 4 systems

On 64-bit systems this load is not an issue due to slightly larger
tolerances in the FSM.

# Truncating lp_array during pruning
===========================

The following adversarial load grows the heap relation, but with the
patch the relation keeps its size. The point being that HOT updates
can temporarily inflate the LP array significantly, and this patch can
actively combat that issue while we're waiting for the 2nd pass of
vacuum to arrive.

> -- Initialize experiment
> TRUNCATE tst;
> INSERT INTO tst SELECT null;
> UPDATE tst SET filler = null;
> VACUUM tst;
>
> -- start workload by filling all line pointer slots with minsized heap tuples
> do language plpgsql $$
> begin
> for i in 1..289 loop
> update tst set filler = null;
> end loop;
> end;
> $$;
> -- all line pointers on page 0 are now filled with hot updates of 1st line pointer
>
> -- hot-update hits the page; pruning is first applied
> -- All but first and last LP are now empty. new tuple is inserted at
> -- offset=2
> UPDATE tst SET filler = null;
>
> -- Insert large tuple, filling most of the now free space between the end of
> -- the max size line pointer array
> UPDATE tst SET filler = repeat('1', 7918);
>
> -- insert slightly smaller tuple, that is ~ the size of the unused space in
> -- the LP array
> UPDATE tst SET filler = repeat('1', 1144);
>
> -- reset experiment to initialized state
> UPDATE tst SET filler = null;