Calculating Table Size in PostgreSQL

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:

SELECT n.nspname                                     AS schema_name
     , c.relname                                     AS table_name
     , pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
     , pg_size_pretty(pg_table_size(c.oid))          AS table_size
     , pg_size_pretty(pg_indexes_size(c.oid))        AS index_size
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
ORDER BY 1, 2;

Column explanations:

• total_size is the total disk space used by the table, including its TOAST data and indexes
• table_size is the disk space used by the table and its TOAST data
• index_size is 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: r is ordinary tables and m is 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 · 2 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:

SELECT n.nspname                                     AS schema_name
     , c.relname                                     AS table_name
     , pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
     , pg_size_pretty(pg_table_size(c.oid))          AS table_size
     , pg_size_pretty(pg_indexes_size(c.oid))        AS index_size
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 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:

SELECT n.nspname                                          AS schema_name
     , pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS total_size
     , pg_size_pretty(sum(pg_table_size(c.oid)))          AS table_size
     , pg_size_pretty(sum(pg_indexes_size(c.oid)))        AS index_size
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
GROUP BY n.nspname
ORDER BY sum(pg_total_relation_size(c.oid)) DESC
LIMIT 10;