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.
Yours,
Laurenz Albe
