Hi,
We have a business management product that we sell to small businesses. Over the last few months we noticed that some of the backups are growing in size much faster than they should. Obviously I am persisting something that I should not.
After consulting Dr Goggle I have a simple query to determine candidate culprit tables that are growing too fast. (see below). It includes TOAST data and it has identified a few candidate tables that I would never have considered investigating.
Now I have some candidate tables - but I'm still struggling to identify which are the culprit columns within those tables that are taking up the space.
Can anyone advise how to get the aggregate data size in columns (including toasted data)?
So I am looking for column data size (inc toast) PER TABLE (not per row).
In the scheme of things these databases are small (generally << 100MB on disk), so elegance, efficiency & performance are not significant issues.
Any suggestions, guidance, tips, tricks etc would be much appreciated.
Many thanks for your time,
-Damian
==== Query to check data usage of tables ============
SELECT relname AS "relation_my_table_name",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size_inc_toast",
pg_size_pretty(relpages::bigint*8*1024) AS "size_sans_toast",
pg_size_pretty((pg_total_relation_size(C.oid)) - (relpages::bigint*8*1024)) AS "toasted_size",
reltuples AS "#_of_entries",
to_char((pg_total_relation_size(C.oid)/reltuples),'999999D9') AS "size_per_entry"
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'
AND reltuples > 0 -- avoid DivByZero in size_per_entry
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;