Thread: add column with default value is very slow

AI Rumman wrote:
> I execued the query:
> ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U';
>
> The db is stuck. The enity table has 2064740 records;
>
> Watching locks:
[all locks are granted]

> Any idea for the db stuck?

To add the column, PostgreSQL has to modify all rows in the table.

But then 2064740 records is not very much, so it shouldn't take forever.

Do you see processor or I/O activity?

Yours,
Laurenz Albe

On Tue, Sep 11, 2012 at 07:20:28PM +0600, AI Rumman wrote:
> I have a table as follows:
> I execued the query:
> ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U';
>
> The db is stuck. The enity table has 2064740 records;

such alter table has to rewrite whole table. So it will take a while

> Watching locks:

output of this was perfectly unreadable, because your email client
wrapped lines at some random places.

In future - please put such dumps on some paste site, or just attach it
to mail, and not copy/paste them to body of message.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

On Tue, Sep 11, 2012 at 07:55:24PM +0600, AI Rumman wrote:
> I added the excel file for locks data.

well, it worked, but why didn't you just make it text file, in notepad or
something like this?

> I was surprised to see that while I was updating a single column value for
> all records in a tables, all indexes are locked by the server.

alter table is not locked (At least looking at the pg_locks data you
showed).

this means - it just takes long time.

Please do:
select pg_total_relation_size('entity');
to see how much data it has to rewrite.

for future - just don't do alter table, with default, and not null.
doing it via add column; set default; batch-backfill data, set not null
will take longer but will be done with much shorter locks.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

On Tue, Sep 11, 2012 at 08:04:06PM +0600, AI Rumman wrote:
> Table size is 1186 MB.

if it takes long, it just means that your IO is slow.

> I split the command in three steps as you said, but the result same during
> the update operation.

three? I was showing four steps, and one of them is usually consisting
hundreds, if not thousands, of queries.

> One more thing, I have just restored the db from dump and analyzed it and
> I am using Postgresql 9.1 with 3 GB Ram with dual core machine.

so it looks like your IO channel is slow.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

On 09/11/2012 09:55 AM, AI Rumman wrote:
> I added the excel file for locks data.
> I was surprised to see that while I was updating a single column value
> for all records in a tables, all indexes are locked by the server.


Any ALTER TABLE command locks the whole table in ACCESS EXCLUSIVE mode,
indexes included. See the description of ACCESS EXCLUSIVE lock at
<http://www.postgresql.org/docs/current/static/explicit-locking.html>

cheers

andrew