Any reason not to use inheritance?

[jao]

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Thomas F.O'Connell]

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, ja*@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[jao]

Quoting "Thomas F.O'Connell" <tf*@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

[David Helgason]

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, ja*@geophile.com wrote:

Quoting "Thomas F.O'Connell" <tf*@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 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Thomas F.O'Connell]

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, ja*@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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[David Garamond]

ja*@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly