MIME-Version: 1.0
From: Isaac Morland <isaac.morland@gmail.com>
Date: Wed, 7 Jun 2023 11:48:32 -0400
Message-ID: <CAMsGm5f47KVmYZBVKjOKYxtu2iWG9NUOQQFXjS2G1JY6xruiJA@mail.gmail.com>
Subject: Disk space not released after schema deletion
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000097f4805fd8c126c"
Archived-At: <https://www.postgresql.org/message-id/CAMsGm5f47KVmYZBVKjOKYxtu2iWG9NUOQQFXjS2G1JY6xruiJA%40mail.gmail.com>
Precedence: bulk

--000000000000097f4805fd8c126c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

The usual question is =E2=80=9Cwhy did DELETE not release disk space?=E2=80=
=9D, 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=E2=80=99m trying to make test/sample data files by starting with a restor=
ed 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=3D# 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=3D# 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=E2=80=99t expect VACUUM to do anything.

Does anybody know what could be holding all that space?

--000000000000097f4805fd8c126c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">The usual question is =E2=80=9Cwhy did DELETE not release =
disk space?=E2=80=9D, and I understand why that is and something about how =
to get the space back (VACUUM).<div><br></div><div>I have a database which =
hosts multiple applications in various schemas and I=E2=80=99m trying to ma=
ke test/sample data files by starting with a restored copy of production an=
d then dropping all schemas except for the ones I need for a particular app=
lication.</div><div><br></div><div>The total size of all relations after th=
e drop operations is just a few MB:</div><div><br></div><div>odyssey=3D# se=
lect sum (pg_total_relation_size (oid)) from pg_class;<br>=C2=A0 =C2=A0sum =
=C2=A0 =C2=A0<br>----------<br>=C2=A013877248<br>(1 row)<br></div><div><br>=
</div><div>Yet the database size is still large (although much smaller than=
 in the original database):</div><div><br></div><div>odyssey=3D# select dat=
name, pg_database_size (oid) from pg_database;<br>=C2=A0 datname =C2=A0| pg=
_database_size <br>-----------+------------------<br>=C2=A0postgres =C2=A0|=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A08930083<br>=C2=A0_repmgr =C2=A0 | =C2=A0=
 =C2=A0 =C2=A0 =C2=A0654934531<br>=C2=A0template0 | =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A08643075<br>=C2=A0template1 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A08864547<br>=C2=A0odyssey =C2=A0 | =C2=A0 =C2=A0 =C2=A014375453475<br>(5 =
rows)<br></div><div><br></div><div>The only change made after starting from=
 a basebackup of production was to set all the passwords to NULL in pg_auth=
id, and to delete most of the schemas. In particular, I wouldn=E2=80=99t ex=
pect VACUUM to do anything.</div><div><br></div><div>Does anybody know what=
 could be holding all that space?</div></div>

--000000000000097f4805fd8c126c--