Published on Wed, Apr 27, 2011
In PostgreSQL, every table is an object, using pg_relation_size(‘object_name’) will give the size of the object. If you send the partition table in the place of ‘object_name’, it gives only that object size but not the sizes of child tables.
Check out the example given below.
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------------+-------+----------+------------+-------------
public | child1 | table | postgres | 8192 bytes |
public | child2 | table | postgres | 8192 bytes |
public | parent | table | postgres | 0 bytes |
(3 rows)
pg_relation_size() on parent table will not give the exact size.
postgres=# select pg_size_pretty(pg_relation_size('parent'));
pg_size_pretty
----------------
0 bytes
(1 row)
To achieve partition table size, firstly know the concerned child tables and its sizes. Using pg_inherits catalog table will help in getting the information of child tables with sizes and later sum them for exact size. I have tried writing a small function using pg_inherits to get it done.
CREATE OR REPLACE FUNCTION pg_partition_table_size(text) returns numeric as
$$
select sum(to_number(pg_size_pretty(pg_relation_size(inhrelid::regclass)),'999999999')) from pg_inherits where inhparent=$1::regclass;
$$ language sql;
Now, send the partition table to the function.
postgres=# select pg_partition_table_size('parent');
pg_partition_table_size
-------------------------
16384
(1 row)
Is it not useful. Do post your comments, they will be greatly appreciated.
–Raghav