From: | sud <suds1434(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>, "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
Subject: | Re: Dropping column from big table |
Date: | 2024-07-16 05:04:36 |
Message-ID: | CAD=mzVW0A-TeX2Pce2N+7xQGy3hEi2xfUntH007MQ0TY=cVRkw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Jul 16, 2024 at 10:26 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>
> On Monday, July 15, 2024, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
>> On Monday, July 15, 2024, sud <suds1434(at)gmail(dot)com> wrote:
>>
>>>
>>> However even with "vacuum full", the old rows will be removed completely
>>> from the storage , but the new rows will always be there with the 'dropped'
>>> column still existing under the hood along with the table storage, with
>>> just carrying "null" values in it. […] Is this understanding correct?
>>>
>>
>> No. The table rewrite process involves creating new tuples that exactly
>> conform to the current row specification. The potentially non-null data
>> present in live tuples for columns that have been dropped are not copied
>> into the newly constructed tuples.
>>
>>
>> https://github.com/postgres/postgres/blob/d2b74882cab84b9f4fdce0f2f32e892ba9164f5c/src/backend/access/heap/heapam_handler.c#L2499
>>
>>
> My bad, stopped at the code comment. Apparently the data is just nulled,
> not removed, the current row descriptor contains those columns with “is
> dropped” and since this behavior doesn’t change the catalogs in this way
> the new ones must as well. We just get the space back.
>
>
>
Thank you for the confirmation.
And if someone wants to fully remove that column from the table , then the
only option is to create a new table with an exact set of active columns
and insert the data into that from the existing/old table and then rename
it back to old. Is this correct understanding?
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2024-07-16 05:26:55 | Re: PostgreSQL Active-Active Clustering |
Previous Message | David G. Johnston | 2024-07-16 04:56:23 | Re: Dropping column from big table |