Get table size of partitioned table (Postgres 10+)

Question

I came across this query on Postgres weekly which shows tables, their sizes, toast sizes and index sizes in bytes:

SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS total,
  pg_size_pretty(pg_relation_size(relid)) AS internal,
  pg_size_pretty(pg_table_size(relid) - pg_relation_size(relid)) AS external,
  pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

I know that Postgres is creating a table for each partition so I am getting entries for each partition separately, but is there a way to get one row per table, regardless of whether this table is partitioned or not?

Answer 1

Going by instructions from @Laurenz Albe I created a query that satisfies my needs. This will get total memory for all partitioned tables from specific database.

SELECT
   pi.inhparent::regclass AS parent_table_name, 
   pg_size_pretty(sum(pg_total_relation_size(psu.relid))) AS total,
   pg_size_pretty(sum(pg_relation_size(psu.relid))) AS internal,
   pg_size_pretty(sum(pg_table_size(psu.relid) - pg_relation_size(psu.relid))) AS external, -- toast
   pg_size_pretty(sum(pg_indexes_size(psu.relid))) AS indexes
FROM pg_catalog.pg_statio_user_tables psu
   JOIN pg_class pc ON psu.relname = pc.relname
   JOIN pg_database pd ON pc.relowner = pd.datdba
   JOIN pg_inherits pi ON pi.inhrelid = pc.oid
WHERE pd.datname = :database_name
GROUP BY pi.inhparent
ORDER BY sum(pg_total_relation_size(psu.relid)) DESC;

Note that in the case when we have partitions of partitions, this will not have one row for the root table, but every parent table will have it's own row

Answer 2

This gives table size per parent table even if we have multiple partition levels:

WITH RECURSIVE tables AS (
  SELECT
    c.oid AS parent,
    c.oid AS relid,
    1     AS level
  FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_inherits AS i ON c.oid = i.inhrelid
    -- p = partitioned table, r = normal table
  WHERE c.relkind IN ('p', 'r')
    -- not having a parent table -> we only get the partition heads
    AND i.inhrelid IS NULL
  UNION ALL
  SELECT
    p.parent         AS parent,
    c.oid            AS relid,
    p.level + 1      AS level
  FROM tables AS p
  LEFT JOIN pg_catalog.pg_inherits AS i ON p.relid = i.inhparent
  LEFT JOIN pg_catalog.pg_class AS c ON c.oid = i.inhrelid AND c.relispartition
  WHERE c.oid IS NOT NULL
)
SELECT
  parent ::REGCLASS                                  AS table_name,
  array_agg(relid :: REGCLASS)                       AS all_partitions,
  pg_size_pretty(sum(pg_total_relation_size(relid))) AS pretty_total_size,
  sum(pg_total_relation_size(relid))                 AS total_size
FROM tables
GROUP BY parent
ORDER BY sum(pg_total_relation_size(relid)) DESC

Answer 3

Sure, but you'd have to join with a bunch of other catalog tables and use GROUP BY.

• Attribute relkind from he catalog pg_class will tell you if a relation is partitioned (p) or not (r).

• The catalog pg_inherits will tell you which partition (inhrelid) belongs to which partitioned table (inhparent).

Since partitions can be partitioned again, you will have to write a recursive common table expression if you want to cover all bases.