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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster