Fetch Sizes of Tables and Indices in a Database

fetch_tables_indices_size_in_database.sql

-- Source: https://stackoverflow.com/a/21738732/495558

-- NOTE: This script is adjusted from the original:
-- - Added Indices' size.
-- - Limited only those whose size exceeding 100MiB.

select
  schema_name,
  relname,
  pg_size_pretty(table_size) as size,
  table_size,
  pg_size_pretty(index_size) as index_size_pretty,
  index_size
from (
   select
    pg_catalog.pg_namespace.nspname as schema_name,
    relname,
    pg_relation_size(pg_catalog.pg_class.oid) as table_size,
    pg_indexes_size(pg_catalog.pg_class.oid) as index_size
      from pg_catalog.pg_class
        join pg_catalog.pg_namespace on relnamespace = pg_catalog.pg_namespace.oid
    ) t
where
  schema_name not like 'pg_%' 
    and table_size >= 104857600
order by table_size desc;