Message-ID: <c00bcfd8fe94d487d47ac02c5f990150cbfd26a9.camel@cybertec.at>
Subject: Re: On-disk postgres database size too large
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Maxwell Dreytser <Maxwell.Dreytser@assistek.com>,
 "pgsql-admin@lists.postgresql.org"
	 <pgsql-admin@lists.postgresql.org>
Date: Tue, 11 Mar 2025 22:43:12 +0100
In-Reply-To: 
 <LV3PR16MB6001F47ABD404082124CC981E6D12@LV3PR16MB6001.namprd16.prod.outlook.com>
References: 
	<LV3PR16MB6001F47ABD404082124CC981E6D12@LV3PR16MB6001.namprd16.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/c00bcfd8fe94d487d47ac02c5f990150cbfd26a9.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-03-11 at 17:01 +0000, Maxwell Dreytser wrote:
> I recently noticed that my PostgreSQL data directory has grown oddly larg=
e.
>=20
> Strangely the database that seems to take the most disk space is the syst=
em
> postgres database. Not the one that data is actually stored in.
>=20
> Here are the sizes of all the folders inside the `/data/base` folder.
>=20
> # du --max-depth=3D1 -x -h
> 28G     ./5
> 7.3M    ./1
> 7.2M    ./4
> 4.0K    ./pgsql_tmp
> 28G     .
>=20
> From what I understand, these folders should correspond to database OIDs,=
 so
> I ran the following statement to investigate the database responsible:
>=20
> SELECT oid, datname, pg_size_pretty(pg_database_size(datname)) db_size
> FROM pg_database
> ORDER BY pg_database_size(datname);
>=20
>   oid  |  datname  | db_size
> -------+-----------+---------
>      5 | postgres  | 6612 kB
>      1 | template1 | 6612 kB
>      4 | template0 | 7329 kB
>  16384 | data      | 92 MB
>=20
> OID 5 seems to be the postgres database, not the "data" database that is
> actually being used and one of the databases even scratch 100MB.=20
>=20
> So, I decided to try to do a full vacuum in case something was broken in
> the autovacuum config. So, I ran `vacuumdb --full --analyze --all --verbo=
se -U postgres`.
> Judging from the output nothing really got cleaned up and there are
> `0 dead row versions cannot be removed yet.` for all tables.=20
>=20
> No change in data size on disk.
>=20
> Here are the top few files in the `/data/base/5` folder:
>=20
> /data/base/5# ls -lahS
> total 28G
> -rw------- 1 1001 aad_admins 324M Mar  3 05:07 423445
> -rw------- 1 1001 aad_admins 323M Feb 27 05:05 421330
> -rw------- 1 1001 aad_admins 323M Feb 21 05:07 409693
> -rw------- 1 1001 aad_admins 323M Feb 23 05:12 410429
> -rw------- 1 1001 aad_admins 322M Mar  4 05:03 423919
> -rw------- 1 1001 aad_admins 322M Mar  3 05:02 423128
> -rw------- 1 1001 aad_admins 322M Mar 11 05:35 427725
>=20
> I tried grabbing the latest one to try to pin down what exactly is in tha=
t
> file, but no luck there:
>=20
> SELECT relname, relnamespace::regnamespace, relkind
> FROM pg_class WHERE relfilenode =3D 427725;
>=20
>  relname | relnamespace | relkind
> ---------+--------------+---------
> (0 rows)
>=20
> At this point I am quite stumped. What else can I look at to clean up som=
e
> space. I am running PostgreSQL 16.3.

One very odd thing is that there is no directory named "16384" in the "base=
"
directory.  Unless you are using tablespaces (check pg_tablespace), that ca=
n
mean only one thing: you are looking at the data directory of a different
cluster.  You can examine the value of the "data_directory" parameter to be
certain.

Yours,
Laurenz Albe