Re: add column with default value is very slow

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Table size is 1186 MB.
I split the command in three steps as you said, but the result same during the update operation.
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.


On Tue, Sep 11, 2012 at 7:59 PM, hubert depesz lubaczewski <depesz@xxxxxxxxxx> wrote:
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/


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux