A couple thoughts about btree fillfactor

Now that the index options infrastructure is in, I am having a couple of
second thoughts about the specific behavior that's been implemented,
particularly for btree fillfactor.

1. The btree build code (nbtsort.c) is dependent on the assumption that
the fillfactor is at least 2/3rds. This is because we must put at least
two keys in each page, and with maximally sized keys (1/3rd page) it
might try to put only 0 or 1 tuple in a page if fillfactor is small.
However, maximally sized keys are certainly a corner case, and in more
usual situations a smaller fillfactor could be useful. I'm thinking
we could change the nbtsort.c code to work like "stop filling page
when fillfactor is exceeded AND there are at least two entries already".
Then any old fillfactor would work.

2. The build code is also set to force fillfactor 70 on non-leaf pages,
using the user-specified fillfactor only on leaf pages. I think this
is reasonable: if you're using a small fillfactor to avoid leaf page
splits, then there shouldn't be much need for new insertions on upper
pages, hence not much need for extra free space there; and having a
low fillfactor on upper pages will force the tree to be much deeper
and hence more expensive to search. In the other case (leaf fillfactor
higher than 70, indicating index is expected to be static), I'm still
not inclined to use the user fillfactor for non-leaf pages, because if
a split does occur it will be very expensive if we have to propagate
splits all the way up the tree. There's a case to be made for making
leaf and non-leaf fillfactors accessible as separate knobs, but I'm
inclined just to use a fixed value of 70 for non-leaf factor. The
index page split code is currently getting this wrong either way (it's
applying the user fillfactor to rightmost pages on all tree levels).

3. What should the minimum fillfactor be? The patch as submitted
set the minimum to 50% for all relation types. I'm inclined to
think we should allow much lower fillfactors, maybe down to 10%.
A really low fillfactor could be a good idea in a heavily updated
table --- at least, I don't think we have any evidence to prove
that it's not sane to want a fillfactor below 50%.

Comments?

regards, tom lane