Thread: Any reason not to use inheritance?
My company's product uses Postgres 7.4.3. Postgres is working well for us, and we've worked through many performance issues by tweaking the schema, indexes, and posgresql.conf settings. Inheritance would be useful for our application, but we did not use this feature initially. We're about to revise part of our application, and this would be a good time to introduce inheritance -- it's a good fit for our data model, and it would greatly simplify some of our upcoming work. I'd really like to use inheritance, but not if I'm asking for trouble. The question is whether there are any performance-related surprises lurking. Our use of inheritance would be very simple: - The columns that would be inherited are not involved in any primary or foreign keys. - One of the columns would be used in indexes on some of the child tables. In these cases, the index would combine an inherited column with a column declared in an inheriting table. - Queries will often restrict the inherited indexed column. - We'll occasionally ORDER BY inherited columns. Any reason not to use inheritance? Jack Orenstein ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
Just beware that inheritance is not a complete methodology in postgres. The implementation has limitations. For instance, indexes in parent tables will not be inherited by children. I've yet to encounter a scenario that actually required inheritance. I've used it a few times, but when I have, the end result always required a bit more work because of the incomplete aspects of the postgres implementation (namely constraints and indexes). -tfo On Sep 20, 2004, at 4:03 PM, jao@geophile.com wrote: > My company's product uses Postgres 7.4.3. Postgres is working well for > us, and we've worked through many performance issues by tweaking the > schema, indexes, and posgresql.conf settings. > > Inheritance would be useful for our application, but we did not use > this feature initially. We're about to revise part of our application, > and this would be a good time to introduce inheritance -- it's a good > fit for our data model, and it would greatly simplify some of our > upcoming work. > > I'd really like to use inheritance, but not if I'm asking for trouble. > The question is whether there are any performance-related surprises > lurking. Our use of inheritance would be very simple: > > - The columns that would be inherited are not involved in any primary > or foreign keys. > > - One of the columns would be used in indexes on some of the child > tables. In these cases, the index would combine an inherited column > with a column declared in an inheriting table. > > - Queries will often restrict the inherited indexed column. > > - We'll occasionally ORDER BY inherited columns. > > Any reason not to use inheritance? > > Jack Orenstein
Quoting "Thomas F.O'Connell" <tfo@sitening.com>: > Just beware that inheritance is not a complete methodology in postgres. > The implementation has limitations. For instance, indexes in parent > tables will not be inherited by children. > > I've yet to encounter a scenario that actually required inheritance. > I've used it a few times, but when I have, the end result always > required a bit more work because of the incomplete aspects of the > postgres implementation (namely constraints and indexes). Could you expand on this? All indexes and constraints would be declared on the child tables. Nearly all table reference would be to the child tables. (So why use inheritance at all? If for no other reason, all the common columns would be declared in one place.) Jack Orenstein ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
I thought that this would be useful to this mailing list:
Our company uses Polyhedra in memory database for storing our object model. We have roughly around 250 C++ classes
thatneed to be persisted. The object model is very complicated involving inheritance hierarchies, associations and
aggregations(all kinds of relations that are defined using UML). Polyhedra does not confirm to SQL95 standards but, it
hasgood support for inheritance.
Some time ago I was working on to replace Polyhedra in our product with PostgreSQL. Though PostgreSQL does not support
automaticallypropagating constraints, uniqueness of indexes etc to derived tables, I could achieve same functionality
bysetting up alter table statements and triggers.
It will be definitely good if Polyhedra directly supports inheriting constraints. It will make mapping the C++/Java
objectsmodels into PostgreSQL schema much easier.
Regards,
Sagar.
-----Original Message-----
From: jao@geophile.com [mailto:jao@geophile.com]
Sent: Monday, September 20, 2004 3:16 PM
To: Thomas F.O'Connell
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Any reason not to use inheritance?
Quoting "Thomas F.O'Connell" <tfo@sitening.com>:
> Just beware that inheritance is not a complete methodology in postgres.
> The implementation has limitations. For instance, indexes in parent
> tables will not be inherited by children.
>
> I've yet to encounter a scenario that actually required inheritance.
> I've used it a few times, but when I have, the end result always
> required a bit more work because of the incomplete aspects of the
> postgres implementation (namely constraints and indexes).
Could you expand on this?
All indexes and constraints would be declared on the child tables.
Nearly all table reference would be to the child tables. (So why use
inheritance at all? If for no other reason, all the common
columns would be declared in one place.)
Jack Orenstein
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Please replace Polyhedra with PostgreSQL in the last paragraph of my previous mail. Sorry for the inconvenience.
Sagar.
-----Original Message-----
From: Vidyasagara Guntaka
Sent: Monday, September 20, 2004 3:44 PM
To: jao@geophile.com; Thomas F.O'Connell
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Any reason not to use inheritance?
I thought that this would be useful to this mailing list:
Our company uses Polyhedra in memory database for storing our object model. We have roughly around 250 C++ classes
thatneed to be persisted. The object model is very complicated involving inheritance hierarchies, associations and
aggregations(all kinds of relations that are defined using UML). Polyhedra does not confirm to SQL95 standards but, it
hasgood support for inheritance.
Some time ago I was working on to replace Polyhedra in our product with PostgreSQL. Though PostgreSQL does not support
automaticallypropagating constraints, uniqueness of indexes etc to derived tables, I could achieve same functionality
bysetting up alter table statements and triggers.
It will be definitely good if Polyhedra directly supports inheriting constraints. It will make mapping the C++/Java
objectsmodels into PostgreSQL schema much easier.
Regards,
Sagar.
-----Original Message-----
From: jao@geophile.com [mailto:jao@geophile.com]
Sent: Monday, September 20, 2004 3:16 PM
To: Thomas F.O'Connell
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Any reason not to use inheritance?
Quoting "Thomas F.O'Connell" <tfo@sitening.com>:
> Just beware that inheritance is not a complete methodology in postgres.
> The implementation has limitations. For instance, indexes in parent
> tables will not be inherited by children.
>
> I've yet to encounter a scenario that actually required inheritance.
> I've used it a few times, but when I have, the end result always
> required a bit more work because of the incomplete aspects of the
> postgres implementation (namely constraints and indexes).
Could you expand on this?
All indexes and constraints would be declared on the child tables.
Nearly all table reference would be to the child tables. (So why use
inheritance at all? If for no other reason, all the common
columns would be declared in one place.)
Jack Orenstein
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Unique columns in the parent tables can't be guaranteed except with triggers. Since I like my data models to be unable to go incoherent (who doesn't) you end up putting triggers on those tables which is paying performance & implementation complexity for something that just works with normal tables. I was also lured into using inheritance since it looked so logical. But I went back to a master table + separate tables instead of children. Unless you have a compelling reason I'd stay away. David Helgason, Over the Edge (http://otee.dk), making great game technology On 21. sep 2004, at 00:16, jao@geophile.com wrote: > Quoting "Thomas F.O'Connell" <tfo@sitening.com>: > >> Just beware that inheritance is not a complete methodology in >> postgres. >> The implementation has limitations. For instance, indexes in parent >> tables will not be inherited by children. >> >> I've yet to encounter a scenario that actually required inheritance. >> I've used it a few times, but when I have, the end result always >> required a bit more work because of the incomplete aspects of the >> postgres implementation (namely constraints and indexes). > > Could you expand on this? > > All indexes and constraints would be declared on the child tables. > Nearly all table reference would be to the child tables. (So why use > inheritance at all? If for no other reason, all the common > columns would be declared in one place.) > > Jack Orenstein > > ---------------------------------------------------------------- > This message was sent using IMP, the Internet Messaging Program. > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match >
If the scope of your inheritance (as in, number of children you expect per parent) is reasonably limited, this can be convenient. But if the number of children grows large, and you discover that you need indexing or constraints, you will need to develop triggers that can manage everything and generally spend a bit more time on manual intervention than if you relied on a normalized data model. -tfo On Sep 20, 2004, at 5:16 PM, jao@geophile.com wrote: >> I've yet to encounter a scenario that actually required inheritance. >> I've used it a few times, but when I have, the end result always >> required a bit more work because of the incomplete aspects of the >> postgres implementation (namely constraints and indexes). > > Could you expand on this? > > All indexes and constraints would be declared on the child tables. > Nearly all table reference would be to the child tables. (So why use > inheritance at all? If for no other reason, all the common > columns would be declared in one place.) > > Jack Orenstein > > ---------------------------------------------------------------- > This message was sent using IMP, the Internet Messaging Program.
jao@geophile.com wrote: > Any reason not to use inheritance? Inheritance is certainly very useful/convenient especially for "table partitioning" (though not the same as in Oracle) and for modeling supertype-subtype stuffs. The only drawback I see is that the implementation is still lacking in several area, e.g. no hierarchy-wide unique constraints, so you'll have to resort to triggers. And there doesn't seem to be many people using it at the moment, so if there were a bug then it might get fixed less quickly. Ironically, the last sentence could be argued as the reason *to* use inheritance in Postgres, to get more people using and testing it. :-) -- dave