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?

score 18 · Accepted Answer · answered Mar 01 '19 at 11:31

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

i actually just needed one row for each partition. replace `pi.inhparent` in group by and `pi.inhparent::regclass AS parent_table_name` in select with `psu.relname` for the win. — WEBjuju, Oct 05 '20 at 21:24

score 7 · Answer 2 · answered Dec 10 '19 at 12:11

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

score 2 · Answer 3 · answered Feb 28 '19 at 08:22

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.

Get table size of partitioned table (Postgres 10+)

3 Answers3

Linked

Related