Postgresql : find size of all objects in a tablespace

Question

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

That sounds like table bloat: wiki.postgresql.org/wiki/Show_database_bloat You should also pass the oid to pg_total_relation_size otherwise this will give wrong results if you have the same table name in different schemas. You might also want to check the queries from here: wiki.postgresql.org/wiki/Disk_Usage — user330315
– user330315, Commented Nov 25, 2016 at 11:27
You query does not include materialized views - do you use them? — user330315
– user330315, Commented Nov 25, 2016 at 11:33

klin · Accepted Answer · 2016-11-25 11:42:39Z

1

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;

answered Nov 25, 2016 at 11:42

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql : find size of all objects in a tablespace

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related