Re: A couple thoughts about btree fillfactor

On Mon, Jul 10, 2006 at 12:36:34PM -0400, Tom Lane wrote:
> 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. ...  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. ...  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....
> 
> 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

I would like to place my vote for supporting fillfactors less than
50%. Like you mentioned, a heavily updated table could be forced to
a page split before VACUUM freed the items and made them available
for reuse. I also think that points 1 and 2 are reasonable.

Ken