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.

Query

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;

Columns

  • 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

Rows

  • 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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.