> Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.
Clearly there are workarounds where you make a new empty column that you then slowly backfill with the correct value and eventually transactionally rename the new column to the old one, but the magic "oh postgres does DDL in a transaction so it's magic and we never have to worry" is pretty much gone at that point.
> Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.
Clearly there are workarounds where you make a new empty column that you then slowly backfill with the correct value and eventually transactionally rename the new column to the old one, but the magic "oh postgres does DDL in a transaction so it's magic and we never have to worry" is pretty much gone at that point.