Index Fill Factor
Fill factor leaves free space in index pages at creation time so future inserts and updates fit in place, reducing page splits at the cost of a larger, colder index.
Definition
Fill factor is the percentage of an index page filled during index build or maintenance, leaving the remainder for growth. When updates widen rows or inserts land mid-index, pages with free space absorb them; full pages split into two half-full pages, causing fragmentation, extra I/O, and buffer churn.
Context and scope
Tuned on monotonically inserted, randomly inserted, and HOT-update-heavy PostgreSQL workloads; largely irrelevant for append-only indexes where new rows always land on the rightmost page.
Common misconceptions
- Lower fill factor is always safer — it permanently enlarges the index, diluting cache density for rarely updated data
- Fill factor prevents all page splits — it only reduces splits from in-page growth; random inserts still split wherever they land
- Page splits are a correctness problem — they are pure performance and fragmentation cost
Anti-patterns
- Setting a low fill factor on static lookup indexes and paying cache and I/O cost for no update benefit
- Forgetting REINDEX resets fill factor to default, losing the setting after routine maintenance
- Tuning fill factor instead of enabling HOT updates, which often removes the update-driven page pressure entirely
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/sql-createindex.html
What it is
B-tree pages hold many rows. A full page cannot accept a new row in key order where it belongs, so it splits: half the rows move to a new page. Fill factor pre-reserves space so that updates and inserts have room, deferring splits and keeping physical order closer to logical order.
How it works
A fill factor of 90 leaves a tenth of each page empty at build time. It matters most for updated index columns and random-key inserts. In PostgreSQL, heap-only tuple updates avoid touching indexes when indexed columns are unchanged, which is usually a better first fix than lowering fill factor.
When to tune
Randomly inserted UUID indexes, frequently updated indexed columns, and tables with measured page-split churn. Set it per index, not globally, and verify via pgstattuple leaf fragmentation stats.
Pitfalls
The reserved space is permanent overhead until rebuilt. Fill factor does not help append-only rightmost-page inserts. REINDEX without storage parameters resets to default, silently undoing the tuning during maintenance windows.