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

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.

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