Documentation / Storage Parameters / SP-GiST indexes
fillfactor
SP-GiST indexes fillfactor
Controls how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index builds, and also when extending the index at the right (adding new largest key values). If pages subsequently become completely full, they will be split, leading to fragmentation of the on-disk index structure. B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected.
Reading PostgreSQL 18.6.
Description
Controls how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index builds, and also when extending the index at the right (adding new largest key values). If pages subsequently become completely full, they will be split, leading to fragmentation of the on-disk index structure. B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected.
B-tree indexes on tables where many inserts and/or updates are anticipated can benefit from lower fillfactor settings at CREATE INDEX time (following bulk loading into the table). Values in the range of 50 - 90 can usefully “ smooth out ” the rate of page splits during the early life of the B-tree index (lowering fillfactor like this may even lower the absolute number of page splits, though this effect is highly workload dependent). The B-tree bottom-up index deletion technique described in Section 65.1.4.2 is dependent on having some “ extra ” space on pages to store “ extra ” tuple versions, and so can be affected by fillfactor (though the effect is usually not significant).
In other specific cases it might be useful to increase fillfactor to 100 at CREATE INDEX time as a way of maximizing space utilization. You should only consider this when you are completely sure that the table is static (i.e. that it will never be affected by either inserts or updates). A fillfactor setting of 100 otherwise risks harming performance: even a few updates or inserts will cause a sudden flood of page splits.
The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods.
- Applies to
- SP-GiST indexes
- Value type
- integer
Usage
WITH (fillfactor = value)Index access method
This entry applies to SP-GiST indexes. Set it with CREATE INDEX WITH or ALTER INDEX SET. Read defaults and behavior for this specific access method.
The manual groups four access methods under fillfactor. Its default of 90 and range of 10–100 refer specifically to B-tree.
Related entries
Documentation and source
Source build
- Version
- 18.6
- Build
- Local English manual 18.6
- Source fingerprint
eb6c2292fdf28e1b4635ab478eec3919411cdf4a6c07553387870e26201f3744
Back to Storage Parameters · Recorded in PostgreSQL 10 through 20; the first sample is not necessarily its introduction.