Adding a non-null column without noticeable downtime

From: Zev Benjamin <zev-pgsql(at)strangersgate(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Adding a non-null column without noticeable downtime
Date: 2014-02-25 03:41:03
Message-ID: 530C10CF.4020101@strangersgate.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Frank Broniewski 2014-02-25 10:01:37 Re: Hard upgrade (everything)
Previous Message Bruce Momjian 2014-02-24 20:55:20 Re: plans for plpython in RDS?