Re: Fill Factor

2011/5/17 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
> 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.

depend of deletes ratio too... without delete I am unsure a reduced
fillfactor will have a good impact on the long term.

>
>> 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.

And one possible way to help adjust the fillfactor is to control the
relation size.
Sometimes reducing fillfactor a lot (60-80%) is good, the table is
stuck at some XX MB and page are well reused.

>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support