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?
