Thread: Any reason not to use inheritance?

Any reason not to use inheritance?

From
jao@geophile.com
Date:
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.

Re: Any reason not to use inheritance?

From
Thomas F.O'Connell
Date:
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


Re: Any reason not to use inheritance?

From
jao@geophile.com
Date:
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.

Re: Any reason not to use inheritance?

From
"Vidyasagara Guntaka"
Date:
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

Re: Any reason not to use inheritance?

From
"Vidyasagara Guntaka"
Date:
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

Re: Any reason not to use inheritance?

From
David Helgason
Date:
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
>


Re: Any reason not to use inheritance?

From
Thomas F.O'Connell
Date:
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.


Re: Any reason not to use inheritance?

From
David Garamond
Date:
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