
Knowing how much disk space individual tables take up is important for DB maintenance and debugging, and it can be accomplished with a single query in PostgreSQL.
First the query, then we'll explain parts of it:
sql
1SELECT n.nspname AS schema_name
2 , c.relname AS table_name
3 , pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
4 , pg_size_pretty(pg_table_size(c.oid)) AS table_size
5 , pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
6FROM pg_class c
7INNER JOIN pg_namespace n ON n.oid = c.relnamespace
8WHERE c.relkind IN ('r', 'm')
9 AND n.nspname NOT IN ('pg_catalog', 'information_schema')
10 AND n.nspname NOT LIKE 'pg_toast%'
11ORDER BY 1, 2;
Column explanations:
total_sizeis the total disk space used by the table, including its TOAST data and indexestable_sizeis the disk space used by the table and its TOAST dataindex_sizeis the disk space used by the table's indexes
Tables used (from the pg_catalog schema):
pg_class: catalogs tables and table-like objects (indexes, sequences, views, materialized views, composite types, and TOAST tables)pg_class.relkind:ris ordinary tables andmis materialized views - this excludes indexes (i), sequences (S), views (v), composite types (c), TOAST tables (t), and foreign tables (f)
pg_namespace: catalogs namespaces (schemas)
See "Calculating Table Size in MySQL" for the MySQL version of this query.

Calculating Table Size in MySQL
Sep 15, 2022 · 3 min read
Knowing how much disk space individual tables take up is important for DB maintenance and debugging, and it can be accomplished with a single query in MySQL.
Finding the largest tables
It's fairly easy to modify the above query to order by the largest total size:
sql
1SELECT n.nspname AS schema_name
2 , c.relname AS table_name
3 , pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
4 , pg_size_pretty(pg_table_size(c.oid)) AS table_size
5 , pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
6FROM pg_class c
7INNER JOIN pg_namespace n ON n.oid = c.relnamespace
8WHERE c.relkind IN ('r', 'm')
9 AND n.nspname NOT IN ('pg_catalog', 'information_schema')
10 AND n.nspname NOT LIKE 'pg_toast%'
11ORDER BY pg_total_relation_size(c.oid) DESC
12LIMIT 10;
This could be used to help debug a DB running out of space, or similar administrative tasks.
Finding the largest schemas
If you aggregate by nspname you can find which schemas are the largest:
sql
1SELECT n.nspname AS schema_name
2 , pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS total_size
3 , pg_size_pretty(sum(pg_table_size(c.oid))) AS table_size
4 , pg_size_pretty(sum(pg_indexes_size(c.oid))) AS index_size
5FROM pg_class c
6INNER JOIN pg_namespace n ON n.oid = c.relnamespace
7WHERE c.relkind IN ('r', 'm')
8 AND n.nspname NOT IN ('pg_catalog', 'information_schema')
9 AND n.nspname NOT LIKE 'pg_toast%'
10GROUP BY n.nspname
11ORDER BY sum(pg_total_relation_size(c.oid)) DESC
12LIMIT 10;




