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.
Attachments:
add-system-functions-to-get-partition-size.patchtext/x-patchDownload
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 81bac6f581..81cab4c21c 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -281,6 +281,32 @@ CREATE OR REPLACE FUNCTION pg_relation_size(regclass)
PARALLEL SAFE STRICT COST 1
RETURN pg_relation_size($1, 'main');
+CREATE OR REPLACE FUNCTION pg_partition_relation_size(regclass, text default 'main')
+RETURNS bigint
+LANGUAGE sql
+PARALLEL SAFE STRICT COST 1
+BEGIN ATOMIC
+ SELECT
+ sum(pg_relation_size(i.inhrelid, $2))
+ FROM
+ pg_class c JOIN pg_inherits i ON c.oid = i.inhparent
+ WHERE
+ oid = $1;
+END;
+
+CREATE OR REPLACE FUNCTION pg_partition_table_size(regclass)
+RETURNS bigint
+LANGUAGE sql
+PARALLEL SAFE STRICT COST 1
+BEGIN ATOMIC
+ SELECT
+ sum(pg_table_size(i.inhrelid))
+ FROM
+ pg_class c JOIN pg_inherits i ON c.oid = i.inhparent
+ WHERE
+ oid = $1;
+END;
+
CREATE OR REPLACE FUNCTION obj_description(oid, name)
RETURNS text
LANGUAGE sql
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.