Hello there.
I read http://www.postgresql.org/docs/current/static/sql-altertable.html
and find it interesting that
" Adding a column with a non-null default or changing the type of an
existing column will require the entire table to be rewritten. This
might take a significant amount of time for a large table; and it will
temporarily require double the disk space."
So adding a new column WITHOUT any default value is actually a quite
cheap operation then? Some quick tests seem to indicate that.
So if you can live with having a null values there until the value is
set (or you let a cron job run and set the value to a desired
"default value" for one row at a time), then adding columns will not
be a real problem? No serious locking for a long time?
And droping a column seems even quicker
"The DROP COLUMN form does not physically remove the column, but
simply makes it invisible to SQL operations. Subsequent insert and
update operations in the table will store a null value for the column.
Thus, dropping a column is quick but it will not immediately reduce
the on-disk size of your table, as the space occupied by the dropped
column is not reclaimed. The space will be reclaimed over time as
existing rows are updated. "
So that is really quick then?
Will autovaccum or other tools try to rewrite or "be clever " and
optimize and causing a total rewrite of the table?
Any other problems with adding/dropping columns that I'm unaware of?
Best wishes.