Re: Synthetic keys and index fillfactor

On 1/12/23 16: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
>

"even a few updates or inserts will cause a sudden flood of page splits"
shouldn't matter for ever-increasing PK values, since you're always filling
that bottom right corner anyway.

This might be a killer if the applications update many indexed fields:
https://www.postgresql.org/docs/15/btree-implementation.html#BTREE-DELETION
"Changing the value of only one column covered by one index during
an|UPDATE|/always/necessitates a new set of index tuples — one for/each and
every/index on the table."

--
Born in Arizona, moved to Babylonia.