Re: How much work is it to add/drop columns, really?

On Wed, Jan 27, 2010 at 5:45 PM, A B <gentosaker(at)gmail(dot)com> wrote:
> 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.

You are correct. It's the nullability AND non-default vaoue that
makes it cheap. Adding an empty column is cheap.

> 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?

Exactly. In fact you can run a job that updates x columns at a time,
run vacuum, then update x columns again to keep bloat down. as long
as x is about 1/10th or less of the total rows in the table you should
be able to keep it from bloating terribly.

> 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?

Ayup.

> Will autovaccum or other tools try to rewrite or "be clever " and
> optimize and causing a total rewrite of the table?

Nope

> Any other problems with adding/dropping columns that I'm unaware of?

The only thing I can think of is some issues with views on top of
those tables, or maybe other tables that reference it.