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