Re: Fill Factor

On Tue, May 17, 2011 at 6:59 AM, Anibal David Acosta <aa(at)devshock(dot)com> wrote:
> Hello,
>
> How fillfactor impact performance of query?

Fillfactor tells the db how much empty space to leave in the database
when creating a table and inserting rows. If you set it to 90% then
10% of the space in the table will be available for updates can be
used for the new data. Combined with pg 8.3+ HOT updates, this free
space allows updates to non-indexed fields to be close to "free"
because now the index for that row needs no updates if the new datum
for that row first in the same 8k pg block.

> I have two cases,
> One is a operational table, for each insert it have an update, this table
> must have aprox. 1.000 insert per second and 1.000 update per second (same
> inserted row)

If you could combine the insert and update into one action that would
be preferable really.

> Is necessary to change the fill factor?

Not necessary but possibly better for performance.

> The other case is a table that have few insert (statistics) but thousands or
> millons of update, In this case the fillfactor is not necessary to change?

Actually updates are the time that a lower fill factor is most useful.
But it doesn't need to be really low. anything below 95% is likely
more than you need. But it really depends on your access patterns. If
you're updating 20% of a table at a time, then a fillfactor of ~80%
might be the best fit. Whether or not the updates fit under the HOT
umbrella, lowering fill factor enough to allow the updates to happen
in place without adding pages to the table files is usually a win.