[deleted]
" I'd like to point out that MySQL is STILL comparing itself to Postgresql 7.1, "
True, they are not very up-to-date down there, which just adds to the unfairness of it all...
Mysql database size
Hmmmm, if you scroll down to that bottom of the linked page, there is a MySQL advocate who actually admonishes many of the claims made!
My own company is going to eventually make part of our Service control System available directly to our clients via the web; looks likes there will to be some consideration for PGsQL.
[deleted]
Scott marlowe (well known from phpbuilder) is not really a mysql-lover. In fact, he's a bit of a PGsql lover actually 
[deleted]
"My own company is going to eventually make part of our Service control System available directly to our clients via the web; looks likes there will to be some consideration for PGsQL."
Just four words: Transactions, Triggers, Stored procedures.
that should be enough to convince mysql user to switch to pgsql
Lastly, for a real example of how pooly MySQL scales, just look at the availability issues for Slashdot running on top of MySQL. Plus, look at the slashcode. It's chock full-o-stuff that they have to do to make it work well on top of a relatively poorly scalable database.
Originally posted by vincente
That's one benefit of mysql, everything that is written for mysql can be ported to any other database.
Benefit? Since it has no stored procs virtually EVERY statement had to be re-written. LIMIT? Bah! Down with MySQL!
[deleted]
Ah!, but only to make it more efficient. Allmost every query that exists in mysql also exists in other databases.
Try porting an Oracle database to mysql.. .
True, I guess, since MySQL doesn't even support ANSI 92 standard. That's not really an advantage, though, but a horrible limitation. You think a product with 'SQL' prominantly in the name would at least support a 10-year old standard!
[deleted]
It's an advantage for all use developers who are called in to help port mysql-based apps to a more capable database
My other pet peeve with MySQL is that their use of limit is exactly backwards to how every other database does it. In most database that accept a limit x,y, the x is the number of rows, and the y is the offset, or if you just do limit x, then x is the number of rows. In MySQL they have that backwards, and use the limit x as normal, but with limit x,y y is the number of rows, and x is the offset.
Of course, since ANSI SQL doesn't support the limit keyword, it's a crap shoot for how it should be implemented.
In a REAL database you of course start a transaction, declare a cursor, and use that. Hmmm. I don't see declare cursor in the MySQL manual. hehe
I prefer Postgresql's method, using
limit x, offset y.
You know which is which.
I don't actually hate MySQL, so much as believe it to be an advanced form of handling hash / ISAM tables, and don't understand why people keep trying to change it from a decent text storage system with built-in full text indexing into an ACID rdbms.
For many projects, I've seen people use BOTH MySQL and a real rdbms, with MySQL kinda being a filing system and the real rdbms handling all the genuine rdbms stuff.
Postgresql has many more strengths than MySQL, but it has it's own little ugly blemishes as well, but it's blemishes exist on parts of the database that MySQL doesn't even have an equivalent for.
The most bothersome argument I keep seeing is people picking on Postgresql's need for vacuuming. I don't consider this a real disadvantage, since MVCC style locking is so superior to row/table locking as to be worth the need to run a cron job every hour or day to keep the database performing well, especially since 7.2 came out with non-blocking vacuuming.
The REAL blemish Postgresql has to deal with right now is uncontrolled index growth on heavily updated tables. That issue is being investigated right now, so hopefully sometime around 7.4 (6 months or so down the line) it will be fixed.
Typical of most things Postgresql, the main discussion right now is on how to implement this so that regular vacuuming can reclaim the lost index space without impacting running transactions etc... I.e. a typical thread is "OK, that's a good idea but what if 100 users are inserting rows on that table at the same time in a transaction?"
2 months later
I guess I'll thow in some waste of bandwidth.
We use mysql here at work, but the application that it supports is rather simple and light. Furthermore, the developer/owners had little in the way of resources (cash), so a purchase of something like Oracle or MSSQL was out of the question. My first suggesion was Postgres, but looking at all that we needed (which based on what the developer told me wasn't much), we fell back to MySQL.
As for transactions and fk's, we get those by using the innodb table handlers, which also provide much better referential integrity.
However, my biggest concern with Postgres was the lack of replication. It's come a long way since, but at the time it was all over the map. There is a core group working on it (I'm on the mailing list!) that has completed a simple master slave replication scheme, but at this time, it's not battle proven.
MySQL replication IS proven. Furthermore, it was no big deal to write some glue between the cluster managers and the mysql servers to react as needed in the event of a failure of the master. It's replication is also fast enough to allow reading from any of the replicated slaves and writting/updates only to the master. In other words, safe db load balancing.
But you first need a battle proven replication scheme to base all of this on.
Another fallacy is that MySQL slows down dramatically beyond 500K lines. Our servers have multiple databases with many of those containing tables with row counts well over 750K! The combined line count of all of our databases is approaching 2 million.
Additionally, some of the squawk I've heard to support the above tail is from people that either didn't setup the system properly, don't know what an index is, don't maintian the system (optimize table for example), or expect to see huge performance eventhough they're on a box that is also running a web server and/or countless other services.
Lastly, sub selects, unions, and full joins are available as of 4.1 (in alpha).
Now this is not to say that MySQL is better. The truth is that I really wanted to use Postgres, but I feel in retrospect that my decision was a good one. Postgres is fantastic! What it offers for free is incredible. In time I hope to use it for projects similar to what we did here at work.
But for now, MySQL has managed to make us very happy and leave us with enough extra cash to put into the cluster in the form of hardware.
Cheers,
BDKR
A note on replication - mysql's implementation is master/slave log based which amounts to logging the sql. Not much is going on here in terms of validation.
Interestingly, mysql has added the postgres compatible LIMIT syntax.
Foreign keys in innodb do not compare well with postgresql's, which have referencing actions and are deferrable.
Mysql's query planner is weak - not an issue for most, but when one query involves 10+ tables...damn is it awful. And the explain output is not as helpful as postgres'.
...
(Who hijacked this thread??)
Mysql's query planner is weak - not an issue for most, but when one query involves 10+ tables...damn is it awful. And the explain output is not as helpful as postgres'.
I agree. It is not intuitive at all!
A note on replication - mysql's implementation is master/slave log based which amounts to logging the sql. Not much is going on here in terms of validation.
This is true, but at least it's there.
Now what sounds impressive is the scheme that some of the Postgres replication developers are cooking up right now. A sort of update everywhere scheme. There is a dissertation on the subject (in pdf) written by Bettina Kemme that is very good reading on this topic. A bit much to chew on if you're not really interested, but it's a good read still.
Something else to say about Postgres. It's the better product when you get right down to it, but the replication issue as well as the required learning curve has hurt it's visibility and mind share. When the un-washed masses can understand and create the smallest something from MySQL in a fraction of the time it takes to do the same with Postgres, what do you think they're going to do? However, in using it (MySQL), you soon realize that you're operating as a Light Fighter as opposed to an M1 (tank, that is). There feature set is small and easy to understand. An MP-10 is easier to understand and master than being a tanker.
Now does this sound familiar?
I came to computing on Macs and Windows boxen, but arrived at Linux and will allways prefer Linux. But Mac and Windows were very VERY easy to comprehend. It took a good year of head banging to get Linux figured. This is a process that happens over and over again. Someone gets a computer, digs it, then learns there is something more powerful and elegant. Luke learned that a Light Saber was preferable to a blaster didn't he? But how much time did it take for him to learn how to use it?
Does this sound familiar?
Postgres is much tougher to learn for the myraid of chaps and tossers new to web development and computing in general! You hear it all the time too. What's an FK? What's a view? What's a stored procedure? I've even heard complaints from people who ARE experienced (with something other than MySQL. Sybase, Oracle, for example) DBAs complain about it's (lack of) user friendliness. Then on top of that, users of MySQL are derided in general. What good is that going to do for Postgres. A lot of people saw Mac users for the longest time as intellectual snobs. And it didn't help Mac either.
When you get right down to it, comparison of the two is almost ridiculous. One is an artillery piece and the other is a silenced MP-10. In other words, even though they both kill enemies, they don't compare.
Now As opposed to just slamming MySQL users or usage, look at it as a stepping stone to better understanding and suggest. Derision is counter productive. Just doing it (and I hope a Nike lawyer doesn't contact me over this 
) is the way to go. All of the energy spent here could've gone towards helping the replication team test and develop further.
One more note on the importance of replication. More and more companies of all sizes are realizing just how cost effective a cluster approach in the back end is. When I came to this company, they tasked me with building out the back end. One of the managers(who is now thankfully gone) kept glancing lustful eyes at these huge and exceptionally expensive servers from the likes of IBM, HP, and others. Dinosaurs! It took a week to finally break him of this obsession. Instead, we use commodity (and that's the important word here) hardware in the clusters and simply add more boxen as the need increases. Of course, there are other things that should be done to deal with increased load, but that's it for the most part.
Now with a cluster of db's, your backend can easily survive the loss of one of the dbs. This much we understand. Furthermore, your db performance as a whole can scale better becuase you do things like read from slaves and task certain masters with update/addition/deletion of certain areas of data.
So with more and more people going towards a clustered approach, is it suprising at all that Postgres has missed the boat in this area? Without proven and battle tested replication, it's not on the same planet.
MySQL on the other hand does have it. For someone running a large hosting company that gaurantees 99.9% uptime, how easy is it going to be to provide that at the db level with one box? If this host is seeing tons of load, are the 3rd party postgres replication schemes (not talking about the official Postgres implementation) going to hang?
Replication is the most important thing for Postgres to get worked into their system for inclusion in the Enterprise.
In short, I like 'em both, and they both CAN get the job done. How do I know MySQL can get the job done? Becuase it's getting the job done for us right now and has been for a year now.
Cheers,
BDKR