Why a partitioned table size is larger than a normal table size with the same data

Question

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?

Answer 1

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.