Re: pg_relation_size on partitioned table

On Fri, Mar 25, 2022 at 08:52:40PM +0800, Japin Li wrote:
> When I try to get total size of partition tables though partitioned table
> name using pg_relation_size(), it always returns zero. I can use the
> following SQL to get total size of partition tables, however, it is a bit
> complex.

This doesn't handle multiple levels of partitioning, as \dP+ already does.

Any new function should probably be usable by \dP+ (although it would also need
to support older server versions for another ~10 years).

> SELECT pg_size_pretty(sum(pg_relation_size(i.inhrelid)))
> FROM pg_class c JOIN pg_inherits i ON c.oid = i.inhparent
> WHERE relname = 'parent';

> Could we provide a function to get the total size of the partition table
> though the partitioned table name? Maybe we can extend
> the pg_relation_size() to get the total size of partition tables through
> the partitioned table name.

Sometimes people would want the size of the table itself and not the size of
its partitions, so it's not good to change pg_relation_size().

OTOH, pg_total_relation_size() shows a table size including toast and indexes.
Toast are an implementation detail, which is intended to be hidden from
application developers. And that's a goal for partitioning, too. So maybe it
would make sense if it showed the size of the table, toast, indexes, *and*
partitions (but not legacy inheritance children).

I know I'm not the only one who can't keep track of what all the existing
pg_*_size functions include, so adding more functions will also add some
additional confusion, unless, perhaps, it took arguments indicating what to
include, like pg_total_relation_size(partitions=>false, toast=>true,
indexes=>true, fork=>main).

--
Justin