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
