How to reclaim the space of dropped columns of a table?

From: Paul Guo <pguo(at)pivotal(dot)io>
To: PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: How to reclaim the space of dropped columns of a table?
Date: 2019-07-15 15:42:11
Message-ID: CAEET0ZEBVajjf6wF6Q6e1VEucr0QG40o5-CPB7LW3H7t7AtgVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello hackers,

I have been having a question about this with no answer from various sources
. As known after dropping a column using 'alter table', table is not
rewritten and vacuum full does not remove them also (still see the dropped
column in pg_attribute).

PG document says:

https://www.postgresql.org/docs/current/sql-altertable.html

"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. This results in reconstructing each row with the dropped
column replaced by a null value."

This seems to a bit vague for users (how to rewrite but keep the table
definition) and it seems to still keep the dropped columns (though with
null). Isn't it better to leave the functionality to command like 'vacuum
full' to completely remove the dropped columns (i.e. no dropped columns in
pg_attributes and no null values for dropped columns for a table)?

Thanks.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David G. Johnston 2019-07-15 15:55:43 Re: How to reclaim the space of dropped columns of a table?
Previous Message John Naylor 2019-07-15 15:18:07 Re: PG 12 draft release notes