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
Date: Sat, 14 Oct 2023 13:02:01 -0400
Message-ID: <1766109.1697302921@sss.pgh.pa.us> (raw)
In-Reply-To: <AM9PR01MB82688DEDD3B7D0D85A58BDE3FED1A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
References: <AM9PR01MB82688DEDD3B7D0D85A58BDE3FED1A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, erki.eessaar@taltech.ee, pgsql-bugs@lists.postgresql.org
Subject: Re: System administration functions about relation size ignore changes in the table structure
In-Reply-To: <1766109.1697302921@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox