Get the size of different tables and other relations in a PostgreSQL database

get_sizes.sql

SELECT
  schema_name, rel_name, table_size,
  pg_size_pretty(table_size) AS size
FROM (
  SELECT
    nspname AS schema_name,
    relname AS rel_name,
    pg_table_size(pg_class.oid) AS table_size
  FROM pg_class, pg_namespace
  WHERE pg_class.relnamespace = pg_namespace.oid
) _
WHERE schema_name NOT LIKE 'pg_%'
      AND schema_name != 'information_schema'
ORDER BY table_size DESC;


-- or if just tables is OK, then this simpler query..

SELECT
   relname AS table_name,
   pg_size_pretty(pg_total_relation_size(relid)) AS total,
   pg_size_pretty(pg_relation_size(relid)) AS internal,
   pg_size_pretty(pg_table_size(relid) - pg_relation_size(relid)) AS external,
   pg_size_pretty(pg_indexes_size(relid)) AS indexes
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

Tweaked so it includes the TOAST data as well.