list of schema with sizes (relative and absolute) in a PostgreSQL database

postgresql

Solution

Try this:

SELECT schema_name, 
       sum(table_size),
       (sum(table_size) / database_size) * 100
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size,
         sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name, database_size

Edit: just noticed the workaround with summing up all tables to get the database size is not necessary:

SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100
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

Problem

I'm looking for a query that returns a result of the form for any database (see example below supposing total space used by the database is 40GB) ``` schema | size | relative size ----------+------------------- foo | 15GB | 37.5% bar | 20GB | 50% baz | 5GB | 12.5% ``` I've managed to concoct a list of space using entities in the database sorted by schema, which has been useful, but getting a summary per schema from this doesn't look so easy. See below. ``` SELECT relkind, relname, pg_catalog.pg_namespace.nspname, pg_size_pretty(pg_relation_size(pg_catalog.pg_class.oid)) FROM pg_catalog.pg_class INNER JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid ORDER BY pg_catalog.pg_namespace.nspname, pg_relation_size(pg_catalog.pg_class.oid) DESC; ``` This gives results like ``` relkind | relname | nspname | pg_size_pretty ---------+---------------------------------------+--------------------+---------------- r | geno | btsnp | 11 GB i | geno_pkey | btsnp | 5838 MB r | anno | btsnp | 63 MB i | anno_fid_key | btsnp | 28 MB i | ix_btsnp_anno_rsid | btsnp | 28 MB [...] r | anno | btsnp_shard | 63 MB r | geno4681 | btsnp_shard | 38 MB r | geno4595 | btsnp_shard | 38 MB r | geno4771 | btsnp_shard | 38 MB r | geno4775 | btsnp_shard | 38 MB ``` It looks like using an aggregation operator like SUM may be necessary, no success with that thus far.

Original source