4

Does Postgres automatically set the fillfactor to 100 instead of 90 when an index is made on a primary key column? If not, is there any reason not to manually set it to 100%?

https://www.postgresql.org/docs/current/static/sql-createindex.html

B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected.

Improve this question

1 Answer 1

Reset to default
3

Why would it matter that the fillfactor is on a primary key? The index works the same way. If you UPDATE an indexed row to a point to a new version of the row -- that is an UPDATE that wasn't heap-only, or if you add a row you have to potentially split the page the index is stored on to make room for the addition.

The point at which you have to do that is when the index's page is full. The question is after the pages are properly sized for the index, how much extra-space on those pages should be left for future operations? If the fillfactor was 100, every operation that wrote to the page would result in the page split. That would be super-slow.

So what fillfactor 90 does is leave a 10% gap. That's 10% more you can grow before you have to pay that price again. Only if you know you won't have to grow the index again would you want to set it to fillfactor 100 (and then REINDEX and shrink down all the pages).

Improve this answer
3
  • 1
    I had a logical gap in my reasoning, I think I get it now - update a row, the index has to point to a new location on disk, even if the primary key is the same. So even if the value for this field is the same after updating a row, the index would still need to be updated to reflect the new location.
    – davidtgq
    Commented Oct 25, 2017 at 18:27
  • I think that was the wrong interpretation, let me try again: unlike a heap where rows are just added to the end, indexes have to be in a correct order, and that usually means adding entries in the middle of an existing page (ex. adding a new name in the middle of an alphabetical phonebook). This means that unless an index is on sequential data, it pretty much should never use fillfactor=100%. It doesn't have anything specifically to do with updates, because both creating and updating affects this equally. Is it correct this time?
    – davidtgq
    Commented Oct 26, 2017 at 21:06
  • 1
    Dead on, except it isn't just about being sequential data. That has nothing to do with it. It's about the data growing inside which as you said includes both insertions AND updates. Unless the data is static the pages which include the index have to be split to grow. That process is expensive so we try to compromise between frequency and wasted space. That compromise is 90% (typically). Even if the fillfactor is 100% and the data is sequential, you have to split the page at the end when the page is full.
    – Evan Carroll
    Commented Oct 26, 2017 at 21:12

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.