Re: Dropping column from big table

> But the only issue would be "VACUUM FULL" will take a table lock and also it
> may take longer to run this vacuum on the full table considering the size of
> the table in TB's. Thus, is it fine to just leave it post execution of the
> "update" statement , so that the normal vacuum operation (which will be online
> operation) and that will take care of the removal of old rows ?

This is unlikely to recover the space.

The UPDATE will duplicate all rows. Since - presumably - there isn't
much free space within each partition the new rows will go at the end of
each partition, effectively doubling its size.

A regular VACUUM (whether autovacuum or invoked manually) will then
remove the old rows. and make the space available for new data. But
since that newly free space is at the beginning of each partition it
can't be returned to the OS. It would be available for new data written
to those partitions I guess not much is written to old partitons.

You could, however, do this in small steps and vacuum after each.
Something like this (in Python)

    conn = ...
    csr = conn.cursor()
    for i in range(100):
        csr.execute("UPDATE tab set id = id WHERE id % 100 = %s", (i,))
        conn.commit()
        csr.execute("VACUUM tab")
        conn.commit()

That might just be able to squeeze the new rows in between the existing
rows and not grow the table.


>  And also, As you mentioned we may also leave it as is and wait for the
> partition to be dropped, so that the dropped column with "not null" values
> which are still lying under the hood and are occupying space will be removed
> automatically. But even then, is that dropped column still lying in the rows
> with null values in it throughout its lifecycle, till the table exists in the
> database?

Yes. But a nullable column with a null value takes only a single bit of
storage, so that's negligible.