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:
>> 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
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:
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
In response to
pgsql-performance by date
|Next:||From: m1ott||Date: 2011-05-17 15:10:31|
|Subject: FW: KVP table vs. hstore - hstore performance (Was:
Postgres NoSQL emulation)|
|Previous:||From: Robert Klemme||Date: 2011-05-17 13:29:51|
|Subject: Re: [PERFORMANCE] expanding to SAN: which portion best to move|