On-disk postgres database size too large

[Maxwell Dreytser <Maxwell.Dreytser@xxxxxxxxxxxx>]

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     .

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.

Thank you,
Maxwell.