Re: Slow updates, poor IO

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 Sun, Sep 28, 2008 at 7:33 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> John Huttley <John(at)mib-infotech(dot)co(dot)nz> 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??
>
>