Subject: Disk space not released after schema deletion

From: Isaac Morland <[email-masked]>
Date: Wed, 7 Jun 2023 11:48:32 -0400

The usual question is “why did DELETE not release disk space?”, and I
understand why that is and something about how to get the space back
(VACUUM).

I have a database which hosts multiple applications in various schemas and
I’m trying to make test/sample data files by starting with a restored copy
of production and then dropping all schemas except for the ones I need for
a particular application.

The total size of all relations after the drop operations is just a few MB:

odyssey=# select sum (pg_total_relation_size (oid)) from pg_class;
   sum
----------
 13877248
(1 row)

Yet the database size is still large (although much smaller than in the
original database):

odyssey=# select datname, pg_database_size (oid) from pg_database;
  datname  | pg_database_size
-----------+------------------
 postgres  |          8930083
 _repmgr   |        654934531
 template0 |          8643075
 template1 |          8864547
 odyssey   |      14375453475
(5 rows)

The only change made after starting from a basebackup of production was to
set all the passwords to NULL in pg_authid, and to delete most of the
schemas. In particular, I wouldn’t expect VACUUM to do anything.

Does anybody know what could be holding all that space?