Re: alter table performance

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."