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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Paul Guo <pguo(at)pivotal(dot)io>, PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: How to reclaim the space of dropped columns of a table?
Date: 2019-07-15 16:52:06
Message-ID: 20956.1563209526@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Mon, Jul 15, 2019 at 8:42 AM Paul Guo <pguo(at)pivotal(dot)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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrey Lepikhov 2019-07-15 17:08:41 Re: Insecure initialization of required_relids field
Previous Message Bruce Momjian 2019-07-15 16:43:05 Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)