Goal Get overview of disk usage by different schema objects that contain user data.
Type Sofware measure (Numeric values (software measures) about the database)
License MIT License
Data Source system catalog only
SQL Query
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;

Categories

This query is classified under the following categories:

NameDescription
Data at the database physical levelQueries of this category provide information about the disk usage.

Further reading and related materials:

Reference
https://wiki.postgresql.org/wiki/Disk_Usage