Hi,
I'm trying to understand why my database consume so much space. I checked the space it consume on disk :
[root@ base]# du -sh * | sort -n
1.1T 17312
5.2G pgsql_tmp
6.3M 1
6.3M 12865
6.4M 12870
119G 17313
myBIGdb=# select t1.oid,t1.datname AS db_name,pg_size_pretty(pg_database_size(t1.datname)) as db_size from pg_database t1 order by pg_database_size(t1.datname) desc
myBIGdb-# ;
oid | db_name | db_size
-------+----------------+---------
17312 | myBIGdb | 1054 GB
17313| mySmallDB | 118 GB
12870 | postgres | 6525 kB
1 | template1 | 6417 kB
12865 | template0 | 6409 kB
(5 rows)
However, when checking the sizes of my biggest tables (included with indexes and toasts) :
select a.oid as oid a.relname as table_name,pg_relation_size(a.oid, 'main')/1024/1024 as main_MB,
pg_relation_size(a.oid, 'fsm')/1024/1024 as fsm_MB,
pg_relation_size(a.oid, 'vm')/1024/1024 as vm_MB,
pg_relation_size(a.oid, 'init')/1024/1024 as init_MB,
pg_table_size(a.oid)/1024/1024 AS relation_size_mb, pg_indexes_size(a.oid)/1024/1024 as indexes_MB,
pg_total_relation_size(a.oid)/1024/1024 as total_size_MB
from pg_class a where relkind in ('r','t') order by relation_size_mb desc,total_size_MB desc limit 10;
oid | table_name | main_mb | fsm_mb | vm_mb | init_mb | relation_size_mb | indexes_mb | total_size_mb
------+-----------------------------+---------+--------+-------+---------+------------------+------------+---------------
17610 | table_1 | 1 | 0 | 0 | 0 | 115306 | 0 | 115306
17614 | pg_toast_17610 | 114025 | 28 | 0 | 0 | 114053 | 1250 | 115304
17315 | table_2 | 166 | 0 | 0 | 0 | 2414 | 18 | 2432
17321 | pg_toast_17315 | 2222 | 0 | 0 | 0 | 2223 | 24 | 2247
17540 | table_3 | 1016 | 0 | 0 | 0 | 1368 | 1606 | 2975
17634 | table_4 | 628 | 0 | 0 | 0 | 677 | 261 | 938
17402 | table_5 | 623 | 0 | 0 | 0 | 623 | 419 | 1043
17648 | table_5 | 393 | 0 | 0 | 0 | 393 | 341 | 735
17548 | pg_toast_17540 | 347 | 0 | 0 | 0 | 347 | 4 | 351
17835 | table 6 | 109 | 0 | 0 | 0 | 109 | 71 | 181
As you can see , the sum of the biggest tables is under 200G. In addition, I know that on that database there were some vacuum full operations that failed. So is there an option of orphans files in case vacuum full failed ? In addition, what else would you recommend to check to understand why the database consume so much space ?
Thanks .
