re:[Zope] MySQL, PostgreSQL, etc
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
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
fyi postgres 7.1 has outer joins and nulls in user functions, and no more 8k row limit. kapil
It is nice that Postgres is close to SQL standards compliance. If I have to learn a db language, it may as well be real SQL. I also like the on-line official Postgres on-line manual much more than MySQLs (though I just ordered Paul Paul DuBois MySQL book which gets rave reviews). One thing that MySQL *does* have that is attractive to me (please feel free to laugh at my pathetic state) is a GUI. Now, for a dope like me who resides, whenver possible, in GUI land, that is a nice feature. But, I'm also not a fool, and it sounds like (at least from the responses I've gotten so far) it is probably worth my while to learn SQL and Postgres from the command line and save myself headaches down the road. So I have two follow up questions... 1. Documentation will be important to me...are there good references out there beyond the Postgres (on-line/hard copy) manual that I could look at? 2. Is there any GUI project for Postgres out there? -Don -----Original Message----- From: Richard Moon [mailto:richard.moon@nec.ac.uk] Sent: Thursday, February 15, 2001 7:39 AM To: zope@zope.org; donald.braman@yale.edu Subject: re:[Zope] MySQL, PostgreSQL, etc 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 !
A big minus is the lack of Windows support in PosgreSQL. To me anyway. One thing is to propose a no-name DB to a windows shop. But it's very hard to sell a linux based solution to a windows based IT shop. Rightly so, as they will be hosed if something goes sour and they have no idea how to fix it. mySQL scores one for better Windows support. Regards Max M
PostgreSQL does work OK on Win32. I'm using it right now supporting several Zope sites on NT 4. (I would have preferred Linux, but got free use of an NT system on the net, so I went with that.) PostgreSQL *is* a bit of a pain on NT: It depends on Cygwin. It depends on an 'ipc-daemon' process running in the background. It's tricky to set up ipc-daemon and the postmaster (PostgreSQL daemon) processes as NT services. Some PostgreSQL tools such as pgaccess are hard to build on NT. I've only been able to access PostgreSQL from Zope via ODBC. OTOH: Cygwin is easy to install. We've done some work to make it easy to install ipc-daemon as a service. A project is underway to get PostgreSQL running directly as an NT service. It currently needs a wrapper like 'srvany' or 'invoker'. ODBC suffices for my Zope sites, at least so far. On Thu, Feb 15, 2001 at 06:55:36PM +0100, Max M wrote:
A big minus is the lack of Windows support in PosgreSQL. To me anyway.
-- Fred Yankowski fred@OntoSys.com tel: +1.630.879.1312 Principal Consultant www.OntoSys.com fax: +1.630.879.1370 OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA
On Thu, Feb 15, 2001 at 06:55:36PM +0100, Max M wrote:
A big minus is the lack of Windows support in PosgreSQL. To me anyway.
One thing is to propose a no-name DB to a windows shop. But it's very hard to sell a linux based solution to a windows based IT shop.
http://emea.cobalt.com/products/qube/details.html Runs postgres without a hitch. The users won't even know that there is Linux under. However, I don't think they support zope on it. -- Riku Voipio riku.voipio@tietoenator.com 09-862 60764
On Thursday 15 February 2001 08:39, Donald Braman wrote:
It is nice that Postgres is close to SQL standards compliance. If I have to learn a db language, it may as well be real SQL. I also like the on-line official Postgres on-line manual much more than MySQLs (though I just ordered Paul Paul DuBois MySQL book which gets rave reviews). One thing that MySQL *does* have that is attractive to me (please feel free to laugh at my pathetic state) is a GUI. Now, for a dope like me who resides, whenver possible, in GUI land, that is a nice feature. But, I'm also not a fool, and it sounds like (at least from the responses I've gotten so far) it is probably worth my while to learn SQL and Postgres from the command line and save myself headaches down the road. So I have two follow up questions...
1. Documentation will be important to me...are there good references out there beyond the Postgres (on-line/hard copy) manual that I could look at?
postgresql, addison wesley, burce momjian. its under open content license with text at postgres site. its been thoroughly reviewed. the mailing lists are good place to start, they have many different ones, good starters are the novices, sql, and maybe the general list.
2. Is there any GUI project for Postgres out there?
never used it, but there is something called pgaccess. also someone was showing a demo of a zope app that provided gui interactivity with postgres (it looked amazing). kapil
Thanks, I'll look for pgaccess. I'd also love to see the Zope app that did that...was it by any chance the ZDatabaseTool? (http://www.zope.org/Members/aeg/ZDatabaseTool) -Don -----Original Message----- From: ender [mailto:kthangavelu@earthlink.net] Sent: Thursday, February 15, 2001 2:16 PM To: Donald Braman; zope@zope.org Subject: Re: [Zope] MySQL, PostgreSQL, etc
2. Is there any GUI project for Postgres out there?
never used it, but there is something called pgaccess. also someone was showing a demo of a zope app that provided gui interactivity with postgres (it looked amazing). kapil
Webmin also has a GUI to postgres.
2. Is there any GUI project for Postgres out there?
never used it, but there is something called pgaccess. also someone was showing a demo of a zope app that provided gui interactivity with postgres (it looked amazing).
participants (7)
-
Donald Braman -
ender -
Fred Yankowski -
marc lindahl -
Max M -
Richard Moon -
Voipio Riku