Re: [HACKERS] Re: [SQL] Proposed Changes to PostgreSQL - Mailing list pgsql-sql

Marten Feldtmann wrote:

>  Actually I'm a little bit uncertain what ORDBMS really improves ? After
> writing a full mapper and wrapper for PostgreSQL and a Smalltalk dialect
> I see really no usage for these additional inheritance features databases
> like PostgreSQL offer.
> 
>  Some points about this:
> 
>   - all these additional features are very specific to PostgreSQL and
>     are not compatible with other databases. Writing an application
>     based on these features results in non-portable systems.

Not true, because if the wrapper conforms to the ODMG standard, it will 
be compatible with ObjectStore, Versant, the new Sun RDBS standard,
Gemstone, and many others.

>   - Speed is still a very, very important feature for a database. A
>     single query, which uses about 5 seconds because the optimizer
>     is not very clever to use several indices to improove the
>     query execution is much more worse and can change the structure
>     of the whole application program.

The biggest thing you can do for speed is to have less objects/tuples
in the database. Inheritance and the array feature of postgresql
can improve things here by orders of magnitude. The problem is that
these
two features are not viable to use at present. With an ODMG interface,
and TOAST to allow tuples of unlimited size this will then be a viable
feature. In some situations this will improve queries by 100x even
with the most brain-dead optimizer. ODBMS doesn't care a great deal
about wonderful optimizers because joins are less necessary.

>   - when creating automatic sql-queries through a mapper one can get
>     very complicated sql queries which tests the parser very hard and
>     the limits of PostgreSQL has been seen very quickly during
>     the development of the wrapper above.

Exactly, so stop mapping things and creating complicated joins. ODBMSes
do not do ANY joins to re-create objects. That's why mappers suck so
hard.

>   What I'm missing from these new database are structural changes to
> the query system: the possibility to execute complicated
> concatenated queries on the server .. perhaps with different
> parameters.

What is a concatenated query? 

I'm all in favour of more powerful queries, but that is not what this
proposal is about. This is about AVOIDING queries. Mappers and so forth
are great query generators because the database representation is
different from the in-memory object representation. This proposal
is all about making the in-memory object representation the same
as in the database.

If you still don't get it take an example..

class CarPart {int volume;
}
class Wheel : CarPart {int diameter;
}
class SteeringWheel : Wheel {      boolean horn;
}
class RoadWheel : Wheel {      int airpressure;
}
class Car {   List<CarPart> parts;
}

Now with an ODBMS, a Car with 4 wheels and a steering wheel we'll have 6
objects in the database - 1 Car, 4 RoadWheels and 1 SteeringWheel. With
a relational mapper, depending on how you map it you'll have 21 objects
- 5 CarPart objects, 5 wheel objects, 4 road wheel, 1 steering wheel, 1
car and 5 car_carpart relation entities. And when you join it all
together you'll have to join against 6 tables instead of 3.