Dropping column in Postgres on a large dataset

Google may be useless for this question, but the manual rarely fails:

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.

And:

To force an immediate rewrite of the table, you can use VACUUM FULL, CLUSTER or one of the forms of ALTER TABLE that forces a rewrite. This results in no semantically-visible change in the table, but gets rid of no-longer-useful data.

Specifically, the column attisdropped in the system catalog table pg_attribute is set to true.

Side effects

There are minor side-effects (as Chris pointed out):

• Updated or newly inserted rows still store an invisible NULL value which forces a NULL bitmap for every new row, even with no NULL in visible columns. Does not affect existing rows as those keep the original (now invisible) column value.

• The NULL bitmap must be big enough to cover all visible and dropped columns. In corner cases this may enlarge the NULL bitmap. About effective size:

  • Do nullable columns occupy additional space in PostgreSQL?

• Dropped columns count against the allowed maximum (which you shouldn't be scraping anyway).

• There is currently (Postgres 13) no easy way to get rid of the zombi column completely. The above mentioned table rewrites replace the invisible value with NULL (which reclaims almost all space), but neither purges the dropped column from the system catalogs. Not even TRUNCATE. Only creating a new table (or a dump/restore cycle) does that.

ALTER TABLE DROP COLUMN does just only disabling columns in system tables. It is very fast, but it doesn't remove data from heap files. You have to do VACUUM FULL later to compact allocated file space. So ALTER TABLE DROP COLUMN is very fast. And to compact files, you have to call the slower (with exclusive LOCK) VACUUM FULL.