DYK: Postgres doesn't really deletes the column when you DROP COLUMN

We've recently encountered an issue that caused our service to fail to load because of unexpected behavior from PostgreSQL.

Background Story:

We have a Golang backend server for a service that polls data regularly from various sources to implement digital risk protection. Although this isn't directly related to the issue at hand, it's important context. Our server startup logic includes dropping and recreating a column in a particular table with different constraints. This is because, during a version upgrade, we needed to change the TSV constraints of a column, and PostgreSQL 15.2 doesn't support altering such metadata directly. To address this, we added a snippet to drop and recreate the column within our database creation flow at server start, ensuring both existing and new customers remain unaffected. During development, we frequently restart the service, causing these commands to run each time. However, after one such restart, the service failed to start without any changes to the code or configuration, which was perplexing.

Issue:

Upon debugging, we discovered that one of our tables had reached its maximum column limit of 1600 columns, preventing any new columns from being added. The error occurred because dropping a column in PostgreSQL doesn't immediately free up the space; it only makes the column invisible to SQL operations. The documentation states:

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.

To force immediate reclamation of space occupied by a dropped column, you can execute one of the forms of ALTER TABLE that performs a rewrite of the whole table.

In PostgreSQL, you can use the following commands to forcefully delete and reclaim the space:

ALTER TABLE table_name DROP COLUMN column_name;
VACUUM FULL table_name;

This approach ensures that the space occupied by the dropped column is immediately reclaimed, preventing the issue from recurring.

Thanks!