PostgreSQL high table and index sizes

I always forget how to check the size of a table in Postgres, as well as the differences between pg_table_size, pg_relation_size, and pg_total_relation_size. The best explanation I’ve found is in this Stack Overflow thread.

So for future me:

-- total_relation_size = relation_size + toast_size + index_size
-- relation_size = size of the table’s main data (heap)
-- toast_size = storage for oversized column data stored off-page
-- index_size = disk space used by its indexes.
WITH table_sizes AS (
    SELECT
        ns.nspname AS schema_name,
        c.relname,
        c.reltuples AS row_estimate, -- estimated number of rows
        pg_total_relation_size(c.oid) AS total_relation_size,
        pg_relation_size(c.oid) AS relation_size,
        pg_indexes_size(c.oid) AS index_size,
        pg_table_size(c.oid) AS table_size,
        pg_table_size(c.oid) - pg_relation_size(c.oid) AS toast_size
    FROM pg_class c
    JOIN pg_namespace ns ON c.relnamespace = ns.oid
    WHERE ns.nspname NOT LIKE 'pg_%'
      AND ns.nspname != 'information_schema'
      AND c.relkind = 'r' -- only select regular tables
)
SELECT
    schema_name AS schema,
    relname AS table_name,
    row_estimate::bigint, -- cast to bigint for display consistency
    pg_size_pretty(total_relation_size) AS total_size,
    pg_size_pretty(relation_size) AS relation_size,
    pg_size_pretty(toast_size) AS toast_size,
    pg_size_pretty(index_size) AS index_size
FROM  table_sizes
ORDER BY
    total_relation_size DESC,
    relation_size DESC,
    toast_size DESC,
    index_size DESC;

Example output:

← Home