0

I insert the same data into two tables: one is partitioned, the other is normal. I use the following command to determine the size of the normal table:

select pg_size_pretty (pg_total_relation_size ('test_normal'))

The output is: 6512 MB

I use the following command to determine the size of the partitioned table:

select sum(to_number(pg_size_pretty(pg_total_relation_size(inhrelid::regclass)),'999999999')) from pg_inherits where inhparent = 'test_partition'::regclass

The output is: 6712.1 MB

The partitioned table has 1001 partitions. Both tables have no indexes or constrains.

Why there is that big difference (200 MB) between the two tables if the size of an empty partition is 0 bytes?

Emissa
  • 15
  • 4

2 Answers2

2

The main problem is that you call to_number(pg_size_pretty(...),'999999999'). pg_size_pretty returns values like 123 MB or 123 GB, both of which will become 123, so the sum is wrong. And even if all units happen to be the same, you will still get rounding error.

So call to_number after summing up the sizes.

There might of course still be a small discrepancy between the unpartitioned table and the partitioned tables.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

Thanks a lot Laurenz

I searched for another method to determine the size of the partitioned table and i found this post : Get table size of partitioned table (Postgres 10+)

so i try this:

WITH RECURSIVE tables AS (
 SELECT
   c.oid AS parent,
   c.oid AS relid,
   1     AS level
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_inherits AS i ON c.oid = i.inhrelid
   -- p = partitioned table, r = normal table
 WHERE c.relkind IN ('p')
   -- not having a parent table -> we only get the partition heads
   AND i.inhrelid IS NULL
 UNION ALL
 SELECT
   p.parent         AS parent,
   c.oid            AS relid,
   p.level + 1      AS level
 FROM tables AS p
 LEFT JOIN pg_catalog.pg_inherits AS i ON p.relid = i.inhparent
 LEFT JOIN pg_catalog.pg_class AS c ON c.oid = i.inhrelid AND c.relispartition
 WHERE c.oid IS NOT NULL
)
SELECT
 parent ::REGCLASS                                  AS table_name,
 pg_size_pretty(sum(pg_total_relation_size(relid))) AS pretty_total_size,
 sum(pg_total_relation_size(relid))                 AS total_size
FROM tables
GROUP BY parent
ORDER BY sum(pg_total_relation_size(relid)) DESC

The result makes more sense now!

Emissa
  • 15
  • 4
  • 1
    If you are on Postgres 12 or later, you can simplify that to `select sum(pg_total_relation_size(relid)) from pg_partition_tree('your_table'::regclass)` –  Feb 04 '22 at 15:11