Re: tune postgres for UPDATE

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Scott Marlowe *EXTERN*" <scott(dot)marlowe(at)gmail(dot)com>, Sebastian Böhm <seb(at)exse(dot)net>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: tune postgres for UPDATE
Date: 2008-12-09 10:17:13
Message-ID: D960CB61B694CF459DCFB4B0128514C202D6767A@exadv11.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2008-12-09 11:02:46 et_EE locale not found in debian
Previous Message Grzegorz Jaśkiewicz 2008-12-09 09:06:20 Re: Problem Related to storing the field value in a String