Re: Initdb-time block size specification

On 6/30/23 22:05, David Christensen wrote:
> On Fri, Jun 30, 2023 at 2:39 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>>
>> ...
>>
>> Besides this, I've not really heard any convincing justification for needing
>> this in the first place.
>
> Doing this would open up experiments in larger block sizes, so we
> would be able to have larger indexable tuples, say, or be able to
> store data types that are larger than currently supported for tuple
> row limits without dropping to toast (native vector data types come to
> mind as a candidate here). We've had 8k blocks for a long time while
> hardware has improved over 20+ years, and it would be interesting to
> see how tuning things would open up additional avenues for performance
> without requiring packagers to make a single choice on this regardless
> of use-case. (The fact that we allow compiling this at a different
> value suggests there is thought to be some utility having this be
> something other than the default value.)
>
> I just think it's one of those things that is hard to evaluate without
> actually having something specific, which is why we have this patch
> now.
>

But it's possible to evaluate that - you just need to rebuild with a
different configuration option. Yes, allowing doing that at initdb is
simpler and allows testing this on systems where rebuilding is not
convenient. And having a binary that can deal with any block size would
be nice too.

In fact, I did exactly that a year ago for a conference, and I spoke
about it at the 2022 unconference too. Not sure if there's recording
from pgcon, but there is one from the other conference [1][2].

The short story is that the possible gains are significant (say +50%)
for data sets that don't fit into RAM. But that was with block size set
at compile time, the question is what's the impact of making it a
variable instead of a macro ....

[1] https://www.youtube.com/watch?v=mVKpoQxtCXk
[2] https://blog.pgaddict.com/pdf/block-sizes-postgresvision-2022.pdf

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company