On Wed, Jul 10, 2024 at 11:28 PM sud <suds1...@gmail.com> wrote: > > On Thu, Jul 11, 2024 at 2:52 AM Adrian Klaver <adrian.kla...@aklaver.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.
