Re: pg_(total_)relation_size and partitioned tables

On 12/28/17 16:24, David Rowley wrote:
>> select pg_partition_root(c.oid), c.relname, pg_table_size(c.oid)
>> from pg_class c
>> order by 1
>>
>> select pg_partition_root(c.oid), sum(pg_table_size(c.oid))
>> from pg_class c
>> group by 1
>
> That seems much nicer. I assume "root" would mean the top level
> partitioned table. If so, would we also want
> pg_partition_parent(regclass)? Or maybe something to control the
> number of "levels-up" the function would run for. If we had that then
> maybe -1 could mean "go until you find a table with no parent".

Hmm, we need to think through some scenarios for what one would really
want to do with this functionality.

Clearly, the existing behavior is useful for management tasks like bloat
and vacuum monitoring.

And on the other hand you might want to have a logical view of, how big
is this partitioned table altogether.

But what are the uses for dealing with partial partition hierarchies?
How easy do we need to make that?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services