Postgres Pro
Downloads
Home   >   mailing lists

Thread: Schema Size - PostgreSQL 9.2

Schema Size - PostgreSQL 9.2

From
"drum.lucas@gmail.com"
Date:
Hi all,

Can you please provide me a Query that tells me how much space is a Schema in my DB?

I'm using one but don't think it's right....

SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as a
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name

Re: Schema Size - PostgreSQL 9.2

From
Karsten Hilbert
Date:
On Fri, Mar 18, 2016 at 09:38:30AM +1300, drum.lucas@gmail.com wrote:

> Can you please provide me a Query that tells me how much space is a Schema
> in my DB?

There's been a discussion on that recently (like last month)
which can be found in the archive.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Schema Size - PostgreSQL 9.2

From
Melvin Davidson
Date:


On Thu, Mar 17, 2016 at 4:45 PM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
On Fri, Mar 18, 2016 at 09:38:30AM +1300, drum.lucas@gmail.com wrote:

> Can you please provide me a Query that tells me how much space is a Schema
> in my DB?

There's been a discussion on that recently (like last month)
which can be found in the archive.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


This should give you what you need:

SELECT n.nspname as schema,
       pg_size_pretty(sum(pg_total_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(c.relname)))) as size,
       sum(pg_total_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(c.relname))) as size_bytes
  FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_authid a ON ( a.oid = c.relowner )
  WHERE relname NOT LIKE 'pg_%'
    AND relname NOT LIKE 'information%'
    AND relname NOT LIKE 'sql_%'
    AND relkind IN ('r')
GROUP BY 1
ORDER BY 3 DESC, 1, 2;


--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.