From: hubert depesz lubaczewski <depesz@depesz.com>
To: AI Rumman <rummandba@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: add column with default value is very slow
Date: Tue, 11 Sep 2012 15:59:40 +0200
Message-ID: <20120911135939.GA1656@depesz.com> (raw)
In-Reply-To: <CAGoODpeorc_7U57NYKsf68Gd3wi9VM+=2kJDZ=89Tk=J=XE=pg@mail.gmail.com>
References: <CAGoODpdBTkhXDuOQDLxuEJYrWfsZZCi_tkjgzDyzPRGDHeQ9ag@mail.gmail.com>
<20120911134425.GA27828@depesz.com>
<CAGoODpeorc_7U57NYKsf68Gd3wi9VM+=2kJDZ=89Tk=J=XE=pg@mail.gmail.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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: depesz@depesz.com, rummandba@gmail.com
Subject: Re: add column with default value is very slow
In-Reply-To: <20120911135939.GA1656@depesz.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox