Re: ToDo: show size of partitioned table

2018-06-20 9:44 GMT+02:00 Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>:

> On 2018/06/20 16:21, Pavel Stehule wrote:
> > 2018-06-20 7:44 GMT+02:00 Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>:
> >
> >> On 2018/06/02 0:15, Ashutosh Bapat wrote:
> >>> I think we should at least display "Type" as "partitioned table" for a
> >>> partitioned table, so that it's easy to understand why the size is 0;
> >>> partitioned tables do not hold any data by themselves.
> >>
> >> There was a long discussion last year (during PG 10 beta period), such
> as
> >> [1], and it seems most of us agreed to doing the above. Maybe, we
> should
> >> finally do it for PG 12, if not PG 11.
> >>
> >> Regarding showing the size of partitioned tables, there are many
> opinions
> >> and it's not clear if showing it in \dt itself is appropriate. For one,
> >> there is no pg_relation_size() or pg_table_size() equivalent in the
> >> backend for aggregating the size of all tables in a partition tree and I
> >> think people are not quite on board about having such a function in the
> >> backend [2].
> >
> > Now, the number of partitions can be low, but if the Postgres can better
> > process high number of partitions, then for some tables we can have
> > hundreds partitions.
> >
> > Then usual \dt can be not too much usable. The aggregation can be done on
> > client side. But maybe this idea is premature. Now, for PG 12, we can
> start
> > with
> >
> > \dtP+ command for showing partition tables only with aggregate size via
> all
> > related partitions.
> >
> > Is it acceptable idea?
>
> Do you mean \dt continues to show size 0 for partitioned tables, but with
> the new option (\dtP+) shows the actual size by aggregating across
> partitions? +1 to such a feature, but we need to agree on an acceptable
> implementation for that. How does the aggregation happen:
>

yes - my proposal is no change for \dt for now. I think so we will have to
change it, when partitioning will be more common and number of partitions
will be high. But it is not today.

\dtP shows only partitions tables (like \dtS shows only system tables),
with "+" shows sum of all related partitions.

> 1. In a new dedicated function in the backend (parallel to pg_table_size)?
>
> or
>
> 2. psql issues a separate query to compute the total size of a partition
> tree
>

In this moment we can simply do sum on client side, so it is related to @2.

> For option 2, I had posted a patch that simplifies writing such a query
> and posted that here:
>
> https://www.postgresql.org/message-id/7a9c5328-5328-52a3-
> 2a3d-bf1434b4dd1d%40lab.ntt.co.jp
>
> With that patch, the query to get the total size of a partition tree
> becomes as simple as:
>
> select sum(pg_table_size(p)) as size
> from pg_get_inheritance_tables('partitioned_table_name') p
>

good to know it. Thank you. Do you think so your patch should be included
to this feature or will be processed independently?

Regards

Pavel

> Thanks,
> Amit
>
>