Re: Postgres vr.s MySQL- style differences?

[Jim Nasby <decibel(at)decibel(dot)org>]

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)