pg_table_sizes.sql

--- Formatted size for total usage, percentages for indexes, toast, data.
--- No system tables.
SELECT pg_class.oid :: regclass AS tab,
       pg_size_pretty(total) AS disk_usage,
       (100.0 * index / greatest(1, total)) :: numeric(6, 2) AS "%index",
       (100.0 * toast / greatest(1, total)) :: numeric(6, 2) AS "%toast",
       (100.0 * (total - index - toast) / greatest(1, total)) :: numeric(6, 2) AS "%data",
       reltuples AS "~rows"
  FROM pg_class LEFT JOIN pg_namespace ON pg_namespace.oid = relnamespace,
       pg_total_relation_size(pg_class.oid) AS total,
       pg_indexes_size(pg_class.oid) AS index,
       COALESCE(pg_total_relation_size(reltoastrelid), 0) AS toast
 WHERE relkind = 'r'
   AND nspname NOT IN ('information_schema', 'pg_catalog');