Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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

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

add comment

1 Answer 1

up vote 1 down vote accepted

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.
http://www.postgresql.org/docs/current/interactive/sql-createindex.html

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.
http://www.postgresql.org/docs/current/interactive/sql-createtable.html

share|improve this answer
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
    
That helps. Thanks! –  S Shah Mar 20 '12 at 21:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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