Thread: alter table performance

alter table performance

From

Antonio Goméz Soto

Date:

17 December 2009, 21:14:25

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

Re: alter table performance

From

Alan Hodgson

Date:

17 December 2009, 21:27:46

On Thursday 17 December 2009, Antonio Goméz Soto
<antonio.gomez.soto@gmail.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."

Re: alter table performance

From

Thomas Kellerer

Date:

17 December 2009, 21:31:39

Antonio Goméz Soto wrote on 17.12.2009 22:26:
> 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?

What's wrong with:

alter table person add column address varchar(64) not null default '';

Although I don't know if such a pre-historic version like 7.3 would support that.

It works for 8.4 and I believe this was working with 8.3 and 8.2 as well

Thomas

Re: alter table performance

From

Tom Lane

Date:

17 December 2009, 21:46:20

Thomas Kellerer <spam_eater@gmx.net> writes:
> What's wrong with:
> alter table person add column address varchar(64) not null default '';

This:

regression=# alter table person add column address varchar(64) not null default '';
ERROR:  Adding columns with defaults is not implemented.
        Add the column, then use ALTER TABLE SET DEFAULT.
regression=# \q

This is just one of many many things that could be improved by getting
off of 7.3.  In general, complaining about performance (or features)
of a seven-year-old, long since EOL'd release is not a productive use of
anybody's time.

            regards, tom lane

Re: alter table performance

From

Antonio Goméz Soto

Date:

17 December 2009, 22:49:19

Op 17-12-09 23:46, Tom Lane schreef:
> This is just one of many many things that could be improved by getting
> off of 7.3.  In general, complaining about performance (or features)
> of a seven-year-old, long since EOL'd release is not a productive use of
> anybody's time.
>

I'm sorry, didn't mean to.

I was just checking if I did it the right way, or if it was supposed to
be that slow.

I know we should upgrade the client machines, but in some cases we just
can't.

Anyway, thanks for giving me the answer.

Antonio

Re: alter table performance

From

Antonio Goméz Soto

Date:

19 December 2009, 20:07:35

Hi,

is there a way in sql to dynamically test for version 7.3, so I can run the

   alter table add column
   update table set column = .. where column IS NULL;
   alter table alter column set not null

on 7.3, and on newer versions:

   alter table add column ... not null default '';

Maybe I can create pg/SQL function, that does this, and remove it
afterwards.
or is there a better way?

Thanks
Antonio

Op 17-12-09 23:46, Tom Lane schreef:
> Thomas Kellerer<spam_eater@gmx.net>  writes:
>
>> What's wrong with:
>> alter table person add column address varchar(64) not null default '';
>>
> This:
>
> regression=# alter table person add column address varchar(64) not null default '';
> ERROR:  Adding columns with defaults is not implemented.
>          Add the column, then use ALTER TABLE SET DEFAULT.
> regression=# \q
>
> This is just one of many many things that could be improved by getting
> off of 7.3.  In general, complaining about performance (or features)
> of a seven-year-old, long since EOL'd release is not a productive use of
> anybody's time.
>
>             regards, tom lane
>

Re: alter table performance

From

Jaime Casanova

Date:

19 December 2009, 20:21:24

On Sat, Dec 19, 2009 at 4:07 PM, Antonio Goméz Soto
<antonio.gomez.soto@gmail.com> wrote:
> Hi,
>
> is there a way in sql to dynamically test for version 7.3, so I can run the
>

are you planning to run this many times? what is wrong with making
this manually?
doesn't seem like something to make automatic...

but if you insist in plpgsql you can execute "select version() into
some_text_var" and act acordingly

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Re: alter table performance

From

Antonio Goméz Soto

Date:

20 December 2009, 20:02:01

Op 19-12-09 22:20, Jaime Casanova schreef:
> are you planning to run this many times? what is wrong with making
> this manually?
> doesn't seem like something to make automatic...
>
> but if you insist in plpgsql you can execute "select version() into
> some_text_var" and act acordingly
>
>
No, this is done in an automatic software update procedure across
hundreds of machines
which run different postgreSQL versions.

Thanks, I'll give this a try.

Antonio.

Re: alter table performance

From

Jaime Casanova

Date:

21 December 2009, 17:23:49

On Sat, Dec 19, 2009 at 4:07 PM, Antonio Goméz Soto
<antonio.gomez.soto@gmail.com> wrote:
> Hi,
>
> is there a way in sql to dynamically test for version 7.3, so I can run the
>

are you planning to run this many times? what is wrong with making
this manually?
doesn't seem like something to make automatic...

but if you insist in plpgsql you can execute "select version() into
some_text_var" and act acordingly

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157