Thread: MySQL vs PostgreSQL

MySQL vs PostgreSQL

From
Kunal
Date:
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

Re: MySQL vs PostgreSQL

From
Oliver Fromme
Date:
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

Re: MySQL vs PostgreSQL

From
"Scott Marlowe"
Date:
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.



Re: MySQL vs PostgreSQL

From
Frank Bax
Date:
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.


Re: MySQL vs PostgreSQL

From
"M. Bastin"
Date:
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

Re: MySQL vs PostgreSQL

From
Gaetano Mendola
Date:
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





Re: MySQL vs PostgreSQL

From
Steve Crawford
Date:
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

DATE column doesn't like empty string or null value

From
michael@floog.net
Date:
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



Re: DATE column doesn't like empty string or null value

From
Tom Lane
Date:
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

Re: DATE column doesn't like empty string or null value

From
michael@floog.net
Date:
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
>






Re: MySQL vs PostgreSQL

From
Kunal
Date:
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