Query below returns tables in a database with space they use ordered from the ones using most.

Query

select schemaname as table_schema,
       relname as table_name,
       pg_size_pretty(pg_relation_size(relid)) as data_size
from pg_catalog.pg_statio_user_tables
order by pg_relation_size(relid) desc;

Columns

• table_schema - name of schema
• table_name - name of table
• data_size - space used

Rows

• One row represents one table in a database
• Scope of rows: all tables in a database
• Ordered by table used size, from largest to smallest

Sample results

Tables in pagila ordered from the ones using most space to least.