size | Laur IVAN

Postgres: Show Schema Sizes in a Database

While loading a large database from files to a Postgres schema, I found very useful to know how much space on disk it actually occupies (just in case I need to re-size the disk). I found this answer online:

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

SELECT schema_name,

pg_size_pretty(sum(table_size)::bigint) as "disk space",

(sum(table_size) / pg_database_size(current_database())) * 100

as "percent"

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

Which results in a list like: HTH, PS: All credit goes to this StackOverflow answer! A little experiment: If you find this post and ad below useful, please check the ad out :-)