CODEWITHSUNDEEP
postgresqlalter-table
jpadvo
jpadvo

Reputation: 6655

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

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?

Upvotes: 50

Views: 48961

Answers (4)

pir
pir

Reputation: 5933

Setting a default value causes locking, but allowing a null default prevents a lock.

The other answers are outdated. Since Postgres 11, adding a column with a default value (null or otherwise), will not require a rewrite of the table. It will only lock the table for a few milliseconds so essentially not locking it all. Adding a column with a default value is now a fast and cheap operation!

Upvotes: 2

Daniel Vérité
Daniel Vérité

Reputation: 61686

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.

Upvotes: 65

Leo
Leo

Reputation: 1136

http://www.postgresql.org/docs/current/static/sql-altertable.html#AEN57290

"Adding a column with a non-null default or changing the type of an existing column will require the entire table and indexes to be rewritten."

So the documentation only specifies when the table is not rewritten. There will always be a lock, but it will be very short in case the table is not to be rewritten.

Upvotes: 13

alexius
alexius

Reputation: 2576

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.

So when you need to add column with default value to big table it's recommended to add null value first and then update all rows in small portions. This way you'll avoid high load on disk and allow autovacuum to do it's job so you'll not end up doubling table size.

Upvotes: 38

Related Questions