PostgreSQL 11 Administration Cookbook

How it works…

The ALTER TABLE statement, which is used to add or drop a column, takes a full table lock (at the AccessExclusiveLock lock level) so that it can prevent all other actions on the table. So, we want it to be as fast as possible.

The DROP COLUMN command doesn't actually remove the column from each row of the table; it just marks the column as dropped. This makes DROP COLUMN a very fast operation.

The ADD COLUMN command is also very fast if we are adding a nullable column with a non-volatile default value, for example, a NULL value or a constant. A non-volatile expression always returns the same value, when computed multiple times within the same SQL statement; this means that PostgreSQL can compute the default value once and write ...