We have a table that will hold records on the 108 order.
╔══════════╦═════╦══════╦══════════════════════════╗
║ id ║ a ║ b ║ ...many other columns... ║
╠══════════╬═════╬══════╬══════════════════════════╣
║ 1 ║ 1 ║ 118 ║ ... ║
║ 2 ║ 1 ║ 1022 ║ ... ║
║ 3 ║ 5 ║ 118 ║ ... ║
║ 4 ║ 3 ║ 118 ║ ... ║
║ ... ║ ... ║ ... ║ ... ║
║ 10020 ║ 5 ║ 1022 ║ ... ║
║ 10021 ║ 1 ║ 118 ║ ... ║
║ ... ║ ... ║ ... ║ ... ║
║ 5000000 ║ 2 ║ 30 ║ ... ║
║ 5000001 ║ 1 ║ 30 ║ ... ║
║ ... ║ ... ║ ... ║ ... ║
║ 28218321 ║ 1 ║ 118 ║ ... ║
║ 28218322 ║ 4 ║ 57 ║ ... ║
╚══════════╩═════╩══════╩══════════════════════════╝
Column a
always holds one of 5 values. Column b
always holds one of ~5000 values. a
and b
are unrelated. So for every value of a
there will be at least tens of millions of table rows, and for every value of b
there will be at least hundreds of thousands of rows.
A lot of our queries are based on WHERE a=<value>
or WHERE b=<value>
so we are indexing those columns (two separate, single-column btree indexes). I am trying to get a sense of what the optimal choice of FILLFACTOR would be for this situation.
This table does not receive many UPDATEs, however it receives large bursts of INSERTs (on the order of 106) in short periods of time. At the same time it is under moderate, constant read load.
If my understanding of btree index fillfactor is correct it sounds as though we may benefit for a fairly low fillfactor for a
and b
indexes, allowing lots of empty space for new row references to be added to the leaf pages as new records come in with the same a
or b
value as previous ones. Is this correct?
Is this a situation where a low fillfactor would be beneficial and if so what approximate value should we be looking at using?