I was surprised that I didn’t easily find a query which gives me all of the table sizes for all of my Postgres database table. I did find this nice bit:
SELECT pg_size_pretty(pg_total_relation_size(table_name))
But I wanted to take it a step further and get the list of the largest tables in my database. This query worked like a charm:
SELECT table_name, pg_size_pretty(pg_total_relation_size(table_name))
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema = 'public'
ORDER BY pg_total_relation_size(table_name) DESC
Finally, I wanted to know if any of these tables had a column name containing the text “org id”.
SELECT t.table_name, pg_size_pretty(pg_total_relation_size(t.table_name))
, case when cn.table_name is not null then ‘Yes’ ELSE ” end as “has_col”
FROM information_schema.tables t
LEFT JOIN
(select distinct c.table_name
from information_schema.columns c
where c.column_name LIKE ‘%org%id%’) cn on cn.table_name = t.table_name
WHERE t.table_type=’BASE TABLE’ AND table_schema = ‘public’
ORDER BY pg_total_relation_size(t.table_name) DESC
;