Обсуждение: pg_relation_size on partitioned table

Hi, hackers

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.

    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.

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.

On 2022-Mar-25, Japin Li wrote:

> 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.

Does \dP+ do what you need?

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La espina, desde que nace, ya pincha" (Proverbio africano)

On Fri, Mar 25, 2022 at 6:23 PM Japin Li <japinli@hotmail.com> wrote:
>
> Hi, hackers
>
> 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.
>
>     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.

If we want to have it in the core, why can't it just be a function (in
system_functions.sql) something like below? Not everyone, would know
how to get partition relation size, especially whey they are not using
psql, they can't use the short forms that it provides.

CREATE OR REPLACE FUNCTION pg_partition_relation_size(regclass)
 RETURNS bigint
 LANGUAGE sql
 PARALLEL SAFE STRICT COST 1
BEGIN ATOMIC
     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 = '$1';
END;

Regards,
Bharath Rupireddy.

On Fri, 25 Mar 2022 at 20:59, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> On 2022-Mar-25, Japin Li wrote:
>
>> 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.
>
> Does \dP+ do what you need?

Thanks for your quick response!

I find the \dP+ use the following SQL:

    SELECT n.nspname as "Schema",
      c.relname as "Name",
      pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
      CASE c.relkind WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
      inh.inhparent::pg_catalog.regclass as "Parent name",
     c2.oid::pg_catalog.regclass as "Table",
      s.tps as "Total size",
      pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
    FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
         LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
         LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
         LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid,
         LATERAL (SELECT pg_catalog.pg_size_pretty(sum(
                     CASE WHEN ppt.isleaf AND ppt.level = 1
                          THEN pg_catalog.pg_table_size(ppt.relid) ELSE 0 END)) AS dps,
                         pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size(ppt.relid))) AS tps
                  FROM pg_catalog.pg_partition_tree(c.oid) ppt) s
    WHERE c.relkind IN ('p','I','')
      AND c.relname OPERATOR(pg_catalog.~) '^(parent)$' COLLATE pg_catalog.default
      AND pg_catalog.pg_table_is_visible(c.oid)
    ORDER BY "Schema", "Type" DESC, "Parent name" NULLS FIRST, "Name";


pg_table_size() includes "main", "vm", "fsm", "init" and "toast", however,
I only care about the "main" fork.

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.

On Fri, 25 Mar 2022 at 21:21, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:
> On Fri, Mar 25, 2022 at 6:23 PM Japin Li <japinli@hotmail.com> wrote:
>>
>> Hi, hackers
>>
>> 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.
>>
>>     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.
>
> If we want to have it in the core, why can't it just be a function (in
> system_functions.sql) something like below? Not everyone, would know
> how to get partition relation size, especially whey they are not using
> psql, they can't use the short forms that it provides.
>
> CREATE OR REPLACE FUNCTION pg_partition_relation_size(regclass)
>  RETURNS bigint
>  LANGUAGE sql
>  PARALLEL SAFE STRICT COST 1
> BEGIN ATOMIC
>      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 = '$1';
> END;
>

Yeah, it's a good idea!  How about add a fork parameter?

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.

On Fri, 25 Mar 2022 at 21:21, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:
> On Fri, Mar 25, 2022 at 6:23 PM Japin Li <japinli@hotmail.com> wrote:
>>
>> Hi, hackers
>>
>> 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.
>>
>>     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.
>
> If we want to have it in the core, why can't it just be a function (in
> system_functions.sql) something like below? Not everyone, would know
> how to get partition relation size, especially whey they are not using
> psql, they can't use the short forms that it provides.
>
> CREATE OR REPLACE FUNCTION pg_partition_relation_size(regclass)
>  RETURNS bigint
>  LANGUAGE sql
>  PARALLEL SAFE STRICT COST 1
> BEGIN ATOMIC
>      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 = '$1';
> END;
>

I add two functions (as suggested by Bharath Rupireddy)
pg_partition_relation_size and pg_partition_table_size to get partition tables
size through partitioned table name.  It may reduce the complexity to get the
size of partition tables.

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.

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

On Fri, Mar 25, 2022 at 08:52:40PM +0800, Japin Li wrote:
> 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.

There are already many replies on this thread, but nobody has
mentioned pg_partition_tree() yet, so here you go.  You could use that
in combination with pg_relation_size() to get the whole size of a tree
depending on your needs.
--
Michael

On Sat, Mar 26, 2022 at 11:35 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Fri, Mar 25, 2022 at 08:52:40PM +0800, Japin Li wrote:
> > 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.
>
> There are already many replies on this thread, but nobody has
> mentioned pg_partition_tree() yet, so here you go.  You could use that
> in combination with pg_relation_size() to get the whole size of a tree
> depending on your needs.

Yeah. The docs have a note on using it for finding partitioned table size:

   <para>
    For example, to check the total size of the data contained in a
    partitioned table <structname>measurement</structname>, one could use the
    following query:
<programlisting>
SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
  FROM pg_partition_tree('measurement');
</programlisting>
   </para>

Regards,
Bharath Rupireddy.

On Sat, 26 Mar 2022 at 22:16, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:
> On Sat, Mar 26, 2022 at 11:35 AM Michael Paquier <michael@paquier.xyz> wrote:
>>
>> On Fri, Mar 25, 2022 at 08:52:40PM +0800, Japin Li wrote:
>> > 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.
>>
>> There are already many replies on this thread, but nobody has
>> mentioned pg_partition_tree() yet, so here you go.  You could use that
>> in combination with pg_relation_size() to get the whole size of a tree
>> depending on your needs.
>
> Yeah. The docs have a note on using it for finding partitioned table size:
>
>    <para>
>     For example, to check the total size of the data contained in a
>     partitioned table <structname>measurement</structname>, one could use the
>     following query:
> <programlisting>
> SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
>   FROM pg_partition_tree('measurement');
> </programlisting>
>    </para>
>

Thanks for all of you!  The above code does what I want.

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.