I want to know the table fillfactor and index fillfactor differences in depth and more details to better desicion for optimize database.
- Any difference in update non-indexed columns or update the index column for fillfactor?
- What is the meaning of “heavily updated tables” in their definition exactly? Does it include inserts and deletes?
- Is table fillfactor override the index fillfactors of the table (whether index fillfactor defined or not (default 90 for btree index))?
Maybe an example of a table with fillfactor 100 and an index fillfactor 50 and a table with fillfactor 50 and an index fillfactor 100 can help.
Their definition in PostgreSQL documentation:
The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables.
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.