pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOn-disk postgres database size too large
2+ messages / 2 participants
[nested] [flat]
* On-disk postgres database size too large
@ 2025-03-11 17:01 Maxwell Dreytser <Maxwell.Dreytser@assistek.com>
0 siblings, 1 reply; 2+ messages in thread
From: Maxwell Dreytser @ 2025-03-11 17:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hello,
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 .
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: On-disk postgres database size too large
@ 2025-03-11 21:43 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Maxwell Dreytser <Maxwell.Dreytser@assistek.com>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2025-03-11 21:43 UTC (permalink / raw)
To: Maxwell Dreytser <Maxwell.Dreytser@assistek.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-03-11 21:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-11 17:01 On-disk postgres database size too large Maxwell Dreytser <Maxwell.Dreytser@assistek.com>
2025-03-11 21:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
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