{"Entry":{"collection":"relopts","key":"hash-fillfactor","name":"fillfactor","aliases":["hash.fillfactor"],"metadata":{"aliases":["hash.fillfactor"],"category":"Hash indexes","content_hash":"b425bc992240074ed3ff51764ee6a34cc42b150adb956fa19c5ea63a8862ce27","derived_content_hash":"91bf09f095b84c7892c9d99d62e94c07f6911d16740fca90231ac2d3367209ce","imported_at":"2026-09-30T17:44:22.155996+08:00","name":"fillfactor","name_zh":"Hash indexes fillfactor","repair_provenance":{"original_source_revision":"","receipt_sha256":"b818e437e224fa95dd7f297c6adcc90cc554ee8ede845bd4ad366b93cbc754a4","repair":"wiki-repair-20261023"},"slug":"hash-fillfactor","source_rev":"wiki-repair-20261023:bb2b3ebf568c75365a4687c819bd764f7e5288eccc12f2fa2eb43469bcf5d6e5","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."}},"Definition":{"Collection":"relopts","Key":"hash-fillfactor","SourceDatabase":"center","Version":"18","SourceTable":"relopt","SourceKey":"hash-fillfactor","SourceRevision":"eb6c2292fdf28e1b4635ab478eec3919411cdf4a6c07553387870e26201f3744","Facts":{"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."],"description_zh":"控制索引方法尝试将索引页填充到多满。对于 B-树，在初始构建索引时，以及在右侧扩展索引（加入新的最大键值）时，叶子页都会填充到这一百分比。如果页面随后变成全满，就会发生分裂，从而导致磁盘上的索引结构碎片化。B-树默认使用 90 的填充因子，但也可以选择 10 到 100 之间的任意整数值。 对预计会有大量插入和/或更新的表，在 CREATE INDEX 时为 B-树索引设置较低的填充因子会有好处（即在向表批量装载数据之后）。50 到 90 范围内的值可以有效地“平滑”B-树索引生命周期早期页面分裂的速率（这样降低填充因子甚至可能减少页面分裂的绝对数量，不过这一效果高度依赖工作负载）。第 65.1.4.2 节中描述的 B-树自底向上索引删除技术依赖于页面上有一些“额外”空间来存储“额外”的元组版本，因此也会受到填充因子影响（不过通常影响不大）。 在某些其他特定情况下，在 CREATE INDEX 时将填充因子提高到 100 可能有助于最大化空间利用率。只有在完全确定该表是静态的（即永远不会受到插入或更新影响）时，才应考虑这样做。否则，将填充因子设为 100 有损害性能的风险：即便只有少量更新或插入，也会导致页面突然大量分裂。 其他索引方法以不同但大致类似的方式使用填充因子；默认填充因子因方法而异。","description_zh_html":"\u003cp\u003e\n      控制索引方法尝试将索引页填充到多满。对于 B-树，在初始构建索引时，以及在右侧扩展索引（加入新的最大键值）时，叶子页都会填充到这一百分比。如果页面随后变成全满，就会发生分裂，从而导致磁盘上的索引结构碎片化。B-树默认使用 90 的填充因子，但也可以选择 10 到\n      100 之间的任意整数值。\n     \u003c/p\u003e\u003cp\u003e\n      对预计会有大量插入和/或更新的表，在 \u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e\n      时为 B-树索引设置较低的填充因子会有好处（即在向表批量装载数据之后）。50 到 90 范围内的值可以有效地\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e平滑\u003c/span\u003e”\u003c/span\u003eB-树索引生命周期早期页面分裂的\u003cspan class=\"emphasis\"\u003e\u003cem\u003e速率\u003c/em\u003e\u003c/span\u003e（这样降低填充因子甚至可能减少页面分裂的绝对数量，不过这一效果高度依赖工作负载）。\u003ca class=\"xref\" href=\"/zh/docs/18/btree.html#BTREE-DELETION\" title=\"65.1.4.2. 自底向上索引删除\"\u003e第 65.1.4.2 节\u003c/a\u003e中描述的 B-树自底向上索引删除技术依赖于页面上有一些\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e额外\u003c/span\u003e”\u003c/span\u003e空间来存储\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e额外\u003c/span\u003e”\u003c/span\u003e的元组版本，因此也会受到填充因子影响（不过通常影响不大）。\n     \u003c/p\u003e\u003cp\u003e\n      在某些其他特定情况下，在 \u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e 时将填充因子提高到 100 可能有助于最大化空间利用率。只有在完全确定该表是静态的（即永远不会受到插入或更新影响）时，才应考虑这样做。否则，将填充因子设为 100 有\u003cspan class=\"emphasis\"\u003e\u003cem\u003e损害\u003c/em\u003e\u003c/span\u003e性能的风险：即便只有少量更新或插入，也会导致页面突然大量分裂。\n     \u003c/p\u003e\u003cp\u003e\n      其他索引方法以不同但大致类似的方式使用填充因子；默认填充因子因方法而异。\n     \u003c/p\u003e","facts":[{"label":"Applies to","value":"Hash indexes"},{"label":"Value type","value":"integer"}],"related":[{"label":"CREATE INDEX","url":"/wiki/sql/create-index/?v=18"}],"release":{"channel":"stable","label":"18.6","major":"18","ref":"Local English manual 18.6","revision":"eb6c2292fdf28e1b4635ab478eec3919411cdf4a6c07553387870e26201f3744"},"sections":[{"paragraphs":["This entry applies to Hash 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."],"title":"Index access method"}],"signature":"WITH (fillfactor = value)","sources":[{"label":"PostgreSQL 18 English manual","path":"sql-createindex.html","sha256":"6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580","url":"/docs/18/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR"}],"translation_provenance":{"inputs":[{"kind":"current-pgdoc-translation","path":"/Users/vonng/pgsty/pgdoc/zh/18/html/sql-createindex.html","sha256":"ce2d5ba3eb6a7da215b754fefccac3a37794c6ec20de7eac0bf4f97034328d82"}],"kind":"same-version-pgdoc","locale":"zh-Hans","source_revision":"b1b500221dbe5437cfca004d6b04d3f6e422765d","version":"18"},"zh_from":"same-version-pgdoc"},"ManualEvidence":{"release":{"channel":"stable","label":"18.6","major":"18","ref":"Local English manual 18.6","revision":"eb6c2292fdf28e1b4635ab478eec3919411cdf4a6c07553387870e26201f3744"},"sources":[{"label":"PostgreSQL 18 English manual","path":"sql-createindex.html","sha256":"6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580","url":"/docs/18/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR"}]},"MeasuredEvidence":{}},"Text":{"Collection":"relopts","Key":"hash-fillfactor","SourceDatabase":"center","Version":"18","Locale":"en","Title":"fillfactor","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.\nB-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).\nIn 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.\nThe other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods.","BodyHTML":"\u003cp\u003eControls 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.\nB-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).\nIn 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.\nThe other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods.\u003c/p\u003e","SourceRevision":"eb6c2292fdf28e1b4635ab478eec3919411cdf4a6c07553387870e26201f3744","ContentHash":"5f0400c9f21874cbebc2ee7c8c6beb4eae6c5f069acb1fb57c8ef353b97542c4","Payload":{"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."],"related":[{"label":"CREATE INDEX","url":"/wiki/sql/create-index/?v=18"}],"sections":[{"paragraphs":["This entry applies to Hash 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."],"title":"Index access method"}]}},"RequestedLocale":"en","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}