Re: Synthetic keys and index fillfactor

On Thu, Jan 12, 2023 at 9:25 PM Gavan Schneider <list(dot)pg(dot)gavan(at)pendari(dot)org>
wrote:

> With respect to “not 100%” for index packing…
>
> On 13 Jan 2023, at 11:17, David G. Johnston wrote:
>
> > I'll admit it seems a bit counter-intuitive, and there really isn't a
> "why"
> > offered in the user-facing documentation, but for 10% I'm not too worried
> > about it.
> >
> I have had the same “why” and this conversation got me thinking…
>
> The resulting “why” is based on what would be needed to maintain the ACID
> requirement while the index is both in use and being reorganised.
> Specifically, when one worker is using the index it has its “horizon” (ie.,
> version) of the index fixed, another worker might add to the data forcing
> an index rebalance, this means two, and maybe more, workers are in the
> same index each has their view/version of that one index. This “multiverse”
> is supported by multiple versions of any given node. This can be
> accommodated in the spare part of the index packing. The spare space allows
> the index rebalancing to be written into blocks that may already be
> buffered. Once the index rework is complete, WAL has been written, ACID
> compliance assured then the revised index (with all its freshly written
> nodes) is declared open for business and new workers will get this version
> for their horizon. Older workers will complete on their version, and the
> nodes supporting their view will then out of date, and the space can
> return to the block’s free area . The cycle repeats…
>
> Likely I have many of the details wrong but it’s an explanation that’s
> good enough for my purposes. :)
>
>
In short, heap pages can benefit from (but do not get by default) free
space to optimize MVCC-related work. Indexes can also benefit from this -
and it is also not built into the default - but also have their own
non-MVCC related maintenance needs that require space and the 10% of the
page default is set aside for this work.

David J.