pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAltering a column (increasing size) in Postgres takes long time
3+ messages / 3 participants
[nested] [flat]
* Altering a column (increasing size) in Postgres takes long time
@ 2010-02-25 23:04 Tomeh, Husam <HTomeh@facorelogic.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tomeh, Husam @ 2010-02-25 23:04 UTC (permalink / raw)
To: pgsql-admin@postgresql.org <'pgsql-admin@postgresql.org'>
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.
We're using: ALTER TABLE Property ALTER COLUMN "situs-number" TYPE
varchar(30);
The index on that column has been dropped before issuing the above
statement.
1) Can you explain what's happening internally that make this a
very long process? Does the table get re-created?
2) Assuming the Alter statement finished successfully, And if I
didn't drop the index (on that column), do I have to rebuild the index?
Does the index get invalidated for just alter the indexed column?
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?
SET SESSION AUTHORIZATION 'postgres';
BEGIN;
update pg_attribute
set atttypmod = 21 + 4
where attrelid = 'property'::regclass
and attname = 'situs-number';
update pg_attribute
set atttypmod = 21 + 4
where attrelid = 'interim-refresh'::regclass
and attname = 'situs-number';
update pg_attribute
set atttypmod = 21 + 4
where attrelid = 'interim-drefresh'::regclass
and attname = 'situs-number';
update pg_attribute
set atttypmod = 21 + 4
where attrelid = 'interim-update-property'::regclass
and attname = 'situs-number';
update pg_attribute
set atttypmod = 21 + 4
where attrelid = 'ix-property-address'::regclass
and attname = 'situs-number';
RESET SESSION AUTHORIZATION;
4) Is there a more practical and safe method to alter a huge
table with reasonable amount of time?
Please advise. Your help is much appreciated.
We're running Postgres 8.3.7 on RedHat Enterprise AS 4.7 on HP585DL.
Regards,
Husam
******************************************************************************************
This message may contain confidential or proprietary information intended only for the use of the
addressee(s) named above or may contain information that is legally privileged. If you are
not the intended addressee, or the person responsible for delivering it to the intended addressee,
you are hereby notified that reading, disseminating, distributing or copying this message is strictly
prohibited. If you have received this message by mistake, please immediately notify us by
replying to the message and delete the original message and any copies immediately thereafter.
Thank you.
******************************************************************************************
FACLD
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Altering a column (increasing size) in Postgres takes long time
@ 2010-02-25 23:40 Jaime Casanova <jcasanov@systemguards.com.ec>
parent: Tomeh, Husam <HTomeh@facorelogic.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jaime Casanova @ 2010-02-25 23:40 UTC (permalink / raw)
To: Tomeh, Husam <HTomeh@facorelogic.com>; +Cc: pgsql-admin
On Thu, Feb 25, 2010 at 6:04 PM, 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.
>
>
>
> We’re using: ALTER TABLE Property ALTER COLUMN "situs-number" TYPE
> varchar(30);
>
it's not better to have the field beign type text and don't worry
about length but just in check constraints if really necesary?
>
> 1) Can you explain what’s happening internally that make this a very
> long process? Does the table get re-created?
>
yes, and all it's indexes rebuild not just the one you dropped
and the FK's rechecked (don't think so but can't remember right now)?
>
>
> 2) Assuming the Alter statement finished successfully, And if I didn’t
> drop the index (on that column), do I have to rebuild the index? Does the
> index get invalidated for just alter the indexed column?
>
it's get rebuilt
>
>
> 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
>
> 4) Is there a more practical and safe method to alter a huge table
> with reasonable amount of time?
>
>
use text fields instead of varchar(n)
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Altering a column (increasing size) in Postgres takes long time
@ 2010-02-26 15:02 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2010-02-26 15:02 UTC (permalink / raw)
To: Husam Tomeh <HTomeh@facorelogic.com>; +Cc: pgsql-admin; Jaime Casanova <jcasanov@systemguards.com.ec>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-02-26 15:02 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-25 23:04 Altering a column (increasing size) in Postgres takes long time Tomeh, Husam <HTomeh@facorelogic.com>
2010-02-25 23:40 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-02-26 15:02 ` Re: Altering a column (increasing size) in Postgres takes long time Kevin Grittner <Kevin.Grittner@wicourts.gov>
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