Re: Ultimate DB Server - Mailing list pgsql-hackers

> Hardware can bring a 2x gain whereas software optimization can boost an
> application by 10x. Until now, I never heard or read about a real *software
> optimization* benchmark between MySQL and PostgreSQL.

It has been my experience that a knowledgeable, SQL savvy engineer can not use
MySQL. You have to have no basic knowledge of SQL to be able to work within its
limitations. Every project with which I have tried MySQL, I have always found
myself trying to work around what I can't do with it. In that respect, it is
like working on Windows.

> I see at least two easy cases where PostgreSQL beats MySQL:
> 1) Create a simple relational DB with triggers storing values instead of
> performing LEFT JOINS. Increase the number of simultaneous queries. MySQL
> will die at x queries and PostgreSQL will still be working at 5x queries.
> 2) Use PL/pgSQL to perform complex jobs normally devoted to an application
> server (Java, PHP) on a separate platform. In some case (recursive loops
> for example), network traffic can be divided by 100. As a result,
> PostgreSQL can be 10x faster because everything is performed server-side.

Server side programming is a double edged sword. PostgreSQL is not a
distributed database, thus you are limited to the throughput of a single
system. Moving processing off to PHP or Java on a different system can reduce
the load on your server by distributing processing to other systems. If you can
cut query execution time by moving work off to other systems, you can
effectively increase the capacity of your database server.

Typically, on a heavily used database, you should try to limit server side
programming to that which reduces the database work load. If you are moving
work, which can be done on the client, back to the server, you will bottleneck
at the server while the client is sitting idle.

> 
> This is to say that, in some circomstances, PostgreSQL running on an i586
> with IDE drive beats MySQL on a double Pentium. In real life, applications
> are always optimized at software level first before hardware level. This is
> why PostsgreSQL is *by nature* better than MySQL.

One of the reasons why PostgreSQL beats MySQL, IMHO, is that it has the SQL
features that allow you to control and reduce the database work load by doing
things smarter.

> 
> Unless MySQL gets better, there is no real challenge in comparing both systems.

It is funny, I know guys that love MySQL. Even when I show them the cool things
they can do with Postgres, they just don't seem to get it. It is sort of like
talking to an Amiga user.