On Wed, Jan 27, 2010 at 5:45 PM, A B <gentosaker@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
