postgresql list and order tables by size

postgresql, postgresql-9.3, sql

Solution

select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
  pg_total_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 3 desc;

This shows you the size of all tables in the schema `public` if you have multiple schemas, you might want to use:

select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
from information_schema.tables
order by 3

SQLFiddle example: http://sqlfiddle.com/#!15/13157/3

List of all object size functions in the manual.

Problem

Original source

Related problems

• What's the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
• list of schema with sizes (relative and absolute) in a PostgreSQL database
• Postgres DB Size Command