Disk space not released after schema deletion

From: Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Disk space not released after schema deletion
Date: 2023-06-07 15:48:32
Message-ID: CAMsGm5f47KVmYZBVKjOKYxtu2iWG9NUOQQFXjS2G1JY6xruiJA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2023-06-07 15:53:56 Re: [DOCS] alter_foreign_table.sgml typo
Previous Message Fujii Masao 2023-06-07 15:36:39 Re: is_superuser is not documented