[InterMine Dev] Why don't subclasses use postgres table inheritance?

Sam Hokin shokin at ncgr.org
Fri Apr 8 15:57:34 BST 2016

Hrm, I'm not sure that explains it, since you're free to define indexes and foreign key constraints on the child tables just as we 
are now doing on the standalone tables (which makes sense, since the child tables often have additional fields). But perhaps that 
creates a complexity that I'm not seeing. Maybe the constraints and keys don't apply to the data pulled up from children? That would 
mess things up.

In any case, it's great that you're "looking under the hood" (or bonnet?) this summer. I'd vote for implementing table inheritance 
for subclasses being a top priority, since that will decrease the size of the database enormously. And, I think, improve 
performance, since the performance of inherited tables is something they continue to work on, I think, and smaller is usually 
better. Thanks!

On 04/08/2016 03:26 AM, Julie Sullivan wrote:
> Hi Sam,
> Yes, as Justin says, this design decision was made prior to us. We are assuming the InterMine team knew about this feature and
> decided not to use it!
> Looking at the docs, I see one problem:
> http://www.postgresql.org/docs/current/static/ddl-inherit.html
> "A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only
>  apply to single tables, not to their inheritance children."
> Being a data warehouse, indexes are our everything. In some cases, when querying we don't even go to the tables, we use the index
> only.
> https://wiki.postgresql.org/wiki/Index-only_scans
> Maybe that's why? Just a guess. However, there is a ray of hope....

More information about the dev mailing list