Re: Difference between "add column" and "add column" with default

Guido Neitzer <guido(dot)neitzer(at)pharmaline(dot)de> writes:
> What is the technical difference between adding a column to a table
> and then apply a "set value = ..." to all columns and adding a column
> with a default value = ...?

"ADD COLUMN DEFAULT ..." is implemented via a full-table rewrite,
so you end up with a version of the table that has no dead space.
Unfortunately this requires an exclusive table lock while the rewrite
happens, so you lock out other processes from the table for a
considerably longer period of time than the UPDATE approach. IIRC it's
also not completely MVCC-safe --- committed-dead rows will get removed
even if there are old open transactions that should still see those rows
as current. Bottom line: there's no free lunch.

regards, tom lane