Re: For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

On Sep 19, 2007, at 10:30 AM, Richard Broersma Jr wrote:

>> Not quite.  Once a page has reached it's fill factor percentage full,
>> no more inserts will happen on that page, only updates.  Also, I
>> think you have large/small backwards wrt fill factor.  If  you have a
>> fill factor of, say, 40% then once a page has reached 40% full no
>> more inserts will happen (unless some space is reclaimed by vacuum).
>> So, smaller fill factors == bigger holes.  The bigger the fill
>> factor, the smaller the whole:  if you have a fill factor of 90%,
>> only 10% is reserved for updates of rows on that page.
>
> So (just to reiterate), fill factor can be applied to both a table
> and/or an index(es).  But the
> "holes" built into the page of a table or index can only be filled
> by UPDATE Statements.
>
> Thanks for the clarification!

Yep.  Although, to be strictly honest, I guess the term UPDATE isn't
the best term to use for indexes.  My description works best for
tables, see the section on FILLFACTOR in http://www.postgresql.org/
docs/8.2/interactive/sql-createindex.html for a better description of
what happens for indexes -- slightly different semantics, but the
same general effect.

Also, note that once we have HOT, figuring out fill factor for
indexes will be a whole different ball game.  Currently, an update to
any tuple in a table, results in a new index entry.  With hot, index
entries will only happen if the indexed column is changed in the update.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com