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

Erki Eessaar <erki(dot)eessaar(at)taltech(dot)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