Re: Dropping column from big table

On Wed, Jul 10, 2024 at 11:28 PM sud <suds1434(at)gmail(dot)com> wrote:

>
> On Thu, Jul 11, 2024 at 2:52 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>>
>> https://www.postgresql.org/docs/current/sql-altertable.html
>>
>> "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. This results in reconstructing each row with the
>> dropped column replaced by a null value.
>> "
>>
>>
> Thank you so much. When you said *"you can execute one of the forms of
> ALTER TABLE that performs a rewrite*
> *of the whole table."* Does it mean that post "alter table drop column"
> the vacuum is going to run longer as it will try to clean up all the rows
> and recreate the new rows? But then how can this be avoidable or made
> better without impacting the system performance
>

"Impact" is a non-specific word. "How much impact" depends on how many
autovacuum workers you've set it to use, and how many threads you set in
vacuumdb.

> and blocking others?
>

VACUUM never blocks.

Anyway, DROP is the easy part; it's ADD COLUMN that can take a lot of time
(depending on whether or not you populate the column with a default value).

I'd detach all the partitions from the parent table, and then add the new
column to the not-children in multiple threads, add the column to the
parent and then reattach all of the children. That's the fastest method,
though takes some time to set up.