List tables by the size of data and indexes in PostgreSQL database

Article for: MySQL

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


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


  • table_schema - table's schema name
  • table_name - table name
  • total_size - Total disk space used by the specified table, including all indexes and TOAST data
  • data_size - Disk space used by specified table or index
  • external_size - Disk space used by realted object to specified table


  • One row represents one table
  • Scope of rows: all tables in database
  • Ordered by total size descending, data size descending

Sample results

Disk space used by tables in our pagila database

There are no comments. Click here to write the first comment.