Re: add column with default value is very slow

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(at)depesz(dot)com> 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/
>