Re: Determine potential change in table size after a column dropped?

On Sat, Jan 22, 2022, 12:47 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com> wrote:

> I have a large large large table with many many many rows, and it's a
> certain size in pg_relation_size -- there's a timestamp with tz column on
> this table that's mostly kind of useless, and I want to figure out how much
> space it would free if we just dropped it. Can I easily do this?
>

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.

............

But you could still try to get a rough estimate, you could use
pg_column_size() and pg_stat_user_tables to get an approx set of tuples and
multiply with the size. (Not including indexes though)

>