Wednesday, April 27, 2011

Size of Partition Table in PostgreSQL 9.0

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

4 comments :

Anonymous said...

Good stuff, thanks

Anonymous said...

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.

Japan Shah said...

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...

Anonymous said...

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;

Post a Comment

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License