I have had great success using FILLFACTOR on certain tables where big updates like this occur and improving performance. It is still not as fast as I would like, but there are significant gains. A big disk array won't help you as much as it should -- yes it will be faster, but it will still be chugging during one of these sorts of large updates and very inefficiently at that.
On some of my cases, a FILLFACTOR of 95 or 98 is enough to do the trick. On others, 80 or 70 works.
It depends on the size of your rows versus the size of the modifications you make. A fillfactor of 99 holds between ~80 bytes and one row-width worth of free space in every page, and is all that is needed if you have larger rows and only modify small fields such as ints. I'm not sure why FILLFACTOR = 99 isn't the default, to be honest. The size difference on disk is far less than 1% since most tables can't fit an exact number of rows in one page, and the benefit for updates is huge in certain cases.
On the other hand, your table has a narrow row width and will fit many rows on one page, and if you are modifying text or varchars, you may need more space for those reserved in the fillfactor void and a smaller FILLFACTOR setting on the table, down to about 50 for updates where the updated rows account for a big fraction of the row width.
A second benefit of using a fillfactor is that you can CLUSTER on an index and the table will retain that ordering for longer while inserts/updates/deletes occur. A fillfactor setting, REINDEX, then CLUSTER sequence can have a big impact.
On some of my cases, a FILLFACTOR of 95 or 98 is enough to do the trick. On others, 80 or 70 works.
It depends on the size of your rows versus the size of the modifications you make. A fillfactor of 99 holds between ~80 bytes and one row-width worth of free space in every page, and is all that is needed if you have larger rows and only modify small fields such as ints. I'm not sure why FILLFACTOR = 99 isn't the default, to be honest. The size difference on disk is far less than 1% since most tables can't fit an exact number of rows in one page, and the benefit for updates is huge in certain cases.
On the other hand, your table has a narrow row width and will fit many rows on one page, and if you are modifying text or varchars, you may need more space for those reserved in the fillfactor void and a smaller FILLFACTOR setting on the table, down to about 50 for updates where the updated rows account for a big fraction of the row width.
A second benefit of using a fillfactor is that you can CLUSTER on an index and the table will retain that ordering for longer while inserts/updates/deletes occur. A fillfactor setting, REINDEX, then CLUSTER sequence can have a big impact.
On Sun, Sep 28, 2008 at 7:33 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
John Huttley <John@xxxxxxxxxxxxxxxxxx> writes:
> Scott Marlowe wrote:
>> was... was a part of the trade-offs.
> You are thinking of HOT?
> I don't think it applies in the case of full table updates??
