Re: MySQL versus Postgres

On 09/08/10 14:54, Sandeep Srinivasa wrote:

>
> The way I see it - for those who want to truly learn, there is the
> documentation. For those who dont, there are ORMs.

Ha, I wish!

Despite being rather comfortable with SQL I've been using the Hibernate
ORM system in a project to try to reduce some of the repetitive coding,
while falling back to JDBC and hand-coded SQL where Hibernate doesn't do
a great job. This is my first venture into ORM-land, and may well be my
last.

The amount of learning required in getting the ORM to behave even
vaguely sanely in anything but trivial situations is vastly greater than
what's required to use plain SQL. I'm not at all sure it's worth it for
anything but the hugest projects, as I've wasted way more time battling
Hibernate than I would've done writing all the repetitive template
classes and SQL mappings myself. All the lazy-loading stuff is useless
in practice, because you're always working with detached entities by the
time you need it, so it doesn't help with the problem of figuring out
what data your app is going to need well before it asks for it.

Additionally, ORM system authors seem to consider the database to be
getting uppity and above its place if it's used for anything much more
than a dumb row store. Basic database features like referential
integrity constraints (especially things like ON DELETE CASCADE),
in-database triggers, column privileges, etc tend to confuse it
mightily, because it assumes it'll be the only thing making changes to
the database. Hibernate is better than most in this regard, and way
better than things like ActiveRecord (from Ruby on Rails) in that it
understands most basic database features and can be told not to cache
between sessions, but it still gets frustrating as soon as you try to do
things like use "weird" data types like the native "xml" type in Pg,
(for which I had to write a custom UserType mapping).

You certainly do need to have a decent understanding of basic SQL to use
an ORM reasonably efficiently, including the trade-offs of joins vs
subqueries, how queries can be rewritten/replanned by the database, the
cost of vast numbers of repeated small queries vs large-and-expensive
one-off multi-way joins that return repetitive information, the effect
of latency and planning time, the difference between prepared and
one-off statements, parameter placement, etc. That said, there are large
sections of the SQL language that most ORMs appear to never go anywhere
near. You won't be using window functions and custom aggregates in any ORM.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/