0
  1. The postgres documentation says index fillfactor affect on "index pages" and says "for heavily updated tables a smaller fillfactor is better". I'm confused that its about update an index value or any value in the row?
  2. Is table fillfactor override the index fillfactors of the table (whether index fillfactor defined or not (default 90 for btree index))?

Fillfactor in PostgreSQL documentation:

Table fillfactor:

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.

Index fillfactor:

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.

0

1 Answer 1

3

Table and index fillfactor are independent from each other.

Both tables and indexes are stored in files on the operating system. These files are organized in 8KB blocks. fillfactor determines to what extent these blocks are filled by an INSERT (in the case of a table) or the initial creation or extension on the right end (for an index).

Even though the fillfactors on table and index are independent, the indication for setting lower values is similar for tables and indexes: if the table receives many updates. Bear in mind that many table modifications also cause an index modification.

Note that a lower fillfactor on the table will only help if none of the updated columns are indexed. Otherwise, it is only a waste of space.

I would set a lower fillfactor on tables if the above condition is satisfied and the table receives many updates. I would not set a lower fillfactor on indexes unless I see that an index regularly gets fragmented by many updates.

4
  • "a lower fillfactor on the table will only help if none of the updated columns are indexed. Otherwise, it is only a waste of space." I think it is not true and it's about HOT updates, not normal MVCC operation: "With a HOT dead tuple space can be automatically reclaimed at the time of INSERT or UPDATE if no changes are made to indexed columns." Reference
    – Pourqavam
    Commented Aug 28, 2020 at 6:20
  • Right; a low fillfactor is waste of space unless you can do HOT updates. Commented Aug 28, 2020 at 6:27
  • "if the table receives many updates" - are you including INSERTs when you say this? Commented Sep 26, 2023 at 16:20
  • @NathanLong No, INSERTs are a different affair. Commented Sep 27, 2023 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.