Обсуждение: How to reclaim the space of dropped columns of a table?
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).
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:
Thanks.
"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.
On Mon, Jul 15, 2019 at 8:42 AM Paul Guo <pguo@pivotal.io> wrote:
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)?
Probably. But it doesn't seem worth the effort to accomplish. The amount of data involved (and VACUUM FULL does perform the table rewrite described) to represent the missing column is minimal.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jul 15, 2019 at 8:42 AM Paul Guo <pguo@pivotal.io> wrote:
>> 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)?
> Probably. But it doesn't seem worth the effort to accomplish. The amount
> of data involved (and VACUUM FULL does perform the table rewrite described)
> to represent the missing column is minimal.
Completely removing a column is pretty impractical, because that would
require renumbering subsequent columns, which would have potential impacts
throughout the system catalogs (for example, in views referencing this
table, or foreign key info for other tables referencing this one).
There's been repeated discussion about separating the concepts of
a column's (a) permanent identifier for catalog purposes, (b)
physical position in table rows, and (c) logical position as
reflected in "SELECT *" ordering. If we had that, this sort of
thing would be much more practical. But making that happen is a
large and very bug-prone task, so it hasn't been done (yet).
regards, tom lane