postgresqlpostgresql-10postgresql-11postgres-12

Get table size of partitioned table (Postgres 10+)

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?

Solution

  • 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