Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL ADD COLUMN DEFAULT NULL locks and performance

Tags:

postgresql

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?
like image 251
Ivan Kalita Avatar asked Dec 08 '17 07:12

Ivan Kalita


People also ask

Does adding a column lock the table PostgreSQL?

Adding new null column will lock the table for very very short time since no need to rewrite all data on disk. While adding column with default value requires PostgreSQL to make new versions of all rows and store them on the disk. And during that time table will be locked.

Are Postgres columns nullable by default?

NULL The column is allowed to contain null values. This is the default.

Do null columns take up space Postgres?

Answer: No, Each null value only uses one bit on disk.

How do I make a column nullable in PostgreSQL?

From the fine manual: ALTER TABLE mytable ALTER COLUMN mycolumn DROP NOT NULL; There's no need to specify the type when you're just changing the nullability.


1 Answers

There's a issue in the response of Vao Tsun in point 2.

If you use ALTER TABLE my_table ADD COLUMN my_column BOOLEAN; it won't rewrite all the tuples, it will be just a change in the metadata.

But if you use ALTER TABLE my_table ADD COLUMN my_column BOOLEAN DEFAULT NULL, it will rewrite all the tuples, and it will last for ever on long tables.

The documentation itself tells this.

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.

This is due to a performance issue on the updates clause. If you need to make an update over a no rewrited tuple, it will need to move the tuple to another disk space, consuming more time.

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.

like image 170
leo begher Avatar answered Sep 22 '22 01:09

leo begher