Re: Adding a non-null column without noticeable downtime

On 02/25/2014 04:41 AM, Zev Benjamin wrote:
> 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;

You need to set the default before doing the update. Also, make sure
the update is in its own transaction.

> 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?

Yes, because ALTER TABLE will have taken an AccessExclusiveLock.

> If so, can it be sped up by using an index (which would be created
> concurrently)?

Unfortunately not.

--
Vik