The major reason I chose postgres for our apps was consistency Firstly, consistency of use: Pop quiz: In which version of mysql did subselects start to work? Yes, you can usually avoid subselect, but they're certainly not triggers or stored procedures. Pop quiz: What are the rules for using indexes in mysql?
And this, from the mysql <= 4.1 manual (http://dev.mysql.com/doc/refman/4.1/en/join.html), sorry for the big paste: "Note that several changes in join processing were made in MySQL 5.0.12 to make MySQL more compliant with standard SQL. These changes include the ability to handle nested joins (including outer joins) according to the standard. If a nested join returns results that are not what you expect, please consider upgrading to MySQL 5.0. Further details about the changes in join processing can be found at JOIN Syntax. You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause. There are exceptions to this rule. Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11." The mysql team made big *interface* (ie SQL) design mistakes in the beginning, which they're rectifying over time. In the beginning it was a feisty reaction to the stuffy, dogmatic SQL adherents. Now they seem to be walking that attitude back, somewhat. Note from the version number that these interface changes were made *between patch level versions*! (Incidentally, this is the main reason that I think of mysql as a toy database. I'm not opposed to toys in production as such, but I'd rather their fun came from areas other than the interface against which I program) I couldn't say definitively that this hasn't ever happened in postgres, but to me it seems that they doggedly adhered to SQL standards (perhaps, say, at the expense of performance) which then stayed rather static. The internals behind a stable interface can always be optimised. The second big reason is consistency of ownership. Mysql has recently been owned by Mysql ab, then its main storage engine (innodb) was bought by Oracle. Then sun bought mysql, now sun is owned by Oracle. I mean, good on 'em for creating a disruptive and desirable product, but it doesn't inspire me to base my own business on their shifting sands. Realistically, we're moving slowly but surely *away* from sql databases to JSONny key value stores. If you're talking about a database that's a better YAGNI fit for web apps then couch, mongo, redis and their ilk are it. But if I'm going to use a relational database, I want it to hop out of the way as much as possible. This is not something that mysql readily does. :lachie On Mon, Jan 25, 2010 at 11:44 PM, Jason Stirk <[email protected]> wrote: > 2010/1/25 Xavier Shay <[email protected]> >> >> On 25/01/10 12:35 PM, Jason Stirk wrote: >>> >>> ... Deployment database? >>> MySQL, recently played a bit with Postgres (although I'm still >>> unimpressed) >> >> This is contrary to most opinion I've heard, so I'm interested in hearing >> more. > > What follows is just (ranty) my take on MySQL/Postgres. I'm more than happy > to have the record set straight by a Postgres guru... > > I'm strongly of the opinion that, unless you're admining the installation, > things like Rails and ActiveRecord are going to hide just about all the > differences between the two anyway. That's, you know, the idea of them... > > I guess part of my preference for MySQL comes from my familiarity with it: > I've admined and used installations of it for about 8 years, as opposed to > about 6 months on Postgres. However, permit me to throw caution to the wind > and rant anyway! > > Generally, my thoughts are that MySQL is postfix to Postgres's sendmail. > Sure, Postgres has all these extra features, but my experience of it is > crazy arcane syntax, and a mishmash of CLI and in-client commands. > > For example, what's with these crazy "\d" commands just to do simple things > like showing a list of databases or table? "SHOW DATABASES" or "SHOW TABLES" > might be longer to type, but they're trivial to remember. (I'm prepared to > have my ass handed to me here, explaining a simple, in client way of doing > this in Postgres... Please, hand away...) > > I was also very unimpressed with the way postgres created and managed DBs > and users (createdb, and friends). The idea of CLI tools (like "createdb") > to manipulate the state within a daemon process does give me the creeps more > than a little... > > Many folks give the excuse that MySQL is a "toy" database, as the older > versions lacked stored procedures, triggers and the like. Whilst that's been > changed recently, realistically, I'm prepared to call bullshit when this > justification comes up in the context of web dev: YAGNI anyway... > > In fact, I'd be very interested to talk with any Ruby web developer who's > ever needed stored procedures, triggers, or anything like that. I'm > genuinely interested to know what situation could have called for them in > the web world, and how they actually benefited your project. > > That's not to say I think they're unnecessary on the whole - there are a > metric shitload of uses where they're essential to preserve DB integrity, or > speed things up when load dictates, but in the web world, I'd be very > surprised if you're grabbing these tools early on in the game. (Unless > you're writing a web console for a nuke reactor, or rebuilding the ASX on > Rails...) > > To be honest, these advanced features are probably going to be a pain to > work with if you're using a ORM layer anyway, whether it's ActiveRecord or > something else. > > (Not to mention that the idea of code in the DB layer scares the crap out of > me...) > > (Offtopic -1: Hell, do things like foreign key constraints even work > reliably in ActiveRecord yet?!?) > > Configuration wise, MySQL is a snap to work with, and it's all pretty damn > logical how it all hangs together. If you want a low memory deployment for > dev, or a dedicated DB server, MySQL can handle it with just a few tweaks. > > Sure, there's a bit of thought work involved when looking at the pros and > cons of each engine for the tables, but even that's pretty simple, and only > going to happen when you define your schema. Plus, that is less of a > question now that awesome tools like Sphinx reduce the need to do in DB > fulltext search (so why the hell would you use MyISAM? Unless you really, > really dig table-level locking!) > > Finally, when you _do_ need to outgrow a single DB installation, you still > have the ability to look at replication or the like. I can't claim to have > set it up myself, but I've read up on it, and it really doesn't look _that_ > hard. I believe that multi-master replication is impossible/very hard out of > the box with MySQL, but if you've got enough load to need to deal with that > kind of issue, good luck to you! > > If you're interested in more advanced topics about MySQL, check out > O'reilly's "High Performance MySQL" by Jeremy Zawodny (who moved all of > Yahoo! Finance over to MySQL from BDB in 2000/2001). It's probably a tad > dated now (6 years old) but is well worth the read for some interesting > ideas. > > Thus concludes my rant for the evening. > > J > > -- > You received this message because you are subscribed to the Google Groups > "Ruby or Rails Oceania" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/rails-oceania?hl=en. > -- You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rails-oceania?hl=en.
