The storage size of each schema data object (the system catalog excluded)

SELECT n.nspname AS object_schema, c.relname AS object_name,  CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='i' THEN 'INDEX'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
WHEN c.relkind='I' THEN 'PARTITIONED INDEX'
END AS object_type,
pg_size_pretty(pg_relation_size(c.oid)) AS data_size
FROM pg_class c INNER JOIN pg_namespace n ON (n.oid = c.relnamespace)
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND (c.relkind IN ('r','i','I','m','p'))
ORDER BY pg_relation_size(c.oid) DESC, nspname, relname;