Re: adding a column takes FOREVER!

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 way to 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