From: | Zev Benjamin <zev-pgsql(at)strangersgate(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Adding a non-null column without noticeable downtime |
Date: | 2014-02-25 17:20:16 |
Message-ID: | 530CD0D0.1010905@strangersgate.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
To be clear, this is with PostgreSQL 9.1. Also, if there is some other
way of doing this, I'd be interested in other methodologies as well.
Zev
On 02/24/2014 10:41 PM, Zev Benjamin wrote:
> Hi all,
>
> I'm sure this has been answered somewhere, but I was not able to find
> anything in the list archives.
>
> I'm conceptually trying to do
> ALTER TABLE "foo" ADD COLUMN "bar" boolean NOT NULL DEFAULT False;
>
> without taking any noticeable downtime. I know I can divide the query
> up like so:
>
> ALTER TABLE "foo" ADD COLUMN "bar" boolean;
> UPDATE foo SET bar = False; -- Done in batches
> ALTER TABLE "foo" ALTER COLUMN "bar" SET DEFAULT False;
> ALTER TABLE "foo" ALTER COLUMN "bar" SET NOT NULL;
>
> The first 3 queries shouldn't impact other concurrent queries on the
> system. My question is about the sequential scan that occurs when
> setting the column NOT NULL. Will that sequential scan block other
> inserts or selects on the table? If so, can it be sped up by using an
> index (which would be created concurrently)?
>
>
> Thanks,
> Zev
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Sameer Kumar | 2014-02-25 18:56:19 | Re: Adding a non-null column without noticeable downtime |
Previous Message | David Johnston | 2014-02-25 16:17:48 | Re: JSON vs Text + Regexp Index Searching |