From: | Alan Hodgson <ahodgson(at)simkin(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: alter table performance |
Date: | 2009-12-17 22:27:03 |
Message-ID: | 200912171427.03245@hal.medialogik.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thursday 17 December 2009, Antonio Goméz Soto
<antonio(dot)gomez(dot)soto(at)gmail(dot)com> wrote:
> 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
You could speed it up:
- drop all indexes on the table
- alter table person add column address varchar(64) not null default ''
- recreate the indexes
It will require exclusive access to the table for the duration, but it'll be
a lot faster and result in a lot less bloat than what you're doing now. It
still has to rewrite the whole table, but it's a lot faster than UPDATE.
(I have no idea if this works on 7.3).
--
"No animals were harmed in the recording of this episode. We tried but that
damn monkey was just too fast."
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2009-12-17 22:30:28 | Re: alter table performance |
Previous Message | Antonio Goméz Soto | 2009-12-17 21:26:53 | alter table performance |