On Thu, Jan 12, 2023 at 3:45 PM Ron <ronljohnsonjr@xxxxxxxxx> 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.
"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.
Born in Arizona, moved to Babylonia.
