On 13 Jan 2023, at 9:54, David G. Johnston wrote:
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.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)
On Thu, Jan 12, 2023 at 4:33 PM Gavan Schneider <list.pg.gavan@xxxxxxxxxxx> wrote:
Yep, and as the question was: is there any reason to use a fillfactor other than 100, the quoted material provides said reasons and thus "Yes" is the correct answer. We interpreted the same material in the same way, I just let the documentation speak for itself.
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 worried about it.
David J.
