Size of Partition Table in PostgreSQL 9.0

Posted on by Raghav

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

10 Replies to “Size of Partition Table in PostgreSQL 9.0”

  1. Very nice indeed, but how do we know whether the returned value is KB, MB, GB, ??? The to_number conversion lops off the factor from pg_size_pretty. I&#39ve tried to preserve it, but cannot.

  2. CREATE OR REPLACE FUNCTION pg_partition_table_size(text) returns text as $$ select pg_size_pretty(sum(pg_relation_size(inhrelid))::bigint) from pg_inherits where inhparent=$1::regclass; $$ language sql; Bit modified version of the same, which actually shows unit GB/MB/KB…

  3. This doesn&#39t consider parent table size right? In case some records are there in parent table ! So slightly changed as followingCREATE OR REPLACE FUNCTION pg_partition_table_size(text) returns text as$$select pg_size_pretty(sum(pg_relation_size(inhrelid))::bigint + pg_relation_size($1)) from pg_inherits where inhparent=$1::regclass;$$ language sql;

  7. This doesn't consider parent table size right? In case some records are there in parent table ! So slightly changed as following

    CREATE OR REPLACE FUNCTION pg_partition_table_size(text) returns text as
    $$
    select pg_size_pretty(sum(pg_relation_size(inhrelid))::bigint + pg_relation_size($1)) from pg_inherits where inhparent=$1::regclass;
    $$ language sql;

  8. CREATE OR REPLACE FUNCTION pg_partition_table_size(text) returns text as
    $$
    select pg_size_pretty(sum(pg_relation_size(inhrelid))::bigint) from pg_inherits where inhparent=$1::regclass;
    $$ language sql;

    Bit modified version of the same, which actually shows unit GB/MB/KB…

  9. Very nice indeed, but how do we know whether the returned value is KB, MB, GB, ??? The to_number conversion lops off the factor from pg_size_pretty. I've tried to preserve it, but cannot.

Comments are closed.