15

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?

migrated from stackoverflow.com Mar 20 '12 at 17:42

This question came from our site for professional and enthusiast programmers.

12

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.

  • 1
    It's customary to mention the source of a quote. Even if it's easy to figure out. – Erwin Brandstetter Mar 20 '12 at 2:05
  • yeah, sure ) forget sometimes... – pukipuki Mar 20 '12 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 Mar 20 '12 at 10:15
  • The links are backwards. – Dave Johansen Jan 7 '16 at 17:23
  • 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 Aug 26 '16 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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