7

I have a heavily used table (with around 5 million rows) in a PostgreSQL database in which I want to drop a column and reclaim the space the column used.

The docs suggest doing a table rewriting ALTER TABLE to force the space back, but that is not safe to run while the table is used and would cause downtime. Are there any practical options that don't require any downtime? I tried to run the pgcompact tool, but that did not change anything.

9

ALTER TABLE .. DROP COLUMN ... marks the column as deleted in the system table pg_attribute. The table itself is not otherwise manipulated until rows are rewritten some way or another. The drop itself is very fast, but it does take a brief ACCESS EXCLUSIVE lock. I would not call that "downtime", though.

Actually reclaiming disk space is the tricky part. You could use the community tool pg_repack instead of VACUUM FULL or CLUSTER (which lock the table exclusively). It avoids exclusive locks, but it needs free space on disk to operate. More details:

Or, if rows in your table are updated eventually, you can just wait it out. Every new row version is written without the deleted column. When the old row version is overwritten or removed by VACUUM you regain the space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.