Re: System administration functions about relation size ignore changes in the table structure

Erki Eessaar <erki(dot)eessaar(at)taltech(dot)ee> writes:
> According to the example at the end of the letter the functions pg_total_relation_size, pg_table_size and pg_indexes_size seem to ignore adding or removing columns from a table.

I don't think this is a bug, it's more like a poor test case designed
with no understanding of Postgres internals.

1. Dropping a column doesn't ever produce an instantaneous reduction
of table size. The column is still there physically. We'll replace
it with NULLs over time as existing rows are updated.

2. Adding a column may not cause an instantaneous increase in size
either, if we can avoid an immediate table rewrite; which is true
if the column has a null or constant default value.

3. Even for your example with adding a generated column, you would
only see an increase in table size if we could now fit fewer rows per
page than before. Your test case seems to involve comparing a table
with 3 integer-size columns to one with 3 integers and 2 smallints.
On 64-bit machines those come out to the same size (16 data bytes per
row) because the row length is padded to a multiple of 8 bytes for
alignment reasons.

regards, tom lane