Add New Column Without Table Lock

Add new column without table lock?

Postgres 11 or later

Since Postgres 11, only volatile default values still require a table rewrite. The manual:

Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten.

Bold emphasis mine. false is immutable. So just add the column with DEFAULT false. Super fast, job done:

ALTER TABLE tbl ADD column delta boolean DEFAULT false;

Postgres 10 or older, or for volatile DEFAULT

Adding a new column without DEFAULT or DEFAULT NULL will not normally force a table rewrite and is very cheap. Only writing actual values to it creates new rows. But, quoting the manual:

Adding a column with a DEFAULT clause or changing the type of an
existing column will require the entire table and its indexes to be rewritten.

UPDATE in PostgreSQL writes a new version of the row. Your question does not provide all the information, but that probably means writing millions of new rows.

While doing the UPDATE in place, if a major portion of the table is affected and you are free to lock the table exclusively, remove all indexes before doing the mass UPDATE and recreate them afterwards. It's faster this way. Related advice in the manual.

If your data model and available disk space allow for it, CREATE a new table in the background and then, in one transaction: DROP the old table, and RENAME the new one. Related:

• Best way to populate a new column in a large table?

While creating the new table in the background: Apply all changes to the same row at once. Repeated updates create new row versions and leave dead tuples behind.

If you cannot remove the original table because of constraints, another fast way is to build a temporary table, TRUNCATE the original one and mass INSERT the new rows - sorted, if that helps performance. All in one transaction. Something like this:

BEGIN

SET temp_buffers = 1000MB;  -- or whatever you can spare temporarily

-- write-lock table here to prevent concurrent writes - if needed
LOCK TABLE tbl IN SHARE MODE;    

CREATE TEMP TABLE tmp AS
SELECT *, false AS delta
FROM   tbl;                -- copy existing rows plus new value
-- ORDER BY ???            -- opportune moment to cluster rows

-- DROP all indexes here

TRUNCATE tbl;              -- empty table - truncate is super fast

ALTER TABLE tbl ADD column delta boolean DEFAULT FALSE; -- NOT NULL?

INSERT INTO tbl
TABLE tmp;                 -- insert back surviving rows.

-- recreate all indexes here

COMMIT;

ALTER TABLE without locking the table?

The only other option is to do manually what many RDBMS systems do anyway...

- Create a new table

You can then copy the contents of the old table over a chunk at a time. Whilst always being cautious of any INSERT/UPDATE/DELETE on the source table. (Could be managed by a trigger. Although this would cause a slow down, it's not a lock...)

Once finished, change the name of the source table, then change the name of the new table. Preferably in a transaction.

Once finished, recompile any stored procedures, etc that use that table. The execution plans will likely no longer be valid.

EDIT:

Some comments have been made about this limitation being a bit poor. So I thought I'd put a new perspective on it to show why it's how it is...

• Adding a new field is like changing one field on every row.
• Field Locks would be much harder than Row locks, never mind table locks.
  
  
• You're actually changing the physical structure on the disk, every record moves.
• This really is like an UPDATE on the Whole table, but with more impact...

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

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 Topics