Here's the thing. If you need any of the features of PostgreSQL that MySQL doesn't provide, like:
On the other hand, there are some applications that will never need any of these things. But, can you really count on that?
MySQL is slowly gaining one feature at a time from PostgreSQL. Sure, it has transactions now, but how mature is the implementation? And it has subselects, but those were introduced in Pg more than a couple of years ago, and have had a lot of time to get more and more optimized.
So, my advice is, if you need a real database, you can't pick MySQL at this point. And even if you think you can get by with MySQL, who is to say that you won't need more features later?
As for support, RedHat's database is PostgreSQL, so you can buy commercial support easily if you're already in the RedHat camp.
About the only thing that MySQL does better right now is replication. But there's a kludgey solution for Pg right now, and a better solution in the next release.
No, Pg's not for everyone. Just the ones that are coming down from Oracle, looking for an "open sourced" Oracle. If you're happy with DBM, MySQL will make you happier. But at some point, real apps need at least one or two of the things in the list above. And then you either migrate, or grimace. {grin}
One feature I cannot seem to live without is the ability to do complex subselects and joins. It's a thorn in my side when I can't do this with MySQL, but somehow I manage to not need it when I'm hacking with MySQL. Another favorite feature of mine is the ability to create and query against views. In most cases, views are a nice-to-have feature that can be routed around with sufficiently tricky SELECT statements.
I've also never written a stored procedure in PL/SQL or any other language, nor have I ever needed a trigger (except for the odd hack to enumerate records). Again, I've seemed to manage somehow.
I'll grant you that a lot of people need these features in their apps, but MySQL sheer existance and popularity never ceases to amaze me with what people can do with basic, rudimentary features. If shops like Yahoo! can use MySQL as heavily as they do, it can't suck that much. Sure, they could come up with a BerkeleyDB-based solution, but it's much more productive for them to use a real RDBMS with SELECTs, ORDER BY and the ability to index on demand. That would make about as much sense as throwing out their Perl, PHP, Java, etc. programs to rewrite their entire infrastructure in FORTH.
That's FUD and you know it. Either their ACID compliance is 100% or it isn't. You cannot have 99 44/100% data integrity in a relational database. (Both offer row locking, although some of MySQL's table types are explicitly less granular.)MySQL is slowly gaining one feature at a time from PostgreSQL. Sure, it has transactions now, but how mature is the implementation?
More FUD. Either they work or they don't.And it has subselects, but those were introduced in Pg more than a couple of years ago, and have had a lot of time to get more and more optimized.
And my point remains that most people don't choose a database based on a lengthy feature list. There are always other factors. MySQL has done a great deal to give voice to some of the them: size of the user community, number of addon utilities, ease of use, ease of administration, quantity of worthwhile documentation, etc.So, my advice is, if you need a real database, you can't pick MySQL at this point. And even if you think you can get by with MySQL, who is to say that you won't need more features later?
RedHat isn't really pushing RHDB anymore. Word on the street is that this was a bargaining chip against Oracle to force them to more actively support Oracle on RHLinux. On paper, it looks like a nice option. In reality, I don't think its as nice as the marketing department would have you believe, nor is it an option if you're not a RHLinux user.As for support, RedHat's database is PostgreSQL, so you can buy commercial support easily if you're already in the RedHat camp.
Re:MySQL vs. PostgreSQL
jdavidb on 2003-03-24T19:47:21
Both offer row locking
Actually, PostgreSQL implements a solution called "Multi Version Concurrency Control" or MVCC, which is billed as "better than row-level locking." This is even more advanced that what Oracle uses, although I would not expect that gap to stay open long.
Re:MySQL vs. PostgreSQL
Matts on 2003-03-24T23:02:10
MVCC is exactly the same as what Oracle uses.Re:MySQL vs. PostgreSQL
jdavidb on 2003-03-26T05:23:10
Um, not according to my O'Reilly Oracle 8, 8i, and 9 Essentials book which describes row locking in great detail. Google for some combination of PostgreSQL, better than row-level locking, and MVCC for their claims that they have it and Oracle doesn't.
Poking around oracle.com with google, I see this, which suggests MVCC for queries, but I believe PostgreSQL uses it for all aspects of a transaction, including DML as well. MVCC replaces row-level locking, so I wouldn't expect to see references to row-level locking in a system that used it. I suppose they might have some way of configuring the database to use one or the other, but I don't seem to see that implied.
But I'm open to being told I've completely misunderstood.
:D Re:MySQL vs. PostgreSQL
Matts on 2003-03-26T08:31:26
Unfortunately I don't have time to look this up right now, but I'm almost 100% certain I'm correct on this (I was an Oracle developer for 2 years).Re:MySQL vs. PostgreSQL
IlyaM on 2003-03-26T22:50:17
MySQL also implements multiversioned database.
And even if you think you can get by with MySQL, who is to say that you won't need more features later?
I think you could just as easily say, even if you think you can get by with PostgreSQL, who is to say that you won't need more features later?
I had a recent meeting with a big company who wants to do something interesting with Wikis and weblogs. The original Wiki is written in a couple of hundred lines of Perl and uses flatfiles to store the pages. Ward said he'd tried DBM but it was actually slower. He estimates some five thousand regular readers per day, perhaps up to a page served every second -- off of one box. There are ways to cluster read-only web boxes so you can add them easily, saving one beefy box for much-less frequent writes. It's fairly scalable.
As for weblogs, some use flat files, and a few others use MySQL. Lots are written in Perl.
The big company has bought into Java big time. Their SAs weren't thrilled with the Perl idea. That's fine; it's not difficult to port something simple like a Wiki or a weblog. It'll take time (which they don't have), but it's workable.
The other requirement was... Oracle.
No flat files, just a heckuva database backend.
Absolutely 100% free
I notice you list that in a list of features PostgreSQL has that MySQL does not. Care to elaborate on why?
Re:Free as in...?
jdavidb on 2003-03-24T19:49:23
Incidentally, I'm all for PostgreSQL, myself. I was just wondering about this item.
Re:Free as in...?
ziggy on 2003-03-24T19:53:52
MySQL is dual licensed. Either you take the standard GPL version, or you can pay for a commercial license with more flexability. PostgreSQL is BSD licensed, so do what you want with it and be happy.Re:Free as in...?
jdavidb on 2003-03-24T20:03:49
I think I sort of knew about the dual licensing, but it seems like to me if it is available optionally under GPL, it's free. And I drank enough FSF koolaid to kill some of you people.
:) I was curious if he meant there was some restriction somewhere people didn't know about; if he meant the dual licensing; if he meant the whole mysql.com vs. mysql.org fiasco; or if he just got carried away and started listing PostgreSQL features in general.
Re:Free as in...?
oneiron on 2003-03-25T04:09:06
You can bundle PostgreSQL as part of a commercial closed-source product that you sell for profit. Can you do that with MySQL?
Re:Free as in...?
jdavidb on 2003-03-25T14:08:53
Well, that's not a "freedom" I value, although I can see that many other people do. Is that what Randall meant?
Seriously: I've build many database-backed applications over the past 15 years, and have used only a few of the features on your list. Easily half of those applications needed nothing more than MySQL provides in 4.0 (e.g., ACID transactions). A big problem with stored procedures is that people reach for them prematurely, locking you in to a particular vendor.
All the databases mentioned so far have their place.. One reason I havent gotten into Postgres yet is because a Win32 version that doesnt run on Cygwin is hard to find (someone please toss me a link so I can install it at home and try it out)
MySQL has a native Win32 version which makes it much easier to install and try out (and yes, there are those among us who actually use Windows based servers, I am one of them
Re:Another point of view..
alessio on 2003-03-26T11:33:31
One reason I havent gotten into Postgres yet is because a Win32 version that doesnt run on Cygwin is hard to find
Will be probably available in the next release.
