CODEWITHSUNDEEP
postgresql
Georgi
Georgi

Reputation: 382

Postgresql : find size of all objects in a tablespace

I am running out of space on my default tablespace. 

SELECT pg_size_pretty(pg_tablespace_size('pg_default'));
89 GB
(1 row)

However, if I calculate the pg_relation_size of all tables in this tablespace i only get 16GB

select pg_size_pretty(sum(sz)) 
from (
    select *,(pg_total_relation_size(tablename::varchar)) as sz 
    from pg_tables 
    where tablespace is null 
      and tablename not like 'pg_%' 
     and schemaname != 'information_schema' 
) as foo;

16 GB
(1 row)

So where is the rest?

I do calculate the index+data

Upvotes: 1

Views: 2383

Answers (1)

klin
klin

Reputation: 121504

Multiple databases may use server's default tablespace. Check it with:

select datname, pg_size_pretty(pg_database_size(oid))
from pg_database
union
select null, pg_size_pretty(sum(pg_database_size(oid)))
from pg_database;

Upvotes: 1

Related Questions