Oldmoe's blog

101 Reasons Why PostgreSQL is a better fit for Rails than MySQL

Written in

by

oldmoe

, , ,

1 – Indexing Support

MySQL cannot utilize more than one index per query. I believe this is worth repeating: MySQL CANNOT UTILIZE MORE THAN ONE INDEX PER QUERY. Wait till your tables get large enough and this will surely hit you. OTOH PostgreSQL can use multiple indices per query which come real handy.

2 – Full Text Indexing Support

MySQL can do full text indexing on MyISAM tables only, those working with InnoDB tables are out if luck. PostgreSQL has very advanced full text indexing capabilities wich enable you to control the tiniest details down to the stemming strategy.

3 – Asynchronous Interface

MySQL drivers are very unfriendly to the Ruby interpreter. Once a command is issued they take over until they come back with results. PostgreSQL sports a completely asynchronous interface where you can send queries to the database and then tend to other matters while the query is being processed by the server. The good news is that an Async ActiveRecord adapter for MySQL is being developed right now, as part of the rapidly growing NeverBlock library.

4 – Ruby Threading Aware

PostgreSQL dirvers enable the Ruby thread scheduler while IO requests are being processed (a nice side effect of the async interface). Which makes it much better suited for multithreaded Rails apps.

5 – Multistatements Per Query

Both MySQL and PostgreSQL support sending multiple statements separated by semi colons at once. But the returning result will be that of the last statement in the group. Now did you know that by using the async interface you can send multiple queries at once and then get back the results, one by one? One of the coolest features of the coming ActiveRecord (and Sequel btw) adapter is it’s support for queuing queries to be consumed by a pool of connections. A trick we are contemplating working on is to group consequent selects together and send them in a single request to PostgreSQL and then later extract the results associated with each one of them. This is still very theoretical but should be verified soon.

Now that the 0b101 reasons are told I rest my case.

Tags

24 responses to “101 Reasons Why PostgreSQL is a better fit for Rails than MySQL”

  1. Roger Pack Avatar
    Roger Pack

    Regarding this–I’m pretty sure that it’s a complicated question with no easy answer.I look forward to hearing of mysql for NeverBlock [asymy? [2]].I think you may be right–postgres users will benefit more from the upcoming multi threaded rails [3], and, hopefully, with Asynchronous MySql [neverblock or what not] then MySql users can similarly benefit.Thanks for your work on this.Take care.-=R[2] http://github.com/tqbf/asymy/tree/master%5B3%5D http://weblog.rubyonrails.org/2008/8/16/josh-peek-officially-joins-the-rails-core

    Reply
  2. Anonymous Avatar
    Anonymous

    Except active record is designed around MySQL in ways which might be hard to unpick: “Active Record’s find family of functions. It’s a horrible hack, for instance, they support the :group clause, which has semantics (“return a collectionof groups of things”) incompatible with find’s base semantics (“return a collection of things”). Rails answer? It implicitly relies on MySQL’s retarded interpretation of SQL and the fact that given a table with two columns, id and colour, it will silently interpret “SELECT * FROM table GROUP BY colour” as “SELECT FIRST(id), colour FROM table GROUP BY colour”. End result? A valid combination of clauses in AR will generate incorrect SQL Postgres will (correctly) choke on.”( from http://groups.google.com/group/comp.lang.lisp/msg/f2c33661b80ba302 )BTW, for 2), you may want to check out the Sphinx storage engine – http://www.sphinxsearch.com/ – it might help you out.

    Reply
  3. Anonymous Avatar
    Anonymous

    How are you supposed to be able to program if you can’t even count? Those were not 101 reasons, there were five of them.5 != 101

    Reply
  4. Joe Grossberg Avatar
    Joe Grossberg

    Anonymous:Read the last line of his post — he was making a joke. It is 101, just not in base-10.

    Reply
  5. Anonymous Avatar
    Anonymous

    101 in binary == 5.

    Reply
  6. Anonymous Avatar
    Anonymous

    You really need to work on the wording of: “MySQL CANNOT UTILIZE MORE THAN ONE INDEX PER QUERY.” MySQL certainly can use one index per JOIN, plus one for the WHERE clause. With your delivery of that and its prominence as the #1 reason, I almost skipped your post entirely. I try not to waste time on stupid assholes.

    Reply
  7. oldmoe Avatar
    oldmoe

    @roger, sure once Asymy matures it will be supported, I actually have a working prototype.@anonymous_with_no_time_to_waste, sorry I pissed you off, not intended, now you really need to work on the words you use generally

    Reply
  8. que0x Avatar
    que0x

    well written !

    Reply
  9. Mr. Interweb Avatar
    Mr. Interweb

    Wow. That Anonymous guy who’s time must be more precious than gold its self, and who takes great offense when his precious time is “wasted”, sure is a the rear end of a donkey. My mother always told me “If you can’t say something nice, don’t say anything at all.” I am glad you wrote this post as I have been torn for reasons to give Postgres a try with my Rails projects. I have been so comfortable with MySQL for years now, but I do need to break out of my comfort zone every once in a while.

    Reply
  10. Ericson Smith Avatar
    Ericson Smith

    We’ve been using Postgresql for many years now. From big 400 GB databases to social networks with millions of unique users per month.I never understood why people were so consumed with using MySQL when there are such an embarrassment of riches with Postgresql.

    Reply
  11. Thomas Hurst Avatar
    Thomas Hurst

    MySQL 5 added support for index merging, allowing it to use multiple indexes per table.

    Reply
  12. Gene Tani Avatar
    Gene Tani

    well you got hacker-news’d and i left a commennt on tsearch (#2) there

    Reply
  13. Gene Tani Avatar
    Gene Tani

    oops forgot the all-important URL. Very sloppyhttp://news.ycombinator.com/item?id=281676

    Reply
  14. Anonymous Avatar
    Anonymous

    Douchebag counting scheme not appreciated.

    Reply
  15. Hongli Lai Avatar
    Hongli Lai

    PostgreSQL has one disadvantage compared to MySQL: it doesn’t support case-insensitive varchar columns. This means I’ll have to manually keep a lower-case index of usernames. If I use functions such as LOWER() then it will skip the index.

    Reply
  16. Aaron Avatar
    Aaron

    Hongli -If you declare your index using a function, then postgres will pick it up. That is, build an index on lower(username).cheers

    Reply
  17. xzilla Avatar
    xzilla

    actually postgresql has a pretty extensable type system, so if you need a specific type, you can generally make your own. case in point, the citext project implements a case insensitive text type. the original code is available at http://pgfoundry.org/projects/citext/, though you can get a more enhanced/updated version from https://svn.kineticode.com/citext/trunk/ (that code is the basis for what will be an include module shipped with 8.4)

    Reply
  18. Roger Pack Avatar
    Roger Pack

    I think this could be titled “101 reasons why it is better for large rails projects’ than for rails in general. I prefer sqlite for small projects, because of the setup simplicity. And others may end up preferring oracle for the very high end. So I guess postgres fits well for ‘large’ rails apps.-=R

    Reply
  19. oldmoe Avatar
    oldmoe

    I totally agree, I myself use MySQL on many projects some of which are of considerable size. I just needed to highlight the short comings given the hype that is surrounding Rails being threadsafe. People need to know that mysql (and sqlite for that matter) wont help them in that area unless they’re using jRuby.

    Reply
  20. José Valim Avatar
    José Valim

    Good to know!Take a close look in PostgreSQL within Ruby is one of next tasks for sure!I’m subscribing to your feed, hoping that I will read more news (including Asynchronous MySql news) soon! =)

    Reply
  21. Rasputnik Avatar
    Rasputnik

    Rails may not be multithreaded yet, but Merb currently is. Not that Ruby (or Rubinius) are especially great at multithreading, but JRuby is ‘ready’ enough now that these things start to matter.A lot of the quirks of PostgreSQL support in Rails are due to DHH preferring MySQL, maybe because he sees the DB as a dumb datastore ( http://www.robbyonrails.com/articles/2005/08/18/active-record-i-3-u-but-i-still-trust-my-database-server-a-tiny-bit-more ) ?Switch to a decent ORM layer (Datamapper or Sequel) and you no longer have the bias.

    Reply
  22. Roger Pack Avatar
    Roger Pack

    Looks like Mohammed has attacked numbers 3 and 4 himself with his release of the mysqlplus driver for Ruby.Nice work!-=R

    Reply
  23. Carlos Avatar
    Carlos

    Good Job! 🙂

    Reply
  24. Hongli Lai Avatar
    Hongli Lai

    After finding out that MySQL apparently doesn’t support nested transactions (!!!!) we decided to switch to PostgreSQL. It seems to be a bit slower but hopefully the other benefits will outweigh this performance penalty in the future.Also, PostgreSQL has many little annoyances compared to MySQL. For example, its primary key sequences can get out of sync after restoring a database from a dump (including from the Rails Rake tasks) and I’ll have to manually fix them.

    Reply

Leave a reply to Gene Tani Cancel reply