Search code examples
postgresqldatabase-designddldatabase-performancepostgresql-9.2

Dropping column in Postgres on a large dataset

So I have a table with a large dataset and this table has a three columns that I would like to drop.
The question is: how will Postgres deal with it?

Will it walk through every entry or will it just update mapping info without much overhead? Can I just make an ALTER TABLE or should I use swap-table in this particular case?

And, if it makes any difference, all three columns have fixed length (two integers and one numeric).

I'm sorry if it's been asked already, but Google couldn't find any related questions / articles ...

Solution

  • 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.