This text is based on my experience with PG 8.4 and 9.3.

Here are some useful selects:

Show row count pro every child table:

select c.relname, count(*)
from schemaname.parent_table m
join pg_class c
on m.tableoid=c.oid
group by c.relname

or this “nerd-stuff” query – which gives the same result only using CAST:

select tableoid::regclass, count(*)
from schemaname.parent_table m
group by tableoid
order by 1

Show all parent-child tables in database:


select p.relname as parent_table, ch.relname as child_table, i.inhseqno
from pg_inherits i
join pg_class p
on i.inhparent = p.oid
join pg_class ch
on i.inhrelid = ch.oid
order by 1, 2

 

And here some experiences with table inheritance / partitioning in PostgreSQL:

  1. Problem with locks when using pg_bulkload to load into child tables:If you select data using query into parent table then all child tables are locked with “access shared lock” which can cause problems with pg_bulkload loading data into some child table.
    Because pg_bulkload uses “access exclusive lock” for table because it does also reindexing.
    So typical problem in such a case is that pg_bulkload waits for lock on child table until running selects are done.
    If there are some new queries launched after pg_bulkload requests exclusive lock they also have to wait. And they can continue only after pg_bulkload finishes its job and releases all locks.
    Only help in such a case is to use COPY command. It is maybe a little slower but does not need exclusive lock on the whole table.
    So in reality using COPY can give you better responses than “quicker” pg_bulkload