Thread: MySQL vs PostgreSQL
I know I know, this topic is probably a cliche by now, but I am looking for real world developers' and DBAs' experiences from people who have managed/ developed for both. I've developed for MySQL before but never used PostgreSQL but am interested in doing so. I'll probably be giving it a shot myself anyways, but with the newer versions of MySQL offering features like views, SPs etc., what makes PostgreSQL worth the switch? I've gone through many articles on this topic, but most of them compare features and not experiences which is what I am looking for. Please do spare some time, if possible, to share this knowledge. Don't think there's scope for another mistake in this mail like my previous one ;) Kunal PS - Please feel free to forward it to one of the other lists and/ or point me to that list, if that question will be more suitably answered there. __________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail
Kunal wrote: > I know I know, this topic is probably a cliche by now, but I am looking for real world developers' > and DBAs' experiences from people who have managed/ developed for both. > > I've developed for MySQL before but never used PostgreSQL but am interested in doing so. I'll > probably be giving it a shot myself anyways, but with the newer versions of MySQL offering > features like views, SPs etc., what makes PostgreSQL worth the switch? I've gone through many > articles on this topic, but most of them compare features and not experiences which is what I am > looking for. The following is just my personal opinion, based on what I experienced with these systems. Your mileage may vary, and I'm certain there are people with conflicting opinions, so please put flames to /dev/null. As far as the features are concerned, mysql is still light- years behind postgres. Yes, they did introduce transactions, but only with certain table types, and then you can't have clustering, or something else is limited in absurd ways. There are many cases of "if you use feature x, you cannot use feature y". I don't know how much of that was fixed, because I tried to avoid mysql in the recent past. It may well be the case that those things have been fixed. One thing which I like about postgres is its robustness. I experienced broken mysql tables several times; it seems to be a very fragile system. It never happened to me with postgres. You can even pull the power plug from a running postgres server -- Upon reboot, it will complain loudly at you, but it will come up and run just fine. Also, I think it is much easier to debug SQL statements in postgres. The output from explain analyze etc. is very helpful in finding out why a query might perform less than optimal. Best regards Oliver PS: In case it matters, I'm using postgres on FreeBSD 4.x on UFS and NFS filesytems. -- Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München Any opinions expressed in this message may be personal to the author and may not necessarily reflect the opinions of secnetix in any way. "I learned Java 3 years before Python. It was my language of choice. It took me two weekends with Python before I was more productive with it than with Java." -- Anthony Roberts
On Tue, 2004-08-24 at 04:51, Kunal wrote: > I know I know, this topic is probably a cliche by now, but I am looking for real world developers' > and DBAs' experiences from people who have managed/ developed for both. > > I've developed for MySQL before but never used PostgreSQL but am interested in doing so. I'll > probably be giving it a shot myself anyways, but with the newer versions of MySQL offering > features like views, SPs etc., what makes PostgreSQL worth the switch? Ever heard the phrase "putting lipstick on a pig?" Don't get me wrong, I don't hate MySQL. I think it's got its uses, but the problems it has with data validation alone are enough for me to steer clear of it. For instance, inserting 0000-00-00 as a date, or just silently munging numbers it isn't sure what to do with. For instance: mysql> create table a (bignum numeric(200)); Query OK, 0 rows affected (0.02 sec) mysql> insert into a values (1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890); Query OK, 1 row affected (0.00 sec) mysql> select * from a; +------------------------------------------------------------------------------------------------------+ | bignum | +------------------------------------------------------------------------------------------------------+ | 1234567890123456718070421139719794622419313690432875243234074448189700007474323004944782828398182400 | +------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Notice those numbers don't match, but there's no error telling me my number is getting hosed up. (This, like so many other things, works perfectly in PostgreSQL.) Plus, most features aren't things on a check list that are simply done. It takes a long time for such features to mature in terms of stability and performance, and PostgreSQL has a big lead on MySQL in both. > I've gone through many > articles on this topic, but most of them compare features and not experiences which is what I am > looking for. PostgreSQL is quite different in terms of philosophy than MySQL. MySQL focuses on speed and ease of use, PostgreSQL focuses on stability, reliability, and correctness first. They are sort of converging, but it's easier to performance tune a database that does the right thing than it is to try and add correctness to an established database while not breaking dozens and dozens of apps already written for it. google for "mysql gotchas" and see what comes up.
At 06:51 AM 8/24/04, Kunal wrote: >I've developed for MySQL before but never used PostgreSQL but am >interested in doing so. I'll >probably be giving it a shot myself anyways, but with the newer versions >of MySQL offering >features like views, SPs etc., what makes PostgreSQL worth the switch? What do you mean "newer versions of MySQL offering features like views, SPs"?? Don't you mean "will be offering"? These features are still a long way from production code in MySQL. Both features are part of 5.0 release. MySQL 4.1 (first development release) was announced in January 2003 and is still in beta. http://www.mysql.com/news-and-events/press-release/release_2003_05.html The announcement also contains: >> Available by the end of 2003, MySQL Version 5.0 will include >> features such as stored procedures and triggers. MySQL 5.0 (alpha developer release) was announced one year later and is still listed as Alpha. http://www.mysql.com/news-and-events/press-release/release_2004_02.html PostreSQL is worth the switch because these and many other features are available TODAY. Personally, I compared the two products early in 2000 and chose PostgreSQL at that time.
Hi Kunal, Even if the features were identical, which they aren't, then you'd still choose PostgreSQL with your eyes shut since it's totally free. MySQL is $500 per seat for commercial deployment. (There's a $250 option but it doesn't include InnoDB and then you wouldn't even be ACID compliant. Google for InnoDB and ACID if you need more info.) Marc
Kunal wrote: > I know I know, this topic is probably a cliche by now, but I am looking for real world developers' > and DBAs' experiences from people who have managed/ developed for both. > > I've developed for MySQL before but never used PostgreSQL but am interested in doing so. I'll > probably be giving it a shot myself anyways, but with the newer versions of MySQL offering > features like views, SPs etc., what makes PostgreSQL worth the switch? I've gone through many > articles on this topic, but most of them compare features and not experiences which is what I am > looking for. > > Please do spare some time, if possible, to share this knowledge. You can start to read mysql gotchas and postgresql gotchas: http://sql-info.de/mysql/gotchas.html http://sql-info.de/postgresql/postgres-gotchas.html apparently sql-info.de is unreachable now but I hope will be in a while. Regards Gaetano Mendola
On Tuesday 24 August 2004 3:51 am, Kunal wrote: > I know I know, this topic is probably a cliche by now, but I am > looking for real world developers' and DBAs' experiences from > people who have managed/ developed for both. ... Relevant article: http://www.tek-tips.com/faqs.cfm?fid=1751
Hi Group: I used COPY to import a bunch of data into my PG database. The db has one table so far, and the table has a column using DATE with a datestyle of US,ISO. To get all the data to copy correctly, I created a meaningless date entry of 01/01/2001 and inserted this into every row in the database. I would like to delete these bogus date entries and leave the date column blank until proper dates can be filled in. What command can I use to remove all the 01/01/2001 entries in every row and leave it blank. Thank you for your time and patience. Mike
michael@floog.net writes:
> What command can I use to remove all the 01/01/2001 entries in every row
> and leave it blank.
Set the values to NULL, eg
UPDATE mytab SET datecol = NULL;
regards, tom lane
Tom, Thanks for the speedy reply. I'm going to ssh to my home-box and give this a try right now. Mike Quoting Tom Lane <tgl@sss.pgh.pa.us>: > michael@floog.net writes: > > What command can I use to remove all the 01/01/2001 entries in > every row > > and leave it blank. > > Set the values to NULL, eg > UPDATE mytab SET datecol = NULL; > > regards, tom lane >
Hi all Was unable to reply earlier but here I go now... thought it's better to consolidate my responses in one single mail than sending multiple ones. This is not my acadmey award winning "thank you" speech :) - just wanted to thank each one of you for taking the time out to write detailed responses and/ or point to some useful links... Thanks a ton! Tim Thanks for your response - not exactly what I asked, like you said :) but it was good to hear your "perspective" - reading your mail has me all excited about learning Python as well - something I have been wishing to do for a long time, and now I have the excuse - or the push, if you must :) Steve and Gaetano Thanks for the links - really useful! Frank You're right about those features not being in MySQL at the moment, but I was just trying to level the playing field out by saying let's ignore the features for the time being and concentrate at other stuff. PostgreSQL kicks some serious butt as far as features are concerned, no doubt ;) Scott Thanks for taking out the time for that long reply man - really appreciate it! MySQL does seem a little primitive and amatuerish compared to PostgreSQL now :) Sean and Oliver I gather from your posts that PostgreSQL is definitely more stable and robust than MySQL - you did share some good "real world experiences" I was looking for - thanks guys! Bruce If you're reading this - though this is from another thread - I still haven't found that button mate - will let you know as soon as I find it ;) Also Marc Bastin who sent a PM - thanks! Cheers guys - I guess I'll be around here a lot more to pester you all with questions ;) Kunal __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail