Re: inheritance vs performance - Mailing list pgsql-general

Hi Pascal,

As other answers to this topic pointed out, it's kind of pointless to
use more tables with the same structure. In the long run it will become
a PITA to manage them, I'm talking from experience here. In our company
we adopted a solution with dynamically created tables (with dynamic
schema), thinking it would be more performant (which actually might be
true in our case). The alternative would have been some kind of generic
"param_name", "param_value" table, holding all the data from all these
dynamic tables. While performance might have been gained using the
dynamic tables, a lot of flexibility was lost, and a maintainance
nightmare was created (just think about migrating all those tables
between versions of the system). Not to mention that you can't easily
create queries which have as parameter a table name... (actually you
can, but I think it's not really recommended). In our case however the
schema is different for all those tables, so it makes sense in a way,
but from maintainance POV I wouldn't chose again dynamic tables, they
are more trouble than worth.

Anyway, I think you would be better off by adding an additional column
to your B tables which holds the "table name" the C tables would have
had. In fact that would be an ID of some sort for efficiency reasons
(and I bet you already have those IDs there ;-). Then you can select the
content of a C table based on those IDs, and have a lot more
flexibility. Performance wise I think the one table solution is actually
better, but that's just a guess from my part.

And also reconsider using separate B tables if that means they are
dynamically created... or be prepared for some hard times later with
maintainance ;-)

Just my 2c,
Csaba.


On Fri, 2004-02-13 at 10:01, Pascal Polleunus wrote:
> Hi,
>
> I'm wondering if there could be problems related to inheritance in the
> following scenario (with PostgreSQL 7.4.1)...
>
> 1 A-table, abstract.
>
> Max 10 B-tables that inherit from A, with sometimes some more columns
> than A. These are also abstracts.
>
> "n" C-tables that inherit from 1 B-table, without more columns.
> Each C-table could contain quite a lot of rows (500K, 1M, ...).
>
> Could there be problems, or performance issues, related to inheritance
> if there is "too much" C-tables (in combination with the number of
> rows)? And what would be that "too much"?
>
> Remarks:
> A-table could be removed as it's not that important/relevant.
> The purpose of this structure is not to be able to easily select through
> the parent in all children tables, though it would be appreciated.
> The purpose of this is just to be able to easily create C-tables, and
> maybe also to easily handle structure changes of A or B-tables.
> The master words here are "performance" and "reliability".
>
>
> Thanks,
> Pascal
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html