On Thu, Jan 12, 2023 at 9:25 PM Gavan Schneider <list.pg.gavan@xxxxxxxxxxx> 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.
