pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Husam Tomeh <HTomeh@facorelogic.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Cc: Jaime Casanova <jcasanov@systemguards.com.ec>
Subject: Re: Altering a column (increasing size) in Postgres takes  long time
Date: Fri, 26 Feb 2010 09:02:32 -0600
Message-ID: <4B878E28020000250002F6AC@gw.wicourts.gov> (raw)
In-Reply-To: <3073cc9b1002251540s8b91e7cw844b3bb6da2a0cd@mail.gmail.com>
References: <7965A9DCF12CC14984420BCC37B1608F25A9E2D038@Elzar.grant.co.uk>
	<F1B0F9305B343E43A1C3EECE48B853D50501DD40@CITGSNA01SXCH02.ana.firstamdata.com>
	<F1B0F9305B343E43A1C3EECE48B853D5056A1328@CITGSNA01SXCH02.ana.firstamdata.com>
	<3073cc9b1002251540s8b91e7cw844b3bb6da2a0cd@mail.gmail.com>

Jaime Casanova <jcasanov@systemguards.com.ec> wrote:
> Tomeh, Husam <HTomeh@facorelogic.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



view thread (3+ messages)

Message-ID: <4B878E28020000250002F6AC@gw.wicourts.gov>
Permalink:  ../4B878E28020000250002F6AC@gw.wicourts.gov/
Also on:    postgresql.org/message-id/4B878E28020000250002F6AC@gw.wicourts.gov

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: Kevin.Grittner@wicourts.gov, HTomeh@facorelogic.com, jcasanov@systemguards.com.ec
  Subject: Re: Altering a column (increasing size) in Postgres takes  long time
  In-Reply-To: <4B878E28020000250002F6AC@gw.wicourts.gov>
  References: <7965A9DCF12CC14984420BCC37B1608F25A9E2D038@Elzar.grant.co.uk> <F1B0F9305B343E43A1C3EECE48B853D50501DD40@CITGSNA01SXCH02.ana.firstamdata.com> <F1B0F9305B343E43A1C3EECE48B853D5056A1328@CITGSNA01SXCH02.ana.firstamdata.com> <3073cc9b1002251540s8b91e7cw844b3bb6da2a0cd@mail.gmail.com> <4B878E28020000250002F6AC@gw.wicourts.gov>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox