Oh, and additionally Postgres 8.4 has window functions which means that doing any queries on large time series datasets becomes possible. My current job involves analysing lots of stock market data. While it's *possible* to _express_ the queries I need without window functions, it's impossible for them to run before the universe ends / in the memory we have on the system. The query optimizers just aren't smart enough otherwise. (essentially we had to do self joins over time on a table - when there are a million rows in the table, you're looking at the size of the intermediate join being 1 million squared).
Postgres rocks :) 2010/1/26 James Sadler <[email protected]>: > Transactional DDL is definitely a big win for Postgres. It has saved > me countless hours of undoing botched MySQL migrations by hand. > > The second big win is that Postgres doesn't silently truncate text > columns like MySQL does. Either a record inserts/updates entirely or > it doesn't at all. Postgres is simply more robust in this regard. > (and there may be options to enable this behaviour in MySQL, but they > are not enabled by default). > > Thirdly, my gut feeling is that MySQL just isn't that well engineered > - to me it's like the IE 6 of the database world. Flaky and hacky. > When I last used it (a couple of years ago at Mooter) we had a single > admin database that was replicated out to our servers. Every now and > again we would audit the slaves and every single one would have > different row counts to the master. No errors, no warnings, just > every single one of them in a different state to the master by a few > dozen rows out of millions. That may not be everyone elses > experience, but it was enough to put me off using it ever again. > > 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]. >>>> 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. >>> >> >> ----- >> 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]. >> For more options, visit this group at >> http://groups.google.com/group/rails-oceania?hl=en. >> >> > > > > -- > James > -- James -- 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.
