Quantcast

alter table performance

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

alter table performance

Antonio Goméz Soto
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



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: alter table performance

Alan Hodgson
On Thursday 17 December 2009, Antonio Goméz Soto
<[hidden email]> 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."

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: alter table performance

Thomas Kellerer
In reply to this post by Antonio Goméz Soto
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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: alter table performance

Tom Lane-2
Thomas Kellerer <[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: alter table performance

Antonio Goméz Soto
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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: alter table performance

Antonio Goméz Soto
In reply to this post by Tom Lane-2
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<[hidden email]>  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
>    


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: alter table performance

Jaime Casanova-2
On Sat, Dec 19, 2009 at 4:07 PM, Antonio Goméz Soto
<[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: alter table performance

Antonio Goméz Soto
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.



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Loading...