1. Does adding a column with NOT NULL DEFAULT X lock the table and cause a table re-write in Postgres 14?

  2. If it doesn't lock the table and cause a table-rewrite (assuming it's just a metadata change), then I imagine subsequent schema operations might trigger a data re-write. The following operations I suspect might trigger a re-write:

    • What happens if I drop the NOT NULL constraint from my new column? Will this cause X to be written to all the rows.
    • What if I change the default value from X to Y on the new column?

1 Answer 1


Since PostgreSQL v11, adding a column with a non-NULL constant default value will not rewrite the table (if X is a non-constant expression, it will rewrite the table). It is always easy to test that yourself:

  • create the table empty

  • see what the file name behind the table is:

    SELECT relfilenode
    FROM pg_class
    WHERE relname = 'my_table';
  • run the ALTER TABLE statement

  • check the file name again

If the file name changed, the statement rewrote the table.

To specifically address your further questions:

  • neither adding nor dropping NOT NULL rewrite the table, but the former has to scan the table, which also can take a long time

  • changing a default value never rewrites the table

  • 1
    Makes sense! I think I understand now after your post and reading . For my 2 questions: A table re-write is not required because all new rows have the default value already set. The metadata shim is only required for the rows that already exist in the table when the new column is added.
    – Bryce
    Apr 16 at 22:27

Your Answer

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

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