{"kind": "relopts", "major": "18", "item": {"slug": "spgist-fillfactor", "name": "fillfactor", "name_zh": "SP-GiST indexes fillfactor", "category": "SP-GiST indexes", "summary": "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.", "aliases": ["spgist.fillfactor"], "versions": {"10": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=10", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 10.23", "label": "10.23", "major": "10", "channel": "historical", "revision": "4ff6f6193d91bf44fc701f657cc3295850bf786e42eafbfeed9f4879c3522613"}, "sources": [{"url": "/docs/10/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS", "path": "sql-createindex.html", "label": "PostgreSQL 10 English manual", "sha256": "7b1817d528a7de6c346b120316ca74abde75ccf0f2f21c34a72dbe032a88e403"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "description": ["The fillfactor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, 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 gradual degradation in the index's efficiency. B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected. If the table is static then fillfactor 100 is best to minimize the index's physical size, but for heavily updated tables a smaller fillfactor is better to minimize the need for page splits. The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods."]}, "11": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=11", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 11.22", "label": "11.22", "major": "11", "channel": "historical", "revision": "284f2186559443efefbb1ea3b00740c25dccd01e540ecbdb81dadf5127e54a0b"}, "sources": [{"url": "/docs/11/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS", "path": "sql-createindex.html", "label": "PostgreSQL 11 English manual", "sha256": "e7cfb07816fa696f1a7d5a3a8c43287796d08f48bddd6b2413279895b57ff2da"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "description": ["The fillfactor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, 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 gradual degradation in the index's efficiency. B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected. If the table is static then fillfactor 100 is best to minimize the index's physical size, but for heavily updated tables a smaller fillfactor is better to minimize the need for page splits. The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods."]}, "12": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=12", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 12.22", "label": "12.22", "major": "12", "channel": "historical", "revision": "0a1032bc0c23478646e9ee7d36edb066dca27b8540fd64d7fd47dcb6a8a6ccb4"}, "sources": [{"url": "/docs/12/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR", "path": "sql-createindex.html", "label": "PostgreSQL 12 English manual", "sha256": "deedeb34af9b62564d13ab2d379b31b19d4de6fb89a0e712826211914a0c5805"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "description": ["The fillfactor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, 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 gradual degradation in the index's efficiency. B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected. If the table is static then fillfactor 100 is best to minimize the index's physical size, but for heavily updated tables a smaller fillfactor is better to minimize the need for page splits. The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods."]}, "13": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=13", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 13.23", "label": "13.23", "major": "13", "channel": "historical", "revision": "346825c2d5fbebb2bd997afbb2d49edfd782dce050c0b934e84a883e5446a99f"}, "sources": [{"url": "/docs/13/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR", "path": "sql-createindex.html", "label": "PostgreSQL 13 English manual", "sha256": "a02f612ec64fc7396ab65a1ce4458cd6971bbd8715e2465ae3c5927d20af3840"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "description": ["The fillfactor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, 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 gradual degradation in the index's efficiency. B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected. If the table is static then fillfactor 100 is best to minimize the index's physical size, but for heavily updated tables a smaller fillfactor is better to minimize the need for page splits. The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods."]}, "14": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=14", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 14.24", "label": "14.24", "major": "14", "channel": "stable", "revision": "b6e7ed6d017bf1d63aae215156cde763babd657199df9fa10123733290c31260"}, "sources": [{"url": "/docs/14/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR", "path": "sql-createindex.html", "label": "PostgreSQL 14 English manual", "sha256": "f4d42bd08081490a491dc7bf4fa78b2a49da55ebcd0e7f843862548f7c66d70d"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "description": ["The fillfactor for an index is a percentage that determines 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 \u201c smooth out \u201d 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 64.4.2 is dependent on having some \u201c extra \u201d space on pages to store \u201c extra \u201d 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."]}, "15": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=15", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 15.19", "label": "15.19", "major": "15", "channel": "stable", "revision": "643ecd492fa284d4d55861f15cbd7c0c7451abce96c8d56485bb2729caba37d5"}, "sources": [{"url": "/docs/15/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR", "path": "sql-createindex.html", "label": "PostgreSQL 15 English manual", "sha256": "d0c693e7bccb80a261cfefed9f515fd2d24fb49395dfcbd61672d7915fa0666c"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "description": ["The fillfactor for an index is a percentage that determines 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 \u201c smooth out \u201d 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 67.4.2 is dependent on having some \u201c extra \u201d space on pages to store \u201c extra \u201d 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."]}, "16": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=16", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 16.15", "label": "16.15", "major": "16", "channel": "stable", "revision": "0080d7d37081f27216dc4aa4f6ad85ea24c0c0441e702e6d0b190a339fd76f62"}, "sources": [{"url": "/docs/16/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR", "path": "sql-createindex.html", "label": "PostgreSQL 16 English manual", "sha256": "ede5053e24573587783eee1735daf6cba76010c2773f10ad066d8cdaf7073c83"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "description": ["The fillfactor for an index is a percentage that determines 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 \u201c smooth out \u201d 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 67.4.2 is dependent on having some \u201c extra \u201d space on pages to store \u201c extra \u201d 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."]}, "17": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=17", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 17.11", "label": "17.11", "major": "17", "channel": "stable", "revision": "440f894c3464e7cb4fedc9846b948ca21cd2ffcef1697ff56a28eb641ee7c3d8"}, "sources": [{"url": "/docs/17/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR", "path": "sql-createindex.html", "label": "PostgreSQL 17 English manual", "sha256": "7fdb46b4a32c524457958417502f4036888552e0577352ee66f107019bbfffd6"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "description": ["The fillfactor for an index is a percentage that determines 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 \u201c smooth out \u201d 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 64.1.4.2 is dependent on having some \u201c extra \u201d space on pages to store \u201c extra \u201d 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."]}, "18": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=18", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 18.6", "label": "18.6", "major": "18", "channel": "stable", "revision": "eb6c2292fdf28e1b4635ab478eec3919411cdf4a6c07553387870e26201f3744"}, "sources": [{"url": "/docs/18/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR", "path": "sql-createindex.html", "label": "PostgreSQL 18 English manual", "sha256": "6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "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 \u201c smooth out \u201d 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 \u201c extra \u201d space on pages to store \u201c extra \u201d 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."]}, "19": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=19", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 19 beta 4", "label": "19 beta 4", "major": "19", "channel": "preview", "revision": "df6d20830d1a09734a2db6ffc3a6138ce80e669294e56a30f6b5d4a89d81aef1"}, "sources": [{"url": "/docs/19/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR", "path": "sql-createindex.html", "label": "PostgreSQL 19 English manual", "sha256": "e90c2051c335f6636fb68f3b03f98c3282445c537c99fd7d2e74e4943662e224"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "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 \u201c smooth out \u201d 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 \u201c extra \u201d space on pages to store \u201c extra \u201d 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."]}, "20": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=20", "label": "CREATE INDEX"}], "release": {"ref": "2c10c2ce4d7bcd57543a49ab402af02a39724e0a", "label": "20 devel", "major": "20", "channel": "devel", "revision": "ca199fbaaf6c4bd29cd5a98beaf34d9f4141c15d2f94d1d0202f3204ef18d09f"}, "sources": [{"url": "/docs/devel/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR", "path": "sql-createindex.html", "label": "PostgreSQL 20 English manual", "sha256": "33a4425c008678a8009be08f283f4f536180f45ebf9f6c2183f06daa3cf385d8"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "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 \u201c smooth out \u201d 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 \u201c extra \u201d space on pages to store \u201c extra \u201d 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."]}}, "content_hash": "cb238eff4a7157bca8325a55aa8802ecccb05e144110686a328f5cb7326cb4df", "imported_at": "2026-09-27T09:57:58.921"}, "snapshot": {"facts": [{"label": "Applies to", "value": "SP-GiST indexes"}, {"label": "Value type", "value": "integer"}], "related": [{"url": "/docs/sql/create-index/?v=18", "label": "CREATE INDEX"}], "release": {"ref": "Local English manual 18.6", "label": "18.6", "major": "18", "channel": "stable", "revision": "eb6c2292fdf28e1b4635ab478eec3919411cdf4a6c07553387870e26201f3744"}, "sources": [{"url": "/docs/18/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR", "path": "sql-createindex.html", "label": "PostgreSQL 18 English manual", "sha256": "6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580"}], "sections": [{"title": "Index access method", "paragraphs": ["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\u2013100 refer specifically to B-tree."]}], "signature": "WITH (fillfactor = value)", "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 \u201c smooth out \u201d 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 \u201c extra \u201d space on pages to store \u201c extra \u201d 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."]}, "comparison": {"left": "17", "right": "18", "status": "unchanged", "diff": ""}}