Re: "Too far out of the mainstream"

On Wed, Sep 5, 2012 at 8: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 kind of agree with both of you somewhat.

A lot of developers think of their data in a hierarchical manner. If
so a simple key->value data store is often your best answer.

MySQL's basic design is that of a simple key->value data store
parading as a relational database. While it's had a lot done to it to
make it better in the role of relational data manager, it's still got
a lot of baggage from back in the day that means that as you go from
simple data store to complex relational data management, you start to
notice warts, like a planner that's dumb as a stump, and very simple
join methods that make complex queries painfully slow. For people who
are just storing and retrieving lots of simple data, it's still great.

PostgreSQL's heritage was correctness in SQL and set theory. The
obvious example is queries that MySQL, or at least older versions of
it, would run that Postgresql would, correctly, throw an error on.
Simple example is:

select a,b,c from sometable group by a;

assuming there's no PK on a, this query SHOULD throw an error because
in that case which values you get for b and c are both undefined, and
the SQL standard says that it should therefore throw an error.
Performance and easy use were not a priority for most of its early
life, so the MySQL philosophy of "just run the query and give me the
wrong answer like I asked" wasn't good enough.

They started from very different places, and while they've moved
towards each other over the last decade, their heritages mean they
still have very different strengths and weaknesses.

If you write code by grabbing small globs of data from the db, doing
the mangling in the CPU, then stuffing them back out to the db, MySQL
might be your best choice. If you write code by transforming data sets
in the database, then PostgreSQL is likely your best bet.

The problem you run into is that if you're only familiar with one db
and you're trying to use it like the other one. MySQL will dominate
at apps that mostly read a lot of tiny bits of data and occasionally
write chunks of code out. PostgreSQL will dominate at lots of atomic
updates or large data transformations all taking place in the db
layer, not in app code.