Re: Updating a very large table

Hello Kevin, Thnks for response,

Doing the alter table to add the new column was fast: ALTER TABLE table1 ADD
COLUMN new_column date;

The problem is that I have to do a update in this column, and the values are
going to be the a misc of others 2 columns of the table1, something like
this:

update table1
set new_column = (date)
where
new_column is null;

Postgres Version: 8.3.6
Os.: Fedora Core 9
4 Gb Ram

On Thu, Apr 23, 2009 at 2:29 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Rafael Domiciano <rafael(dot)domiciano(at)gmail(dot)com> wrote:
>
> > I have a large table to do update (in every tuple), the table
> > (table1) has about 8 millions tuples, and I had to add a new column
> > (new_column). The table1 is one of the most accessed table in my
> > cluster, so I can't do a direct update 'cause almost everyone stays
> > in waiting. Doing the update at night was unsuccesful, in the
> > morning it was running yet, and we had to kill it.
>
> Doing that with an ALTER TABLE against only 8 million rows should
> probably not have taken all night (depending, of course, on a lot of
> details you haven't provided). Killing it after running for all those
> hours will have bloated your tables and indexes. (Perhaps they were
> already bloated, in which case this will have made it worse.)
>
> What version of PostgreSQL is this, on what OS?
>
> What does a VACUUM ANALYZE VERBOSE on this table show as output?

INFO: "table1": encontrados 1572 versões de registros removíveis e 8022357
não-removíveis em 244388 páginas
DETAIL: 7101 versões de registros não vigentes não podem ser removidas
ainda.
Havia 1657653 ponteiros de itens não utilizados.
62515 páginas contém espaço livre útil.
0 páginas estão completamente vazias.
CPU 9.38s/26.74u sec elapsed 27540.53 sec.

>
>
> What are the last few lines of VACUUM ANALYZE VERBOSE on the whole
> database?

INFO: mapeamento de espaço livre contém 152886 páginas em 907 relações
DETAIL: O total de 151280 páginas estão em uso (incluindo excesso).
151280 páginas são necessárias para encontrar todo espaço livre.
Limites atuais são: 153600 páginas, 2500 relações, utilizando 1065 kB.

>
>
> If you can show us the table description and how you tried to modify
> it, that would help.
>
> -Kevin
>