Get the tables by size - Postgres

view_tables_by_size.sql

-- source: https://makandracards.com/makandra/52141-postgresql-how-to-show-table-sizes

SELECT nspname || '.' || relname AS "relation",
  pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;

-- Example output:
-- relation	            | total_size
-- -----------------------------------
-- public.biggest_table	| 13 GB
-- public.other_table	| 6651 MB
-- public.next_table	    | 3021 MB
-- public.and_so_on	    | 1691 MB