Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

For my own future reference, here's a query for finding size of the biggest relations in postgres, the tables and indexes. Taken from the wiki, but I've modified it so any pg_toast tables also display the tablename that they come from.

SELECT CASE WHEN relname like 'pg_toast%' THEN CONCAT(relname, ' (', CAST(rtrim(ltrim(relname, 'pg_toast_'), '_index') as INTEGER)::regclass, ')') ELSE relname END as relation, 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') ORDER BY pg_relation_size(C.oid) DESC LIMIT 50;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.