alter table performance

From: Antonio Goméz Soto <antonio(dot)gomez(dot)soto(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: alter table performance
Date: 2009-12-17 21:26:53
Message-ID: 4B2AA21D.2030903@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I am regularly altering tables, adding columns setting default values etc.
This very often takes a very long time and is very disk intensive, and this
gets pretty annoying.

Things are hampered by the fact that some of our servers run PG 7.3

Suppose I have a table and I want to add a non NULL column with a default value.
What I normally do is:

alter table person add column address varchar(64);
update person set address = '' where address IS NULL;
alter table person alter column address set not NULL;
alter table person alter column address set default '';

When the table contains millions of records this takes forever.

Am I doing something wrong? Do other people have the same problems?

Thanks,
Antonio

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alan Hodgson 2009-12-17 22:27:03 Re: alter table performance
Previous Message wumpus 2009-12-17 20:51:07 feature request: create table with unique constraint