Re: "Too far out of the mainstream"

Em 06/09/2012 00:39, Scott Marlowe escreveu:
> On Wed, Sep 5, 2012 at 8:56 PM, Edson Richter <edsonrichter@hotmail.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.

Yes, I heard from a beginner devel that he likes MySQL because it gives
less errors. PostgreSQL was always bugging his app complaining about
some "foreign keys".
I just had to get out for laugh :-)

Nevertheless, I've a large app, and I admit: I tried to run with
MySQL+InnoDB. I'll never do the same mistake twice. My data got corrupt
(foreign keys have been ignored, as well primary keys), and I got lots
of zombie records in database.

Nowadays, I just limit my self to adults databases: PostgreSQL (my
preferred on last 5 years because it just works), MS SQL (because I
worked with it for most of my professional life since 1990s: and yes, I
used it when it was just Sybase's core), Oracle (besides I think it's
like a big expensive White Elephant) and Db2, that surprised me in its
last incarnation.

What I feel missing in PgSQL? Tools that help me to improve performance.
Every time I need to analyze a query, I miss the MS SQL Server
performance analyzer tool, and the Db2 optimizer :-)

But life is like that, and I get used to it. And PostgreSQL have been
working very weel since 8.4 for me (by today, all my databases
(development and production) run 9.1 without any trouble).

Regards,

Edson