Read my latest article: 8 things I look for in a Ruby on Rails app (posted Thu, 06 Jul 2017 17:59:00 GMT)

PostgreSQL vs MySQL with Rails

Posted by Sun, 19 Jun 2005 00:44:00 GMT

Every once in a while, there is a short debate about using MySQL or PostgreSQL with Ruby on Rails as a database server. If you know me at all, you’ll know that I am a strong proponent of using PostgreSQL. Putting some logic in the database is something that my last few jobs have completely trained me to do.

For example, I try to develop applications under the assumption that it will never be the only interface to the data set that it uses. In a way, I use the database as a part of the model and don’t put the reliance on the model in my Rails application.

For example, take the following example of updating a bunch of records that meet a certain requirement.

Items.find(:all, :conditions => "type_id='2'") do |item| 
    item.x = "foo" 
    item.save 
end

Is this method quicker than running a simple UPDATE query on the database? With a smaller data set, this might not be big enough of a concern. But, what if your database has a million rows that meets this criteria? Does it make sense to find them all first and then loop through the recordset one million times and call one million updates? A few problems might occur, one big potential issue is that it is likely that your application might go beyond the timeout of your server and there is no rollback with this transaction.

So, what is the best Rails-way to solve this problem?

If the request takes too long and times out, how do you ROLLBACK your updates automatically? I know how to do this with PostgreSQL (quite simply), but am curious as to how this is addressed by MySQL proponents.

Let’s take another scenario. Perhaps you have a table called foobars. Every time you INSERT or UPDATE this table, you want to add a record into table foobarlogs that shows what was added or what fields were specifically changed. With PostgreSQL, you could build a trigger that compares the existing record with the new values and then adds the differences into the foobarlogs table. This might not be something that people encounter, but for some systems, it’s vital that you know when something happened, by whom, and what exactly happened.

Step forward into the future. Client needs a new interface built for desktop usage. Yes, you could use Ruby with wxWidgets and build a nice multi-platform application. In your code, you deal with the same database. With your trigger in place, you don’t need to do anything but call it like you did with Rails.

Yes, MySQL is getting advanced features, but its still a while before you’re going to get to use pl/Ruby for building your database functions.

Wouldn’t this follow more along the lines of the DRY principal than doing this in the Rails code?

- A curious PostgreSQL-fan

PostgreSQL, all the features you need next year, available last year.

On a side note, here is a good explanation of how PostgreSQL is useful for certain application requirements.

Get help with your Rails project

comments powered by Disqus