Fast ALTER TABLE ... ADD COLUMN ... DEFAULT xxx?

From: Dmitry Koterov <dmitry(at)koterov(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Fast ALTER TABLE ... ADD COLUMN ... DEFAULT xxx?
Date: 2009-05-21 08:06:29
Message-ID: d7df81620905210106h33f6cad4q227279bed4d99fa9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello.

PostgreSQL is very fast when we perform (even on a huge table)

ALTER TABLE ... ADD COLUMN ... NULL;

(nullable without a default value). This is because of NULL bitmap in
tuples. And it's greatest feature for a developer!

But another very common-case query like

ALTER TABLE ... ADD COLUMN ... BOOLEAN NOT NULL DEFAULT false;
or
ALTER TABLE ... ADD COLUMN ... INT NOT NULL DEFAULT 0;

for a huge table is performed very slow - this is because PostgreSQL have to
re-create all tuples assigning the default value to them. If I have a table
with 1 billion rows (for example), I have no chance to perform this query at
all - too slow.

(In most cases NOT NULL DEFAULT xxx fields are BOOLEAN, flags: it is not
handy to have 3-way flags.)

So, are there plans to optimize such kind of queries? This could be done by
many ways:

1. Store the DEFAULT flag directly in NULL BITMAP (add a bit to NULL bitmap
not only for NULLable fields, but also for NOT NULL DEFAULT ... fields).
2. Add another bitmap for each tuple (DEFAULT bitmap). Bit value 0 means
that there is a real value in a cell, 1 - that the value is default.
3. The same as (1), but always force default value to be 0 (or false or any
other values with meaning "zero") and optimize only these cases.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2009-05-21 09:50:56 Re: Fast ALTER TABLE ... ADD COLUMN ... DEFAULT xxx?
Previous Message Heikki Linnakangas 2009-05-21 07:01:59 Re: Compiler warning