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: Sun, 15 Oct 2023 11:14:45 -0400
Message-ID: <2082192.1697382885@sss.pgh.pa.us> (raw)
In-Reply-To: <AM9PR01MB8268A9EA16A0D77CD69D759BFED0A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
References: <AM9PR01MB82688DEDD3B7D0D85A58BDE3FED1A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
<1766109.1697302921@sss.pgh.pa.us>
<AM9PR01MB8268A9EA16A0D77CD69D759BFED0A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
Erki Eessaar <erki.eessaar@taltech.ee> writes:
> After that I dropped ALL the columns of the table (by the way it is a nice feature that PostgreSQL allows tables without columns). VACUUM ANALYZE did not change the relation size. VACUUM FULL reduced the size, but it was still pretty large.
You still have 15000 live rows. None of their columns are accessible,
but the data is still there physically. Plain VACUUM is not charged
with rewriting row contents, only with deleting dead rows, and there
are none to delete. IIRC, VACUUM FULL will rewrite the rows to have
just null entries, making them smaller (but not zero size).
> After that I tried to add a mandatory (NOT NULL) column to the table (with a new name). It failed!!
That would work only if you provided a non-null default to populate
the column with.
> After that I tried to add an optional (permit NULLs) column to the table. It succeeded but the table now had 15_000 rows with NULLs.
As expected. Reducing a row to zero columns does not eliminate the row.
> Is it possible somehow to reorganize pages so that the pages that were once used by the relation would be released?
VACUUM FULL/CLUSTER will do that, as will variants of ALTER TABLE
that force a table rewrite. (As you've found, we've expended a
fair amount of effort on avoiding that when possible.)
> The border case with evolving the table structure produces a result that from the point of view of database user is a bug.
You seem far too willing to use that word. We have generally
optimized these sorts of operations to run quickly, not to
reclaim disk space as soon as possible. In the project's
opinion, every one of these behaviors is a feature not a bug
--- and in many cases, a feature we put considerable sweat into.
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: <2082192.1697382885@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