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.
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