Postgres Pro
Downloads
Home   >   mailing lists

Thread: Disk space usage analyzer?

Disk space usage analyzer?

From
Yang Zhang
Date:
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!

Re: Disk space usage analyzer?

From
Steve Crawford
Date:
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

Re: Disk space usage analyzer?

From
"Nicholson, Brad (Toronto, ON, CA)"
Date:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Steve Crawford
> Sent: Monday, March 28, 2011 12:22 PM
> To: Yang Zhang
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Disk space usage analyzer?
>
> 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.

pg_total_relation_size() will give you the size of the table and the indexes on it.

Brad.