Re: Disk space usage analyzer? - Mailing list pgsql-general

On 03/25/2011 07:58 PM, Yang Zhang wrote:
> Is there any tool for breaking down how much disk space is used by
> (could be freed by removing) various tables, indexes, selected rows,
> etc.?  Thanks!
>

You can use the pg_class table and the pg_relation_size (and optionally
the pg_size_pretty) functions to get that info. This query gives table
sizes and percent of overall usage.

BUT! It is only looking at tables, not indexes. If you want to know how
much space will be freed by dropping a table, you will have to modify
this query to total up the index space used for all the indexes
associated with each table.

select
     relname as table,
     lpad(pg_size_pretty(pg_relation_size(oid)), 9) as size,
     (100*pg_relation_size(oid)/(select sum(pg_relation_size(oid)) from
pg_class where relkind='r'))::numeric(4,1) as percent
from
     pg_class
where
     relkind = 'r'
order by
     relpages desc
;

               table               |   size    | percent
----------------------------------+-----------+---------
  foobar                           |   2310 MB |    19.7
  foobaz                           |   2021 MB |    17.2
  bazbar                           |   1642 MB |    14.0
...

Cheers,
Steve