Re: tune postgres for UPDATE

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Re: tune postgres for UPDATE
Дата
Msg-id D960CB61B694CF459DCFB4B0128514C202D6767A@exadv11.host.magwien.gv.at
обсуждение исходный текст
Ответ на Re: tune postgres for UPDATE  ("Scott Marlowe" <scott.marlowe@gmail.com>)
Ответы Re: tune postgres for UPDATE  ("Scott Marlowe" <scott.marlowe@gmail.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Grzegorz Jaśkiewicz"
Дата:
Сообщение: Re: Problem Related to storing the field value in a String
Следующее
От: "Andrus"
Дата:
Сообщение: et_EE locale not found in debian