We are working on a RDS Postgresql 14.4 instance and lately we discovered that our storage space was being bloated. We went from 46Gb total size on a freshly migrated database to 81Gb in the course of a week.
We found this article : diskfull-error-rds-postgresql
We checked if we had a huge number of dead rows:
SELECT SUM(n_dead_tup) FROM pg_stat_all_tables WHERE n_dead_tup > 0;
which gave us 114836
It doesn't seem significant considering the amount of space that is taken.
We checked our database total space:
SELECT pg_size_pretty(pg_database_size('db_production'));
It gave us 81Gb
then we compared it to:
SELECT pg_size_pretty(SUM(pg_relation_size(oid))) FROM pg_class;
which gave us 46Gb
In the article it is stated that it might be caused by orphan files that are being stored.
Unfortunately we cant execute a VACUUM FULL on the whole database because we can't afford downtime on our platform
I'm trying to understand where this 35Gb gap is coming from in order to handle it manually (if possible)
Does anyone have any clue on how I could solve this issue ?
Thank you in advance for your kind help :)
Edit: After running SELECT pg_size_pretty(SUM(pg_total_relation_size(oid))) FROM pg_class; it gave us 70Gb
So that is much closer to our actual database size but we still are missing 11Gb :(