2011/5/17 Scott Marlowe <scott.marlowe@xxxxxxxxx>: > On Tue, May 17, 2011 at 6:59 AM, Anibal David Acosta <aa@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
