Re: Updating a very large table

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Rafael Domiciano" <rafael(dot)domiciano(at)gmail(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Updating a very large table
Date: 2009-04-23 17:29:32
Message-ID: 49F05F2C.EE98.0025.0@wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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?

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

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

-Kevin

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Rafael Domiciano 2009-04-23 18:32:19 Re: Updating a very large table
Previous Message Rafael Domiciano 2009-04-23 16:34:34 Updating a very large table