Reclaim disk space from dropped column without downtime

Question

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.

Answer 1

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. That's more like a "down-moment" than "downtime".

Actually reclaiming disk space is the tricky part. You could use a community tool like pg_repack or pg_squeeze, instead of the built-in VACUUM FULL or CLUSTER which lock the table exclusively. It avoids exclusive locks, but either needs free space on disk to operate. More details:

• VACUUM returning disk space to operating system

Or, if rows in your table are updated eventually, you can just wait it out. Every new row version is written with the dropped column set to NULL. When the old row version is overwritten or removed by VACUUM you basically regain the space. Some corner case exceptions apply, though. See:

• Dropping column in Postgres on a large dataset