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.
