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

Phoenix Kiula wrote:
> Then I am confused again about how the fill factor stuff works. Let's
> say I have a table with four BTREE indexes. Should all of them have a
> fill factor of about 60 (lower than the default 90, that is) to be
> effective? Or will it help if I lower the fill factor on only a couple
> of the most often used ones? The primary key index is very, very
> rarely updated so I don't need it to have a fill factor.
>
> I could try and see these one by one, but that's no better than
> touching/feeling the database blind-folded. I would love to see some
> writeup about this whole thing, but it seems hard to come by!

Let me present my understanding; I hope I won't confuse issues further.

Indexes and tables are bath organized in pages, each page contains
several
entries or rows.

When an INSERT or UPDATE on the table occurs, a new row (version) is
created (and the old version of the row will be freed upon VACUUM).

Any index entry that points to this row will have to be changed
because the location of the row has changed.
That means that there will also have to be a new entry in the index,
even if the key has not changed (simply modifying the existing
index entry to point to the new row location won't do, because there
may be transactions that still need the old version of the row).

Reducing fillfactor on tables (default 100) will reduce the number
of table pages that need to be touched during an UPDATE.

Reducing fillfactor on a B-tree index (default 90) will reduce the
frequency of page splits that can happen upon INSERT or UPDATE.

Both at the cost of wasting some disk (and memory) space.

So I *guess* that when you decide that a table will be heavily updated
and you want to reduce disk I/O at the cost of wasting some space,
it will be a good idea to reduce fillfactor on the table and all its
indexes.

I emphasize the "guess" because
a) I may have made a mistake in my deductions :^) and
b) I cannot tell you good numbers to choose.

As in most performance tuning questions, the best thing you can probably
is to test and compare various settings and see which performs best
for you....

Yours,
Laurenz Albe