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: 
 <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>
Comments: In-reply-to Erki Eessaar <erki.eessaar@taltech.ee>
	message dated "Sun, 15 Oct 2023 08:27:05 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2082191.1697382885.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 15 Oct 2023 11:14:45 -0400
Message-ID: <2082192.1697382885@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2082192.1697382885%40sss.pgh.pa.us>
Precedence: bulk

Erki Eessaar <erki.eessaar@taltech.ee> writes:
> After that I dropped ALL the columns of the table (by the way it is a ni=
ce 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 (wi=
th 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 o=
nce 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