Does PostgreSQL optimize adding columns with non-NULL DEFAULTs? When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?

In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default value - which could be very painful, obviously. With the optimization the DB would instantaneously create the new column, store just one copy of the default value which would be returned when no non-default value is found for that column in a suitable index data structure.

For example Oracle 11g has such an optimization.
There is no such mechanism in PostgreSQL.

However, you can still avoid the excessive effects of such a table change.

The following statement acquires an access exclusive lock on the table for the duration of the statement/transaction:

ALTER TABLE your_table
    ADD COLUMN new_column integer NOT NULL DEFAULT 0;


This statement changes the catalog, then rewrites the whole table so that the new column contains the default value in all rows.  If the table has many rows and being accessed frequently enough, this would cause some temporary problems.

To avoid it, try to hold the exclusive lock for as short as possible:

ALTER TABLE your_table
    ADD COLUMN new_column integer;
ALTER TABLE your_table
    ALTER COLUMN new_column SET DEFAULT 0;


As this is basically only a (actually two) change to the catalog (no data change happens), it will complete pretty fast.  Then depending on your needs and table usage, you can update the new column to the default in one step or in batches, and when finished, set the column to NOT NULL.

Update about a wish coming true: PostgreSQL 11 will have this feature.  See https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/ for more.