From: Gavan Schneider <list.pg.gavan@pendari.org>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>
Subject: Re: Synthetic keys and index fillfactor
Date: Fri, 13 Jan 2023 15:25:01 +1100
Message-ID: <EC161126-5364-4A54-90C0-CAE013530F77@pendari.org>
In-Reply-To: 
 <CAKFQuwarr=AOrb5+GTuBtyfmxC25cRbEKEeScj=sFtYz4Ci+1w@mail.gmail.com>
References: <d50cc687-d903-3312-cee2-4f4fdc864b0a@gmail.com>
 <CAKFQuwanbq3T1xiysk=kP9Oy5qtNyXdFG8asPxttBq_fJg==8w@mail.gmail.com>
 <ACB3A1E6-50C0-49C5-95E1-B05F3DC31CF3@pendari.org>
 <CAKFQuwarr=AOrb5+GTuBtyfmxC25cRbEKEeScj=sFtYz4Ci+1w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/EC161126-5364-4A54-90C0-CAE013530F77%40pendari.org>
Precedence: bulk

With respect to =E2=80=9Cnot 100%=E2=80=9D for index packing=E2=80=A6

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 worri=
ed
> about it.
>
I have had the same =E2=80=9Cwhy=E2=80=9D and this conversation got me th=
inking=E2=80=A6

The resulting =E2=80=9Cwhy=E2=80=9D is based on what would be needed to m=
aintain the ACID requirement while the index is both in use and being reo=
rganised. Specifically, when one worker is using the index it has its =E2=
=80=9Chorizon=E2=80=9D (ie., version) of the index fixed, another worker =
might add to the data forcing an index rebalance,  this means two, and ma=
ybe more, workers are in the same index each has their view/version of th=
at one index. This =E2=80=9Cmultiverse=E2=80=9D 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 wr=
itten 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 wi=
ll complete on their version, and the nodes supporting their view will th=
en out of date,  and the space can return to the block=E2=80=99s free are=
a . The cycle repeats=E2=80=A6

Likely I have many of the details wrong but it=E2=80=99s an explanation t=
hat=E2=80=99s good enough for my purposes. :)

Gavan Schneider
=E2=80=94=E2=80=94
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a wel=
l-known solution to every human problem =E2=80=94 neat, plausible, and wr=
ong.
=E2=80=94 H. L. Mencken, 1920