Re: adding a column takes FOREVER!

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 so that 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(at)mac(dot)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 the string I use to create the table, so you can see the contents. I don't think I have foreign key references or triggers of 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