42

I have a table in my PostgreSQL 9.6 database with 3 million rows. This table already has a null bitmap (it has 2 other DEFAULT NULL fields). I want to add a new boolean nullable column to this table. I stuck with the difference between these two statements:

ALTER TABLE my_table ADD COLUMN my_column BOOLEAN;
ALTER TABLE my_table ADD COLUMN my_column BOOLEAN DEFAULT NULL;

I think that these statements have no difference, but:

  1. I can't find any proof of it in documentation. Documentation tells that providing DEFAULT value for the new column makes PostgreSQL to rewrite all the tuples, but I don't think that it's true for this case, cause default value is NULL.
  2. I ran some tests on copy of this table, and the first statement (without DEFAULT NULL) took a little bit more time than the second. I can't understand why.

My questions are:

  1. Will PostgreSQL use the same lock type (ACCESS EXCLUSIVE) for those two statements?
  2. Will PostgreSQL rewrite all tuples to add NULL value to every of them in case that I use DEFAULT NULL?
  3. Are there any difference between those two statements?
Improve this question
1

2 Answers 2

Reset to default
50

In Postgres 10 or older (such as OP's 9.6), any DEFAULT clause even DEFAULT NULL will require a full rewrite of the table, which might take essentially forever for large tables[1].

Per the documentation:

When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified). If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead.

This tell us that if there is a DEFAULT clause, even if it is NULL, it will rewrite all the tuples.

I tested this by my own on Postgresql 9.6, when i had to add a column, on a table that had 300+ million tuples. Without the DEFAULT NULL it lasted 11 ms, and with the DEFAULT NULL it lasted more than 30 minutes.

This was changed with Postgres 11, Vao Tsun's answer is only correct for these, per the Postgres 11 documentation:

When a column is added with ADD COLUMN and a non-volatile DEFAULT is specified, the default is evaluated at the time of the statement and the result stored in the table's metadata. That value will be used for the column for all existing rows. If no DEFAULT is specified, NULL is used. In neither case is a rewrite of the table required.

So as long as DEFAULT is a static value or a non-volatile expression the table will not need to be rewritten.

[1]: whether 3 million rows is large is debatable.
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

please add your test case to an answer - I grounded my answer by quoting the statements with /timing. Also please add it with your full build version.
Note that the current version (11) of the docs says that "In neither case is a rewrite of the table required." assuming the default is non-volatile (volatile would be things like random() which would be a silly default). so this applies to older versions only.
See postgresql.org/docs/11/sql-altertable.html under the section Notes
The conclusions here are not accurate. There is no difference between DEFAULT NULL and the absence of DEFAULT. See @vau-tsun's answer, explained well.
There is an important difference in the notes of the doc between version 10 and version 11 ! Version 11: In neither case is a rewrite of the table required. Version 10: If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead.
18

https://www.postgresql.org/docs/current/static/sql-altertable.html

  1. Yes - same ACCESS EXCLUSIVE, no exceptions for DEFAULT NULL or no DEFAULT mentionned (statistics, "options", constraints, cluster would require less strict I think, but not add column)

Note that the lock level required may differ for each subform. An ACCESS EXCLUSIVE lock is held unless explicitly noted. When multiple subcommands are listed, the lock held will be the strictest one required from any subcommand.

  1. No - it will rather append NULL to result on select

When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified). If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead.

  1. No - no difference AFAIK. Just metadata change in both cases (as I believe it is one case expressed with different semantics)

Edit - Demo:

db=# create table so(i int);
CREATE TABLE
Time: 9.498 ms
db=# insert into so select generate_series(1,10*1000*1000);
INSERT 0 10000000
Time: 13899.190 ms
db=# alter table so add column nd BOOLEAN;
ALTER TABLE
Time: 1025.178 ms
db=# alter table so add column dn BOOLEAN default null;
ALTER TABLE
Time: 13.849 ms
db=# alter table so add column dnn BOOLEAN default true;
ALTER TABLE
Time: 14988.450 ms
db=# select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 on x86_64-apple-darwin15.6.0, compiled by Apple LLVM version 8.0.0 (clang-800.0.42.1), 64-bit
(1 row)

lastly to avoid speculations it is data type specific:

db=# alter table so add column t text;
ALTER TABLE
Time: 25.831 ms
db=# alter table so add column tn text default null;
ALTER TABLE
Time: 13.798 ms
db=# alter table so add column tnn text default 'null';
ALTER TABLE
Time: 15440.318 ms
Improve this answer

1 Comment

As commented in the other answer, there is a big difference starting with PG 11. As for your measurements, I have performed the same several times on PG 10 and the 'default null' version sometimes takes forever and sometimes is lightenting fast...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.