Postgresql – Index fillfactor and index pages in PostgreSQL

indexpostgresqltable

  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.

Best Answer

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.

Related Question