UPDATE June 2020
That first SQL isn't working in Postgres 12 and onwards. Use this instead:
SELECT relname AS "table_name",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND relkind = 'r'
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 10;
We have lots of tables that weigh a lot. Some of the tables are partitions so they're called "mytable_20150901" and "mytable_20151001" etc.
To find out how much each table weighs you can use this query:
select table_name, pg_relation_size(table_name), pg_size_pretty(pg_relation_size(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2 desc limit 10;
It'll give you an output like this:
table_name | pg_relation_size | pg_size_pretty --------------------------+------------------+---------------- raw_adi_logs | 14724538368 | 14 GB raw_adi | 14691426304 | 14 GB tcbs | 7173865472 | 6842 MB exploitability_reports | 6512738304 | 6211 MB reports_duplicates | 4428742656 | 4224 MB addresses | 4120412160 | 3930 MB missing_symbols_20150601 | 3264897024 | 3114 MB missing_symbols_20150608 | 3170762752 | 3024 MB missing_symbols_20150622 | 3039731712 | 2899 MB missing_symbols_20150615 | 2967281664 | 2830 MB (10 rows)
But as you can see in this example, it might be interesting to know what the sum is of all the missing_symbols_* partitions.
Without further ado, here's how you do that:
select table_name, total, pg_size_pretty(total)
from (
select trim(trailing '_0123456789' from table_name) as table_name,
sum(pg_relation_size(table_name)) as total
from information_schema.tables
where table_schema = 'public'
group by 1
) as agg
order by 2 desc limit 10;
Then you'll get possibly very different results:
table_name | total | pg_size_pretty --------------------------+--------------+---------------- reports_user_info | 157111115776 | 146 GB reports_clean | 106995695616 | 100 GB reports | 100983242752 | 94 GB missing_symbols | 42231529472 | 39 GB raw_adi_logs | 14724538368 | 14 GB raw_adi | 14691426304 | 14 GB extensions | 12237242368 | 11 GB tcbs | 7173865472 | 6842 MB exploitability_reports | 6512738304 | 6211 MB signature_summary_uptime | 6027468800 | 5748 MB (10 rows)
You can read more about the trim() function here.
Comments