Sometimes, the question "Which FOSS DBMS to use for my application, MySQL or PostgreSQL?" arises in forum topics, mailing lists, blogs, etc. The answer is not as simple as the question itself and I won't try to answer it here. Instead, I will stand at an issue that arises from the answers given by various people.
It seems that there's a considerable percentage of developers out there that consider MySQL a RBDMS like system. You want transactions? Forget it! You want foreign keys? Forget it! Use PostgreSQL! Well, maybe these people can explain us why the heck MySQL is by far the most popular DBMS used in the web nowadays since it lacks these basic features.
These people might forget the fact that MySQL has a pluggable architecture that accepts a variety of storage engines that each has its own implementation of how to store and validate data. A basic MySQL installation comes with the MyISAM, InnoDB, BDB, memory, archive, etc. storage engines. For most generic use, the first two are mostly used (and that's why they're considered the most stable). When most people talk about MySQL, they have the equation MySQL=MyISAM in their minds, which is of course wrong. There's an option for the MySQL configuration file (my.cnf or my.ini) called default-storage-engine, that selects the default storage engine upon the creation of tables when there's no indication which storage engine to use. Simply because the default storage engine until now is MyISAM and the developers don't bother to define it while creating the database tables do not mean that MySQL is not ACID compliant. InnoDB is a more than fine storage engine, that it supports transactions, foreign keys, row level locking and multi versioning concurrency control besides other. I think that the upcoming MySQL 5.5 series will make InnoDB the default storage engine. Maybe then it will be the time that people actually realize that MySQL is more than just a RDBMS like system.
This is not a MySQL biased point of view. Of course MySQL has its weaknesses, regardless of the underlying storage engines, since there are features that must be implemented in the server portion and the fact is that each newer version brings more and more features on the table. On the other hand, PostgreSQL is known to be an excellent alternative. I haven't personally used it, however I plan to "play" with it in the near future.
