Does adding "NOT NULL DEFAULT X" Column to Postgres cause a table re-write?

Question

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?

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