Re: "Too far out of the mainstream"

On Wed, Sep 5, 2012 at 7:56 PM, Edson Richter <edsonrichter(at)hotmail(dot)com>wrote:

> Em 05/09/2012 23:49, Chris Travers escreveu:
>
> Regarding MySQL vs PostgreSQL:
>>
>> MySQL is what you get when app developers build a database server.
>> PostgreSQL is what you get when db developers build a development
>> platform.
>>
>> There really isn't anything more to say about it.
>>
>
> This kind of claim is just to feed flame wars. Don't think I need to state
> that a "db developer" becomes a "app developer" as soon as he start to
> develop any database server code, right?
>

I don't mean it that way.

The basic thing is that MySQL's view of data integrity is extremely
application centric. Even today, applications get to tell the server
whether to throw an error when you try to insert 0000-00-00 into a date
field (this is via the sql_mode setting and admins can't restrict what an
app can do there). MySQL makes perfect sense when you are an application
developer looking at the database as a place to store information for your
own private use. In essence, MySQL makes perfect sense when you realize
that "my" = "private" in OO terms.

This isn't necessarily a bad thing if that's what you are using it for, and
because of ways the db market has developed there are a huge number of
developers who are very happy with a lowest common denominator RDBMS where
you can assume one app writing to the db (or at least any given relation),
and possibly other apps reading. In short if you want an easy db to port
SQL code that was intended to be portable to, MySQL is the RDBMS for you.
For people who want to avoid putting business logic in the db, and want to
put all the API's for interoperability and integration in their app logic,
it's a good RDBMS. In fact, I can't actually think of better. This is
*especially true* if you want to make it dangerous for other apps to write
to the db, perhaps in order to say this is not supported and ask people to
purchase more client access licenses....

MySQL behavior that seems "incorrect" is not necessarily "incorrect" in
that context. It's a data store for one app to write to and optionally
other apps to read from. The app can be trusted to not do crazy things
with sql_mode settings or the like, and if it does, whatever the app tells
the db is correct behavior, the db is supposed to do.

PostgreSQL on the other hand has been engineered from the beginning (as I
understand it) with the idea that you have multiple applications writing to
the same relations. So a lot of the things like sql_mode settings, which
are great for porting applications to MySQL, would be dangerous in a
PostgreSQL context. The relations are a public API, while in MySQL they
are at least semi-private. Additionally from the beginning you have had a
very strong emphasis on being able to do advanced data modelling in
PostgreSQL perhaps to an extent even today unparalleled elsewhere. If you
are going to do db-level programming in PostgreSQL, you shouldn't IMO think
like an application developer but rather like a database developer.

What I am getting at is that if you are an app developer looking at
databases, MySQL looks fine, and the warts more or less match how you would
tend to think a db should act anyway. If you are a db developer,
PostgreSQL tries hard where we all agree on correct db behavior to do the
right thing without respect to what the app might have intended. On the
other hand, this is mostly a platform for data modelling, and if you are an
app developer a lot of things will seem weird in that context until you get
used to it.

Like it or not, the perspectives are very different. If all you want is an
information store for your app with reporting capabilities, then you end up
with a different solution then if you want to manage data in a centralized
way.

Best Wishes,
Chris Travers