Re: Updating a very large table

Rafael Domiciano <rafael.domiciano@gmail.com> wrote:

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

So far, so good.

> 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;

You're probably going to want to do that in small chunks (I would try
to avoid updating more than about 10,000 rows per transaction.)

> Postgres Version: 8.3.6

You should update to the latest 8.3 bug-fix version, if you can.  No
conversion needed; just stop on the old software and start on the new.

> Os.: Fedora Core 9
> 4 Gb Ram

I assume that you've tuned PostgreSQL, but if my other suggestions
don't help, please post again with the non-commented lines of the
postgresql.conf file, and actual table specifications and query text,
along with an EXPLAIN of the query.

> Kevin Grittner <Kevin.Grittner@wicourts.gov wrote:

>> 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.

That could be better.  You might want to schedule an overnight CLUSTER
of the table (followed by an ANALYZE) before attempting the update.

You don't show any index or toast-table information.  Was there none?

>> 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.

That looks OK, although you're right at the edge.

-Kevin