Do indexes on primary keys default to fillfactor of 90%, and should they be set to 100%? 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.
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).