Thread: adding a column takes FOREVER!

All,

I'm adding a column in postgres 8.3 with the syntax:  alter table images add column "saveState" varchar(1) default '0';
It takes a good solid 20 minutes to add this column to a table with ~ 14,000 entries.  Why so long?  Is there a way to
speedthat up?  The table has ~ 50 columns. 

Thanks,
Eric

On 10/22/2011 06:45 AM, Eric Smith wrote:
> All,
>
> I'm adding a column in postgres 8.3 with the syntax:  alter table images add column "saveState" varchar(1) default
'0'; It takes a good solid 20 minutes to add this column to a table with ~ 14,000 entries.  Why so long?  Is there a
wayto speed that up?  The table has ~ 50 columns. 

PostgreSQL has to re-write the table to add the column with its new value.

I guess in theory PostgreSQL could keep track of the default for the new
column and write it in lazily when a row is touched for some other
reason. That'd quickly get to be a nightmare if the user ALTERed the
column again to change the default (you'd have to write the _old_
default to all the columns before making the change) and in many other
circumstances, though.

You can ALTER your table to add the column without the default, ALTER it
again to add the default, then manually UPDATE the values to the new
default in the background if you want. Doing it that way will cause the
new column to be initially added as NULL, which doesn't require a full
table re-write at ALTER time.

--
Craig Ringer

Eric Smith <eric_h_smith@mac.com> writes:
> I'm adding a column in postgres 8.3 with the syntax:  alter table images add column "saveState" varchar(1) default
'0'; It takes a good solid 20 minutes to add this column to a table with ~ 14,000 entries.  Why so long?  Is there a
wayto speed that up?  The table has ~ 50 columns. 

As Craig explained, that does require updating every row ... but for
only 14000 rows, it doesn't seem like it should take that long.
A quick test with 8.3 on my oldest and slowest machine:

regression=# create table foo as select generate_series(1,14000) as x;
SELECT
Time: 579.518 ms
regression=# alter table foo add column "saveState" varchar(1) default '0';
ALTER TABLE
Time: 482.143 ms

I'm thinking there is something you haven't told us about that creates a
great deal of overhead for updates on this table.  Lots and lots o'
indexes?  Lots and lots o' foreign key references?  Inefficient
triggers?

Or maybe it's just blocking behind somebody else's lock?

            regards, tom lane

Tom Lane wrote on 22.10.2011 05:24:
> I'm thinking there is something you haven't told us about that creates a
> great deal of overhead for updates on this table.  Lots and lots o'
> indexes?  Lots and lots o' foreign key references?

Why would an add column need to check foreign key references?

Eric Smith <eric_h_smith@mac.com> writes:
> Thank you for the response... to be perfectly honest, I don't know enough to know what I'm not telling you.  Below is
thestring I use to create the table, so you can see the contents.  I don't think I have foreign key references or
triggersof any kind.  Any ideas? (this is 8.3 running on Mac OS 10.7) 

I'm wondering how much data is hiding behind this column:

>     [tableString appendString:@"\"imageData\" bytea null,"];

Since you're installing a non-null column default value, the ALTER TABLE
ADD COLUMN command has to rewrite the entire table.  If there are large
images hiding in each of those "only 14000" rows, there'd be a lot of
data to copy over and so it could take awhile.

(In contrast, ADD COLUMN without a default value is speedy because
Postgres plays some tricks to avoid rewriting the table data.  That
won't help you if you have to install non-null values in the new
column, but it's good to know that there's a difference.)

            regards, tom lane

Tom,

Well... there's a lot of data hiding in each of those rows... as much as 4MB in each.  I'll make allowances in my code
sothat adding a column without a default is a workable solution.  

Thank you,
Eric

On Nov 1, 2011, at 8:27 PM, Tom Lane wrote:

> Eric Smith <eric_h_smith@mac.com> writes:
>> Thank you for the response... to be perfectly honest, I don't know enough to know what I'm not telling you.  Below
isthe string I use to create the table, so you can see the contents.  I don't think I have foreign key references or
triggersof any kind.  Any ideas? (this is 8.3 running on Mac OS 10.7) 
>
> I'm wondering how much data is hiding behind this column:
>
>>     [tableString appendString:@"\"imageData\" bytea null,"];
>
> Since you're installing a non-null column default value, the ALTER TABLE
> ADD COLUMN command has to rewrite the entire table.  If there are large
> images hiding in each of those "only 14000" rows, there'd be a lot of
> data to copy over and so it could take awhile.
>
> (In contrast, ADD COLUMN without a default value is speedy because
> Postgres plays some tricks to avoid rewriting the table data.  That
> won't help you if you have to install non-null values in the new
> column, but it's good to know that there's a difference.)
>
>             regards, tom lane