Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?

Question

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.

Answer 1

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.

Answer 2

Yes, with PostgreSQL 11

This feature is new and landed in Version 11.

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

The above is one such command that will be affected by this optimization; but, it should be said the NOT NULL is not required. Any new column added with a non-null default is optimized now. You can find the entry in this commitfest You should also check up this great write-up about it, "A Missing Link in Postgres 11: Fast Column Creation with Defaults".

Pre-PostgreSQL 11 Workaround

If you're trying to avoid the exclusive table-lock on the table follow the advice from Craig Ringer,

• Add a column without a DEFAULT
• ALTER it to add the DEFAULT afterwards so it applies to newly inserted rows
• Then populate the new column on existing rows by progressive batch UPDATEs
• When all rows have the value you add the NOT NULL constraint