index fillfactor (was Re: For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER)

In response to "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com>:

> Thanks for a very informative post! One question:
>
> > I'm not sure how to find the current value, but a smaller fill factor
> > on busy tables should lead to less fragmentation, thus more efficient
> > indexes over time. Keep in mind that a smaller fill factor will also
> > lead to larger indexes initially.
>
> What constitutes a "small fill factor"? Would 70 be good?

Unfortunately, I can't say. I have not yet had the opportunity to
experiment with different fillfactors, so I can only speak in vague
estimations on this topic.

> I guess my
> current must have been the default, which the manual says is 100.

I expect it's at the default, but the docs say that is 90%:
http://www.postgresql.org/docs/8.2/static/sql-createindex.html

Where did you see 100?

> Or
> did you mean really small fill factor like 20? In this context, what
> is "packing" in the manual -- is that some kind of compression?

Hopefully, someone more knowledgeable will chime in with some wise
suggestions. Barring that, I can only suggest you experiment to find
what works for your workload, but don't rule out the possibility that
extremely low fillfactor values might work well for you.

--
Bill Moran
http://www.potentialtech.com