Re: Postgres vr.s MySQL- style differences?

On May 28, 2007, at 2:37 PM, Lukas Kahwe Smith wrote:
> Andrew Sullivan wrote:
>> This is why many applications that have some portability layer for
>> the database seem to be so awful at using the database: they can't
>> use any of the strengths of any of the underlying databases, because
>> those strengths are usually the thing that some other database is
>> really bad at. So you often end up with an application that isn't
>> truly stellar at anything, and your database system has to be
>> oversized to perform acceptably. (Note that there's nothing
>> intrinisically wrong with this approach; you've just optimised your
>> code for portability at the expense of performance.)
>
> Yup, the tend to heavily rely on data integrity management on the
> middle tier. Which by the way is not totally crazy, because you
> need most of the data integrity rules on the frontend anyways, to
> generate proper GUI's. Actually in non object-relational RDBMS
> (which are the norm, PostgreSQL is special), you can never do your
> full integrity management inside the database.

Can you give an example of that, because I can't think of one, unless
you're talking about integrity that has to lie outside the database
(ie: if you're storing filesystem locations in the database).

> Which is all kind of ok, as long as you never mess with the
> database through anything but your single middle tier implementation.

I do a lot of work on systems where it would be impractical or
impossible to do everything through middleware. The other issue is
that you have to be very, very careful how you do integrity checking
in the middleware if you want to avoid things like race conditions.
The database not only gives that to you for free, but it's also
extremely efficient at it.

> My point is, there are solid reasons for writing applications that
> use the database as a mostly stupid data store with very efficient
> retrieval and these applications tend to also perform pretty well
> regardless of what RDBMS you stick underneath.

Perhaps if you carefully hand-code your middleware... my experience
is that things like Hibernate tend to destroy performance because
unless you really, really know it well you end up with some pretty
bad database access methods. It doesn't have to be that way, but it
often is.

Put another way, if a database is a foot-gun when it comes to
performance, middleware is a foot-bazooka. :)
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)