Dropping column in Postgres on a large dataset

postgresql database-design ddl database-performance postgresql-9.2
nikita2206 picture nikita2206 · Mar 29, 2013 · Viewed 12.2k times · Source

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

Answer

Pavel Stehule picture Pavel Stehule · Mar 29, 2013

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.

Related questions

Primary key for multiple column in PostgreSQL?

How to provide primary key for multiple column in a single table using PostgreSQL? Example: Create table "Test" ( "SlNo" int not null primary key, "EmpID" int not null, /* Want to become primary key */ "Empname" varchar(50) null, "EmpAddress" varchar(50) null ); Note: …

Read More
Safely rename tables using serial primary key columns

I know that PostgreSQL tables that use a SERIAL primary key end up with an implicit index, sequence and constraint being created by PostgreSQL. The question is how to rename these implicit objects when the table is renamed. Below is …

Read More
Grant all on a specific schema in the db to a group role in PostgreSQL

Using PostgreSQL 9.0, I have a group role called "staff" and would like to grant all (or certain) privileges to this role on tables in a particular schema. None of the following work GRANT ALL ON SCHEMA foo TO staff; GRANT …

Read More