Re: Adding a non-null column without noticeable downtime

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