Does adding a null column to a postgres table cause a lock?

postgresql alter-table
jpadvo picture jpadvo · Oct 22, 2013 · Viewed 24.4k times · Source

I think I read somewhere that running an ALTER TABLE foo ADD COLUMN baz text on a postgres database will not cause a read or write lock. Setting a default value causes locking, but allowing a null default prevents a lock.

I can't find this in the documentation, though. Can anyone point to a place that says, definitively, if this is true or not?

Answer

Daniel Vérité picture Daniel Vérité · Oct 22, 2013

The different sorts of locks and when they're used are mentioned in the doc in Table-level Locks. For instance, Postgres 11's ALTER TABLE may acquire a SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, or ACCESS EXCLUSIVE lock.

Postgres 9.1 through 9.3 claimed to support two of the above three but actually forced Access Exclusive for all variants of this command. This limitation was lifted in Postgres 9.4 but ADD COLUMN remains at ACCESS EXCLUSIVE by design.

It's easy to check in the source code because there's a function dedicated to establishing the lock level needed for this command in various cases: AlterTableGetLockLevel in src/backend/commands/tablecmds.c.

Concerning how much time the lock is held, once acquired:

  • When the column's default value is NULL, the column's addition should be very quick because it doesn't need a table rewrite: it's only an update in the catalog.
  • When the column has a non-NULL default value, it depends on PostgreSQL version: with version 11 or newer, there is no immediate rewriting of all the rows, so it should be as fast as the NULL case. But with version 10 or older, the table is entirely rewritten, so it may be quite expensive depending on the table's size.

Related questions

How can I add a column that doesn't allow nulls in a Postgresql database?

I'm adding a new, "NOT NULL" column to my Postgresql database using the following query (sanitized for the Internet): ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL; Each time I run this query, I receive the following error …

Read More
Postgresql - change the size of a varchar column to lower length

I have a question about the ALTER TABLE command on a really large table (almost 30 millions rows). One of its columns is a varchar(255) and I would like to resize it to a varchar(40). Basically, I would like to change …

Read More
How to alter a column's data type in a PostgreSQL table?

Entering the following command into a PostgreSQL interactive terminal results in an error: ALTER TABLE tbl_name ALTER COLUMN col_name varchar (11); What is the correct command to alter the data type of a column?

Read More