Re: tune postgres for UPDATE

On Tue, Dec 9, 2008 at 3:17 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> Scott Marlowe wrote:
>> Sebastian Böhm wrote:
>> > I have a table with a lot of columns (text and integer).
>> >
>> > It currently has 3Mio Rows.
>> >
>> > Updating a column in all rows (integer) takes endless (days).
>>
>> I'm afraid you may not understand how postgresql's MVCC implementation
>> works here. Updating a row creates a new copy of the row and leaves
>> the old copy in place. Running such an update several times in a row
>> can result in a table that is mostly dead space and very slow to
>> access, both for reads and writes.
>
> As far as I know, that problem can be alleviated to some extent by using
> PostgreSQL 8.3 and creating the table with a fillfactor substantially less
> than 100.
>
> Then free space is left in database blocks during insert which can be
> used for later updates. This reduces the number of blocks accessed per
> update and also the number of index updates if the changed column is not
> indexed.
>
> Moreover, row pruning can kick in if the row is updated more than once,
> reducing the amount of dead space.

I'm pretty sure you'd have to vacuum still in between runs or the
extra fill factor space would only get used the first time. I.e.:

create table fill factor 50%
load data into table
update whole table -- 50% free space gets used.
(should vacuum here but didn't)
update whole table -- boom, new tuples are added onto the end of the table.

What I don't know is if the new tuples added at the end of the table
will have a fill factor of 50%. I'd expect so, in which case it might
help a bit.