Long running update

From: "Andrew Janian" <ajanian(at)scottrade(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Cc: "Andrew Janian" <ajanian(at)scottrade(dot)com>
Subject: Long running update
Date: 2005-10-16 16:28:52
Message-ID: 80AD4CD06F9D904EAB15D6A1792268D50F82B4@EXCHSTL2.scottrade.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I needed to expand the size of one of the varchar columns in a table of
my 135GB database. To do this, I used the following command:

ALTER TABLE mb_fix_message RENAME COLUMN mb_symbol TO mb_symbol_old;

ALTER TABLE mb_fix_message ADD COLUMN mb_symbol VARCHAR(25);

UPDATE mb_fix_message SET mb_symbol = mb_symbol_old;

ALTER TABLE mb_fix_message DROP COLUMN mb_symbol;

During the update I ran an analyze and got the following output:

INFO: analyzing "public.mb_fix_message"

INFO: "mb_fix_message": 12502398 pages, 3000 rows sampled, 176684832
estimated total rows

The update has been running for 26 hours now. My scheduled nightly
vacuum ran and took about 12 hours and finally finished this morning.
The symbol fields (old and new) are not indexed. Is there anything I
can do to see how much has been completed / how long this should take?

Will this affect insert performance in my table tomorrow when users
begin to insert using transactions?

Sorry for all the questions, any help would be greatly appreciated.

Thanks,

Andrew Janian

Scottrade, Inc.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-10-16 16:40:25 Re: PostgreSQL Gotchas
Previous Message Martijn van Oosterhout 2005-10-16 14:36:29 Re: PostgreSQL Gotchas