Re: Synthetic keys and index fillfactor

On 13 Jan 2023, at 9:54, David G. Johnston wrote:

> On Thu, Jan 12, 2023 at 3:45 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>>
>> (This mostly pertains to recreating a PK on an existing table.)
>>
>> Is there any reason to have the PK index on an ever-increasing field
>> (for
>> example SERIAL, sequence or timestamp fed by clock_timestamp() at
>> time
>> zone
>> 'UTC') be anything but fillfactor=100?
>>
>> New records will always be added to the "lower right hand corner" of
>> the
>> tree, so having 20% empty space in the rest of the tree would just
>> waste
>> space (mainly buffers, but disk space could even start to add up on
>> Very
>> Big Tables).
>>
>>
> Yes, at least that is what I gather from the advice on the CREATE
> INDEX
> page.
>
> https://www.postgresql.org/docs/15/sql-createindex.html
>
My reading would say otherwise even in the context of monotonic serial
additions

Quote —
You should only consider [fillfactor 100] when you are *completely*
sure that the table is static
(i.e. that it will *never* be affected by either inserts or updates). A
fillfactor setting of 100 otherwise
risks harming performance: even a few updates or inserts will cause a
sudden flood of page splits.
(My emphasis added)

Checking
https://www.postgresql.org/docs/current/btree-implementation.html#67.4.1
My understanding of the “leaf” and “internal” pages of the
B-tree structure is that the the binary search gets it effectiveness
when starting more or less in the middle of the existing index range and
is most efficient when the tree is well balanced (ie., “leaf” values
less-than and greater-than in equal numbers to the left and right at
each “internal page”). There is a point when the lopsided tree that
develops from always adding a bigger index value to the lower right
corner will require a “root page split” (aka rebalance?). I am
thinking a bit of padding helps make this process more in-place and less
like a full index rebuild which is something that would really hurt on a
VBTˇ as more items get added.

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a
well-known solution to every human problem — neat, plausible, and
wrong.
— H. L. Mencken, 1920