From: Tom Lane <tgl@sss.pgh.pa.us>
To: Erki Eessaar <erki.eessaar@taltech.ee>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: System administration functions about relation size ignore
 changes in the table structure
In-reply-to: 
 <AM9PR01MB82688DEDD3B7D0D85A58BDE3FED1A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
References: 
 <AM9PR01MB82688DEDD3B7D0D85A58BDE3FED1A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
Comments: In-reply-to Erki Eessaar <erki.eessaar@taltech.ee>
	message dated "Sat, 14 Oct 2023 09:30:48 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1766108.1697302921.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 14 Oct 2023 13:02:01 -0400
Message-ID: <1766109.1697302921@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1766109.1697302921%40sss.pgh.pa.us>
Precedence: bulk

Erki Eessaar <erki.eessaar@taltech.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 o=
r 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