🐘If you rebuild your indexes without knowing their #fillfactor you may not really know what you are doing. Here is a demo-thread ⤵️
Conversation
🐘I've an empty table with one index. size =8k, nearly empty.
1
🐘I insert 5 million rows with increasing value (think sequence, generator, current timestamp, ordered file import...) that takes 110 MB and still 110 MB after an index rebuild. Happy with that?
1
🐘I can pack it further with a 100% fillfactor (the default is 90%). Now takes 100MB. Smaller is better?
1
1
🐘Inserting the same number of rows now takes 100MB (was 110MB when fillfactor was default). All perfect?
1
🐘Inserting the same number of rows, but not increasing values, now takes 200MB. Is that bad?
1
🐘Another same number of rows, sill in same range of values, increasing by 100MB only. Was not so bad?
1
🐘You decide to rebuild, but which fillfactor? 100 to pack for always increasing values? 90 because it is the default? 80 to give more room for same range values? With inserts on same range, leaf pages will be between 100 (full) and 50 (split) so it may eventually average to 75
2
Replying to
I am prepared to say that ff 100 for B-Trees is practically always a bad idea. The risk of correlated page split storms is way too high to ever make up for the space savings. Split choice algorithm in Postgres is quite smart from version 12 on.
1
2
Replying to
Sure, and in OLTP concurrent access may break the always increasing idea. What about DWH tables bulk loaded with index created after? We don't need 10% wastage for them. Is ff 100 ok?
1
1
Replying to
Theoretically yes, but ff 96-98 is almost as space efficient but much less risky. Page splits are inevitable when insertions take place, but the rate over time still matters a lot. Note that Postgres 12 internally uses ff 96 for pages full of the same duplicate value.
"Waves of misery after index creation" paper describes the issues with FF in depth: btw.informatik.uni-rostock.de/download/tagun (diagrams are good). Issues seem to be treated as trivialities by DB researcher community - which seems like a mistake to me. Surprisingly subtle/interesting area IME.
2