I haven't come across the idea of transactional DDL before, and I can see it being a huge win once you're trying to migrate with large production databases.
A few other points from other posts: * PostGIS Yup, PostGIS is where it's at AFAIK. MySQL does have some GIS-related extensions, but I've never spoken with anyone who's worked with them to know what they're like, and definitely nobody I've spoken with has used PostGIS and the MySQL GIS libs side by side to give a decent comparison. (They've all grabbed PostGIS and gone with that...) * Postgres + SELinux That's pretty cool, but (without RTFA closely) I'm unsure of the benefit of it over a standard user-based system. I imagine banks/financial have a great use for it though (like SP, et al). * Consistent, Transaction-Safe DB Dump Did "mysqldump --single-transaction" not produce consistent dumps in a transaction safe manner? (Available since <=4.1) Granted, it doesn't work on MyISAM tables (because they don't support transactions at all), but my understanding was that the dump would have the same protection any normal transaction would. An interesting discussion - I'm learning heaps about Postgres (and a few things about MySQL), which is very much appreciated. J 2010/1/26 Steve Hayes <[email protected]> > Postgres has transactional DDL. Last time I looked MySQL didn't - that was > enough for me. > > Simon Harris once told me that when he was maintaining the RedhillOnRails > database plugins 90% of the reported defects (he actually said "all", but > there might have been some exaggeration) were related to MySQL > idiosyncracies. Of course that doesn't affect everyone. > > > On 26/01/2010, at 10:32 AM, Lachie wrote: > > 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]<rails-oceania%[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]<rails-oceania%[email protected]> >> . >> For more options, visit this group at >> http://groups.google.com/group/rails-oceania?hl=en. >> >> > ----- > Steve Hayes > > Cogent Consulting > http://www.cogentconsulting.com.au > http://iridescenturchin.blogspot.com/ > twitter: steve_hayes > GTalk: steve.e.hayes > Yahoo: steve_e_hayes > Mobile: 0403 902 431 > SkypeIn: +61 3 9005 6695 > > Measure your Ruby code quality: http://www.codeyak.com > > Have more things to do than you have time to do them? Use > http://www.runwayapp.com. > Runway - where your life takes off. > > > > > > > > > > > > -- > 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]<rails-oceania%[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.
