Re: MySQL vs PostgreSQL

On Tue, 2004-08-24 at 04:51, Kunal wrote:
> I know I know, this topic is probably a cliche by now, but I am looking for real world developers'
> and DBAs' experiences from people who have managed/ developed for both.
>
> I've developed for MySQL before but never used PostgreSQL but am interested in doing so. I'll
> probably be giving it a shot myself anyways, but with the newer versions of MySQL offering
> features like views, SPs etc., what makes PostgreSQL worth the switch?

Ever heard the phrase "putting lipstick on a pig?"

Don't get me wrong, I don't hate MySQL. I think it's got its uses, but
the problems it has with data validation alone are enough for me to
steer clear of it. For instance, inserting 0000-00-00 as a date, or
just silently munging numbers it isn't sure what to do with.

For instance:
mysql> create table a (bignum numeric(200));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into a values
(1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+------------------------------------------------------------------------------------------------------+
|
bignum |
+------------------------------------------------------------------------------------------------------+
|
1234567890123456718070421139719794622419313690432875243234074448189700007474323004944782828398182400 |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Notice those numbers don't match, but there's no error telling me my
number is getting hosed up. (This, like so many other things, works
perfectly in PostgreSQL.)

Plus, most features aren't things on a check list that are simply done.
It takes a long time for such features to mature in terms of stability
and performance, and PostgreSQL has a big lead on MySQL in both.

> I've gone through many
> articles on this topic, but most of them compare features and not experiences which is what I am
> looking for.

PostgreSQL is quite different in terms of philosophy than MySQL. MySQL
focuses on speed and ease of use, PostgreSQL focuses on stability,
reliability, and correctness first. They are sort of converging, but
it's easier to performance tune a database that does the right thing
than it is to try and add correctness to an established database while
not breaking dozens and dozens of apps already written for it.

google for "mysql gotchas" and see what comes up.