Calculating Table Size in PostgreSQL

Christian Emmer
Christian Emmer
Nov 26, 2021 · 2 min read
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)

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.