Re: Adding a non-null column without noticeable downtime

On Mon, Feb 24, 2014 at 7:41 PM, Zev Benjamin
<zev-pgsql(at)strangersgate(dot)com> wrote:
[...]
> 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 should set default before performing updates, otherwise new rows
will be with nulls in this column. The template sniplet for your case
is below.

ALTER TABLE foo ADD bar boolean;

ALTER TABLE foo ALTER bar SET DEFAULT false;

CREATE INDEX CONCURRENTLY foo_migration_tmp
ON foo (id) WHERE bar IS NULL;

/*

PSQL=/usr/local/bin/psql

total_updated=0
updated=1
time (
while [ $updated -gt 0 ]; do
updated=$(($PSQL -X Game2 <<EOF
UPDATE foo SET bar = false
WHERE id IN (
SELECT id FROM foo
WHERE bar IS NULL LIMIT 100);
EOF
) | cut -d ' ' -f 2)
(( total_updated+=updated ))
echo -ne "\r$total_updated"
done
) 2>&1

*/

DROP INDEX foo_migration_tmp;

ANALYZE foo;

ALTER TABLE foo ALTER bar SET NOT NULL;

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com