Re: pg_relation_size on partitioned table - Mailing list pgsql-hackers

From Justin Pryzby
Subject Re: pg_relation_size on partitioned table
Date
Msg-id 20220325152742.GE28503@telsasoft.com
Whole thread Raw
In response to pg_relation_size on partitioned table  (Japin Li <japinli@hotmail.com>)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: automatically generating node support functions
Next
From: Alvaro Herrera
Date:
Subject: Re: support for MERGE