Database Administration - PostgreSQL 9 Administration - page 158

Though the ALTER TABLE runs much faster. That's very cool if you want to do an update,
though not much fun if you want to keep the column data already there.
How it works...
ALTER TABLE , to add or drop a column, takes a full table lock (at AccessExclusiveLock level);
so that it can prevent all other actions on the table. So, we want it to be as fast as possible.
DROP COLUMN doesn't actually remove the column from each row of the table, it just marks
the column as dropped. This makes DROP COLUMN a very fast operation.
ADD COLUMN is also very fast if we are adding a nullable column with a null default value. If
we use a NOT NULL constraint, or we specify an explicit default value, then we need to rewrite
every row of the table, which can be quite slow.
ALTER TABLE allows us to execute many column operations at once, as shown in the main
recipe. ALTER TABLE is optimized, so that we include all column operations into a single pass
of the table, greatly improving speed for complex sets of changes, for example:
ALTER TABLE mytable
ADD COLUMN last_update_userid INTEGER,
ADD COLUMN last_update_comment TEXT;
If we rewrite the table, then the dropped columns are removed. If not, they may stay there
for some time. Subsequent INSERT and UPDATE operations will insert a null value for the
dropped column(s). Updates will reduce the size of stored rows if they were not already null.
So in theory, you just have to wait, and the database will eventually reclaim the space. In
practice, this only works if all of the rows in the table are updated within a given period of
time. Many tables contain historical data, and so space would not be reclaimed at all without
additional actions.
The PostgreSQL manual recommends changing the data type of a column to the same type,
which forces rewriting every row. I don't recommend this because it will completely lock the
table for a long period, at least on larger databases. My recommendation is to not drop the
column at all, if you can avoid it, when you're in production. Just keep a track of the changes
you would make if you get time, if ever. If you're looking at alternatives, then VACUUM will not
rewrite the table, though a VACUUM FULL will. Though be careful there also, because that also
holds a full table lock.
There's more...
Indexes that depend upon a dropped column are automatically dropped as well. All other
objects that depend upon the column(s) will cause the ALTER TABLE to be rejected. You can
override that, and drop everything in sight by using the CASCADE option, as follows:
ALTER TABLE x
DROP COLUMN last_update_timestamp
CASCADE;