Thread: Long running update
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.
"Andrew Janian" <ajanian@scottrade.com> writes:
> I needed to expand the size of one of the varchar columns in a table of
> my 135GB database.
How big is the particular table you are doing this on? What foreign key
relationships is it involved in? (I wonder if the time is going into FK
checking more than the actual update...) What PG version exactly?
Personally, at this point I'd cancel the update while there's still time
to do a VACUUM before you have to be operational again. You could do
the required operation (increasing a varchar's limit) in O(1) time by
twiddling the system catalogs. You're going to need a VACUUM anyway
because of all the dead tuple versions left behind by the UPDATE.
regards, tom lane
The actual table that I am updating is 134GB of the 135GB. Each row has
one foreign key to another table, but no tables have a foreign key to
this table.
If I cancel the update then I can drop the new column, rename the old
column, do a vacuum, and then I should be left with what I started with,
right?
How can I expand that column without using this query?
I am using PostgreSQL 7.4.5.
Thanks for the help,
Andrew
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Sunday, October 16, 2005 12:07 PM
To: Andrew Janian
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Long running update
"Andrew Janian" <ajanian@scottrade.com> writes:
> I needed to expand the size of one of the varchar columns in a table
of
> my 135GB database.
How big is the particular table you are doing this on? What foreign key
relationships is it involved in? (I wonder if the time is going into FK
checking more than the actual update...) What PG version exactly?
Personally, at this point I'd cancel the update while there's still time
to do a VACUUM before you have to be operational again. You could do
the required operation (increasing a varchar's limit) in O(1) time by
twiddling the system catalogs. You're going to need a VACUUM anyway
because of all the dead tuple versions left behind by the UPDATE.
regards, tom lane
"Andrew Janian" <ajanian@scottrade.com> writes:
> If I cancel the update then I can drop the new column, rename the old
> column, do a vacuum, and then I should be left with what I started with,
> right?
Right.
> How can I expand that column without using this query?
Basically you want to alter the pg_attribute.atttypmod field for the
column. Here's an example:
regression=# create table mytable (mycolumn varchar(10));
CREATE TABLE
regression=# UPDATE pg_attribute SET atttypmod = 25 + 4
regression-# WHERE attrelid = 'mytable'::regclass
regression-# AND attname = 'mycolumn';
UPDATE 1
regression=# \d mytable
Table "public.mytable"
Column | Type | Modifiers
----------+-----------------------+-----------
mycolumn | character varying(25) |
Note the +4 ... this is a hangover from days gone by, but varchar
typmods are still defined as 4 more than what the user said.
*Practice* on a scratch database to make sure you have it right.
Also I'd suggest doing it inside a BEGIN block so you can roll it
back if you mess up. Use \d to verify that the table looks as you
expect before committing.
BTW, this trick doesn't really work nicely for anything except the
case of increasing the field width of a varchar column, so that's
why there's not a cleaner interface for it ...
regards, tom lane