Re: postgresql vs mysql - Mailing list pgsql-general

At 07:31 PM 2/21/2007, Chad Wagner wrote:
>On 2/20/07, gustavo halperin
><<mailto:ggh.develop@gmail.com>ggh.develop@gmail.com> wrote:
>I  have a friend that ask me why postgresql is better than mysql.
>I personally prefer posgresql, but she need to give in her work 3 or 4
>strong reasons for that. I mean not to much technical reasons. Can you
>give help me please ?
>
>
>How about the fact that MySQL accepts the following query as legal:
>
>SELECT foo, bar, COUNT(*)
>FROM baz
>GROUP BY foo
>
>And produces, naturally, an unexpected result instead of an
>error.  Totally annoying, I don't know if it was ever fixed.  It
>seems that MySQL's parser is generally weak at syntax validation in
>it's default configuration.

** syntax/misc gotchas

Too many. See other emails. Or search for MySQL gotchas.

** Feature gotchas
At first look MySQL seems to have all sorts of nice features and
great performance. BUT, when you start to get to the details, too
often you'd find that some features aren't so compatible with others
or take a bit (lot?) more effort to get working properly.

Want transactions? Use innoDB. Want to restore a multi-gigabyte
database fast from backups, sure use MyISAM (too many people seem to
have probs doing that with innoDB).

Want foreign keys to work? Use innoDB. MyISAM tables allow you to
specify foreign keys but ignores AND forgets them.

You can mix MyISAM tables with innoDB tables in the same database.
That's a minus.

Want to back up a consistent snapshot of the database AND still have
users using the database live? Use only InnoDB tables. Because to
ensure consistency when dumping MyISAM tables you should lock all the
tables involved. You still want a live consistent backup of a
database with some MyISAM tables? Here's a method I suggested: use
multiple MySQL servers with replication - do the backup snapshot off
a slave, while users are using the master (or other slaves). If
anyone has better ideas do let me know :).

Do not use innoDB on a filesystem that does not support files > 2GB
in size. Though MySQL +innoDB supports a configurable like
"autoextend:max:1000M", this only works if you using a single shared
tablespace, doesn't work if you are using one
"innodb_file_per_table". BUT if you are using a single shared
tablespace be aware that you can't easily shrink such tablespaces and
reclaim unused space.

Too many IFs, BUTs, ONLYs, etc.

** D'oh level release gotchas
Example: Before MySQL 5.0.13, GREATEST(x,NULL) and
LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.13,
both functions return NULL if any argument is NULL, the same as
Oracle.  This change can cause problems for applications that rely
on the old behavior.

Or release 5.0.19: The InnoDB storage engine no longer ignores
trailing spaces when comparing BINARY or VARBINARY column values.
This means that (for example) the binary values 'a' and 'a ' are now
regarded as unequal any time they are compared, as they are in MyISAM
tables. (Bug#14189)

** Commercial/strategic gotchas
Oracle owns the companies that make the transactional backends for
MySQL (innoDB, sleepycat).

** Conclusion
In my opinion, if you don't have anything that specifically requires
MySQL, but where MySQL is suggested, it's better to use Postgresql.

Not saying Postgresql is perfect - rather that MySQL makes Postgresql
look really good.

Unfortunately, I have to deal with MySQL at work. *sigh*.

Regards,
Link.