Re: adding a column takes FOREVER!

Eric Smith <eric_h_smith(at)mac(dot)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 way to 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