Re: Initdb-time block size specification

On Fri, Jun 30, 2023 at 4:12 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
>
>
> 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

Cool, thanks for the video links; will review.

David