From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Maxwell Dreytser <Maxwell.Dreytser@assistek.com>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: On-disk postgres database size too large
Date: Tue, 11 Mar 2025 22:43:12 +0100
Message-ID: <c00bcfd8fe94d487d47ac02c5f990150cbfd26a9.camel@cybertec.at> (raw)
In-Reply-To: <LV3PR16MB6001F47ABD404082124CC981E6D12@LV3PR16MB6001.namprd16.prod.outlook.com>
References: <LV3PR16MB6001F47ABD404082124CC981E6D12@LV3PR16MB6001.namprd16.prod.outlook.com>
On Tue, 2025-03-11 at 17:01 +0000, Maxwell Dreytser wrote:
> I recently noticed that my PostgreSQL data directory has grown oddly large.
>
> Strangely the database that seems to take the most disk space is the system
> postgres database. Not the one that data is actually stored in.
>
> Here are the sizes of all the folders inside the `/data/base` folder.
>
> # du --max-depth=1 -x -h
> 28G ./5
> 7.3M ./1
> 7.2M ./4
> 4.0K ./pgsql_tmp
> 28G .
>
> From what I understand, these folders should correspond to database OIDs, so
> I ran the following statement to investigate the database responsible:
>
> SELECT oid, datname, pg_size_pretty(pg_database_size(datname)) db_size
> FROM pg_database
> ORDER BY pg_database_size(datname);
>
> oid | datname | db_size
> -------+-----------+---------
> 5 | postgres | 6612 kB
> 1 | template1 | 6612 kB
> 4 | template0 | 7329 kB
> 16384 | data | 92 MB
>
> 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.
>
> 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 --verbose -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.
>
> No change in data size on disk.
>
> Here are the top few files in the `/data/base/5` folder:
>
> /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
>
> I tried grabbing the latest one to try to pin down what exactly is in that
> file, but no luck there:
>
> SELECT relname, relnamespace::regnamespace, relkind
> FROM pg_class WHERE relfilenode = 427725;
>
> relname | relnamespace | relkind
> ---------+--------------+---------
> (0 rows)
>
> At this point I am quite stumped. What else can I look at to clean up some
> 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 can
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, Maxwell.Dreytser@assistek.com, pgsql-admin@lists.postgresql.org
Subject: Re: On-disk postgres database size too large
In-Reply-To: <c00bcfd8fe94d487d47ac02c5f990150cbfd26a9.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox