We have a Rails app powered by Postgresql v11.4 where I want to add a new column with a default value and a not null constraint like below:

ALTER TABLE "blogs" ADD "published" boolean DEFAULT FALSE NOT NULL

I know adding a new column with a default value is safe. However, is it still safe when combined with NOT NULL? Or will it lock the database? Thanks!

1 Answer 1


That statement is safe.

An ALTER TABLE will never lock "the database", it will only lock the table.

Postgres will not actually rewrite the table because you provided a constant value (false) for the default value. So adding the column is actually done in a few milliseconds because Postgres only stores the information that a new column is available. When that column is accessed (and no value is available) then it will use the default value from the column's definition. Only when a row is updated, the new row will contain an actual value for the column.

If the default value was an expression that could potentially be different for each row, Postgres would rewrite the table to physically put the default column into all rows. But still, that would only lock the table, not the database.

From Postgres documentation about ALTER TABLE: "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."

And tested in Postgres v.11: dbfiddle.uk

  • I did 3 migrations on 3 different tables. 2 of them worked like a charm, but for some reason it did end up locking the whole DB for 121 seconds on the third migration. 🤔
    – ogirginc
    Commented May 13, 2022 at 8:44
  • 1
    @ogirginc: well, as I wrote: an ALTER TABLE never locks the database. Maybe it was waiting for an exclusive lock which then in turn made other requests on that table also wait for its completion. It's impossible to tell what was going on ther, but it's not inherent in the ALTER TABLE you are running
    – user1822
    Commented May 13, 2022 at 8:49

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.