Skip to content

Instantly share code, notes, and snippets.

@dcosson
Last active May 25, 2016 17:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dcosson/db2759221bd69b17ec191abc8975dd5d to your computer and use it in GitHub Desktop.
Save dcosson/db2759221bd69b17ec191abc8975dd5d to your computer and use it in GitHub Desktop.

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