Message: 9 Reply-To: <donald.braman@yale.edu> From: "Donald Braman" <donald.braman@yale.edu> To: <zope@zope.org> Date: Wed, 14 Feb 2001 14:08:53 -0500 Subject: [Zope] MySQL, PostgreSQL, etc.

Hello!

I'm in a situation that I'm sure many are familiar with...choosing between MySQL or PostgreSQL. My main goals are stability under high load and ease of use. I don't need transactions. I know the standard comparison: MySQL is faster, simpler, and more popular, while PostgreSQL is slower but is more fully SQL standards based, handles transactions, etc. Are these generally accurate descriptions?

Stability under load - seems to favour PostgreSQL . Ease of Use - not quite sure what you mean by that, but the features available in PostgreSQL make it easier to code SQL that does what you want it to. Features such as Unions and Subqueries help you get at your data easily, features such as Views, Triggers and Stored Procedures are used to make databases consistent and secure - which I guess makes them easier to use ! I've seen lots of discussions on this topic and the answer you get seems to depend entirely on the person you ask. If you ask someone with experience of developing large apps using commercial databases they will say PostgreSQL. They find it amazing that anyone would contemplate serious database development without Unions, Subqueries, Views, Triggers and Stored Procedures. It's like going back ten years. Having said that the lack of OUTER (LEFT) joins in PostgreSQL is a pain, though of course there's a workaround If you ask a web-developer then the apparent speed gain of MySQL apparently overrides all other considerations. However the latest release of PostgreSQL (7.1) appears to actually outperform MySQL, particularly in a high-load situation where inserts are being done on-line (MySQL's lack of row-level locking appears to be the problem here). Take a look at this comparison http://www.phpbuilder.com/columns/tim20001112.php3 I developed a Zope-based project using PostgreSQL originally but I migrated it to MySQL because my host providers (those excellent people at NIP) were only supporting MySQL. Since my project (www.tunedb.org) didn't have any transactions involving money or real things (like stock) then the loss of transactions was not a major problem. Of course all limitations _can_ be worked around and I found workarounds for the missing Unions and Subqueries though it was a real pain. The 7. 1 release of PostgreSQL seems to address the performance issue so I can't see any reason to use MySQL if you have a free choice.

I'm wondering what people who have used either (or both!) think about using these (or other databases) in conjunction with Zope. Are there any special cases where one or the other works better or worse with Zope?

In terms of use with Zope - I found no practical difference. Both work extremely well. The only issue I had with MySQL is that I developed my application using the latest available version at the time, then implemented it at NIP on a slightly earlier release and I found a couple of important differences to the product. If you are looking for external hosting make sure you use the same version as your external hosts - MySQL in particular seems to introduce major changes in minor releases. When I migrated I often had to workaround the limitations of MySQL so I left the ZSQL Method code there and used a variable to hold the type of database being used. So, where there is a difference I have <dtml-if "dbtype=='mysql'"> Mysql code <dtml-else> Postgres code </dtml-if> so that I can generate the correct SQL depending on the database being used. I don't know of a way to change the database connection programmatically, so to actually make the change you would have to go and change all the connections in all the ZSQL Method - not a huge task. Richard Moon