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@gmail.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)