Subject: Re: refactoring relation extension and BufferAlloc(), faster COPY
Message-ID: <a1efd2f9-290e-b860-b490-a8bf6530b288@amazon.com>
Date: Tue, 21 Feb 2023 15:00:15 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.7.2
To: Andres Freund <andres@anarazel.de>, <pgsql-hackers@postgresql.org>,
	"Thomas Munro" <thomas.munro@gmail.com>, Melanie Plageman
	<melanieplageman@gmail.com>
CC: Yura Sokolov <y.sokolov@postgrespro.ru>, Robert Haas
	<robertmhaas@gmail.com>
References: <20221029025420.eplyow6k7tgu6he3@awork3.anarazel.de>
Content-Language: en-US
From: Jim Nasby <nasbyj@amazon.com>
In-Reply-To: <20221029025420.eplyow6k7tgu6he3@awork3.anarazel.de>
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/a1efd2f9-290e-b860-b490-a8bf6530b288%40amazon.com>
Precedence: bulk

On 10/28/22 9:54 PM, Andres Freund wrote:
> b) I found that is quite beneficial to bulk-extend the relation with
>     smgrextend() even without concurrency. The reason for that is the primarily
>     the aforementioned dirty buffers that our current extension method causes.
>
>     One bit that stumped me for quite a while is to know how much to extend the
>     relation by. RelationGetBufferForTuple() drives the decision whether / how
>     much to bulk extend purely on the contention on the extension lock, which
>     obviously does not work for non-concurrent workloads.
>
>     After quite a while I figured out that we actually have good information on
>     how much to extend by, at least for COPY /
>     heap_multi_insert(). heap_multi_insert() can compute how much space is
>     needed to store all tuples, and pass that on to
>     RelationGetBufferForTuple().
>
>     For that to be accurate we need to recompute that number whenever we use an
>     already partially filled page. That's not great, but doesn't appear to be a
>     measurable overhead.
Some food for thought: I think it's also completely fine to extend any 
relation over a certain size by multiple blocks, regardless of 
concurrency. E.g. 10 extra blocks on an 80MB relation is 0.1%. I don't 
have a good feel for what algorithm would make sense here; maybe 
something along the lines of extend = max(relpages / 2048, 128); if 
extend < 8 extend = 1; (presumably extending by just a couple extra 
pages doesn't help much without concurrency).