PostgreSQL schema sizes

pg_schemas_size.sql

WITH schema_size AS (
    SELECT
      tab.table_catalog                                             AS database_name,
      tab.table_schema                                              AS schema_name,
      tab.table_name,
      pg_total_relation_size(table_schema || '.' || tab.table_name) AS table_size_total,
      pg_relation_size(table_schema || '.' || tab.table_name)       AS table_size
    FROM information_schema.tables tab
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
), pretty_size AS (
    SELECT
      database_name,
      schema_name,
      pg_database_size(database_name)                 AS database_size_bigint,
      pg_size_pretty(pg_database_size(database_name)) AS database_size,
      sum(table_size_total)                           AS schema_size_bigint_total,
      pg_size_pretty(sum(table_size_total))           AS schema_size_total,
      sum(table_size)                                 AS schema_size_bigint,
      pg_size_pretty(sum(table_size))                 AS schema_size
    FROM schema_size
    GROUP BY database_name, schema_name
)
SELECT
  database_name,
  schema_name,
  database_size,
  schema_size_total,
  schema_size,
  ((schema_size_bigint_total * 100) / database_size_bigint) AS perc_total,
  ((schema_size_bigint * 100) / database_size_bigint)       AS perc
FROM pretty_size;