[Zope] MySQL, PostgreSQL, etc

[Richard Moon]

>
>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