Skip site navigation (1) Skip section navigation (2)

Re: Altering a column (increasing size) in Postgres takes long time

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Husam Tomeh" <HTomeh(at)facorelogic(dot)com>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>, "Jaime Casanova" <jcasanov(at)systemguards(dot)com(dot)ec>
Subject: Re: Altering a column (increasing size) in Postgres takes long time
Date: 2010-02-26 15:02:32
Message-ID: 4B878E28020000250002F6AC@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-admin
Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> Tomeh, Husam <HTomeh(at)facorelogic(dot)com> wrote:
>> We have a huge table with hundred million records. We need to
>> increase the size of an existing column with varchar type, and it
>> has been running for more than 12 hours.
 
>> 3) Some folks referred to directly updating Postgres internal
>> tables (pg_attribute) which takes seconds to make the column
>> change happen. How safe if this and would potentially cause any
>> corruption?
>>
> 
> no, that's insane
 
Not really; but it is something to approach with great caution. 
With this approach you don't need to drop or rebuild the index, and
it's all done, as you say, in a matter of seconds.
 
Thoughts:
 
*  Don't over-generalize the technique.  Going from a varchar(n) to
a varchar(larger-n) is safe.  Most changes aren't.
 
*  Test, test, test.  Copy your schema to a test database.  Look at
the pg_attribute row.  Use ALTER TABLE to make the change.  Then
look at it again.  Restore the schema to the starting point and try
it with a direct update as a database superuser.  Write a query to
SELECT the row which will be updated using table name and column
name (since oid might not match between your copy and the real
database), then modify the SELECT to get to your UPDATE.  Confirm
that it made exactly the right change to the right row.  If you can
arrange a copy of the complete database, or some reasonable test
facsimile, test there; then make sure your application works as
expected.
 
*  Have a good backup.  Confirm that it can actually be restored;
otherwise you'll be doing this trapeze act without a net.
 
We've done this successfully with large production databases, but
we've been very careful.  If you're not, you could corrupt your
database.
 
Insane, no.  If it doesn't make you nervous enough to take great
care -- well, *that* would be insane.
 
-Kevin

In response to

pgsql-admin by date

Next:From: Roland WellsDate: 2010-02-26 20:12:45
Subject: db recovery after hd crash (could not open relation 1663/16385/16400: No such file or directory)
Previous:From: Jaime CasanovaDate: 2010-02-25 23:40:55
Subject: Re: Altering a column (increasing size) in Postgres takes long time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group