19

In Postgres, one can set fillfactor for an index as well as a table. What is the difference? How to decide values for either. What are the usecases?

I am trying to cluster a spatial relation on a spatial index. It has a couple of million records. The records are updated all the time though very few new ones are created daily.

The use case is spatial range queries. What will be a good value for the table fillfactor and / or the index fillfactor?

Improve this question

1 Answer 1

Reset to default
17

From the CREATE TABLE manual page (emphasis added):

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.

From the CREATE INDEX manual page (emphasis added):

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 (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 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.

Improve this answer
6
  • 1
    It's customary to mention the source of a quote. Even if it's easy to figure out.
    – Erwin Brandstetter
    Commented Mar 20, 2012 at 2:05
  • yeah, sure ) forget sometimes...
    – pukipuki
    Commented Mar 20, 2012 at 2:16
  • Sorry to nag some more. Please link to the current version of the manual (unless you need to address a particular one). I edited. More about that here.
    – Erwin Brandstetter
    Commented Mar 20, 2012 at 10:15
  • 2
    Re: "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." Trying to understand context of "heavily updated tables" - does that mean table with row values that are heavily updated, or perhaps could that also include tables with rows that are frequently inserted and/or deleted? I wanted to know if my table, with few changes to row values but extensive inserts and deletes, might benefit from lower fillfactor on it's principal (key) index.
    – Reinsbrain
    Commented Aug 26, 2016 at 14:29
  • 1
    @Reinsbrain I think updates and deletes are operational pretty similar under mvcc, so I think they would benefit (updates could be placed on disk beside the previous version). I'm not sure about inserts; I believe postgres basically appends inserted rows but it's probably more complicated than that
    – jberryman
    Commented May 17, 2018 at 2:36

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.