PostgreSQL vs MySQL with Rails
12 comments Latest by Ray Fri, 16 May 2008 20:38:22 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.
Enjoying the content? Be sure to subscribe to my RSS feed.
yup. I am myself a die-hard fan of postgres, and the reason being the same which you mentioned here. Now the performance difference is no more there between MySql and Postgres (almost). Postgres is available now natively on Windows too, that too free. Last and foremost, there is extension which makes postgres almost look like oracle from an application program point of view. Now, what one would else want? bye :-) Ashish Ranjan ashishwave@yahoo.com
Thanks for the help on picking database, Robby! I will trust your opinion.
By the way: I hope this article is not too out of date? (It is just two years old. What could have changed!?)
Daniel,
PostgreSQL is still our primary database engine for all of our client work. It works well with Ruby on Rails.
Good article. I agree that in a multi-application environment, putting restrictions and rules at the database level makes more sense and is more DRY-friendly.
However, I feel like there are a couple of points you missed:
1.) The whole idea behind ActiveRecord is to allow the programmer to do Ruby rather than SQL. Yes, SQL is necessary and by no means evil, but putting too many things at the database level leads to less maintainable code (at least in my opinion).
2.) Putting rules at the database level is good if multiple applications share the same database. However, many people are less concerned about sharing a database among different applications and are more concerned with how easy it is to switch to a different database vendor. For example, what if I need to switch from Postgres to Oracle? In this case, keeping logic at the application layer makes it more portable.
Good article. I agree that in a multi-application environment, putting restrictions and rules at the database level makes more sense and is more DRY-friendly.
However, I feel like there are a couple of points you missed:
1.) The whole idea behind ActiveRecord is to allow the programmer to do Ruby rather than SQL. Yes, SQL is necessary and by no means evil, but putting too many things at the database level leads to less maintainable code (at least in my opinion).
2.) Putting rules at the database level is good if multiple applications share the same database. However, many people are less concerned about sharing a database among different applications and are more concerned with how easy it is to switch to a different database vendor. For example, what if I need to switch from Postgres to Oracle? In this case, keeping logic at the application layer makes it more portable.
Robby, thanks for this piece. I too love PostgreSQL and use it in production in third project now.
I’ve written a small piece with some additional hints on how to use constraints and partial indexes… For anyone interested, please see below.
On Ruby on Rails, PostgreSQL and Acts as Paranoid
Diwali festival of lights celebrated in india on 9th November 2007 to gift your loved ones click on the below sites, We are online florist for delivery of flowers and gifts to 250 cities in India and 23 countries across the world.Send flowers to Pune, gifts, cakes, flower delivery to Pune, Mumbai, India, Bangalore, Chennai, Hyderabad, Mumbai, Delhi, Coimbatore, Goa, Pondicherry, Ahmedabad, Calcutta, Cochin, Trivandrum, Vijayawada, Madurai and anywhere across the world. http://www.flowersfloristsgifts.com http://www.flowersnfloristsindia.com/ http://www.flowersnflorists.com/ http://www.floristonlineindia..com/ http://www.onlinefloristindia.com http://www.cakesflowerschocolates.com http://www.colourfulflowersindia.com http://www.indiafloristshop.com/ http://www.giftgiftstoindia.com http://www.floristindianetwork.com/ http://www.bloomonlineindia.com/ http://www.indiacakesonline.com http://www.giftflowersindia.com http://www.flowerstochennai.com http://www.indiaflowerwala.com http://www.bangaloregiftsflower.com/ http://www.sendflowerspunjab.com http://www.sendfreshflowersindia.com/ http://www.allindiagift.com/ http://www.indiachocolatedelivery.com/ http://www.keralaflorist.com/ http://www.sendvalentineflowers.com http://www.punegiftsflowers.com http://www.puneflowerdelivery.com http://www.hyderabadgiftsflowers.com http://www.goaflorist.com http://www.floristbangalore.com http://www.delhigiftsflowers.com http://www.delhiflorists.com http://www.mumbaiflorist.com http://www.mumbaigiftsflowers.com http://www.chennaigiftsflowers.com http://www.globalflowerdelivery.com http://www.indiafloristonline.com http://www.indianonlineflorist.com http://www.giftsvalentine.com http://www.sendvalentinegifts.com http://www.indiacakedelivery.com http://www.aindiaflorist.com
Hi Robby, I have an existing PostgreSQL database that i need now to access web wise. I’ve chosen Ruby on Rails as a neat Framework to get me up and running quickly.
The problem i’m coming across is the Database schema. I don’t know what constraints Ruby needs to operate. When i do a simple scaffold in my controller, list functtion works, but the create one fails because i do not have an id_seq column. Is this id column essential?
I guess what i’m looking for is how to import my existing postgreSQL db to Ruby on rails. Any help is appreciated, thank you in advance. A new-comer, jan
I am a PostgreSQL convert!
After using MySQL because of its default support in rails, I began to see the light: MySQL is only faster than PostgreSQL using MyISAM; PostgreSQL knocks the socks off of MySQL everywhere else through its advanced joins, indexing, and memory configurability.
Can’t wait for PostgreSQL 8.3 to come out (tomorrow – Feb 4th)!
I wrote down a lot of the stuff I found out in WikiVS: MySQL vs PostgreSQL and hopefully it’ll help someone else.
Also, feel free to correct any mistakes I’ve made!
Thanks David for the link and comparison guide it definitely is complete and shined the light on using Postgres. I am a devote Postgres user now!
Thanks David for the link and comparison guide it definitely is complete and shined the light on using Postgres. I am a devote Postgres user now!
I wonder if there’s some way Rails could use the capabilities of Postgres to put the “application layer logic” into the database? So to the developer, Rails was doing all the work, but if they wanted to use the Postgres database Rails generated with a different application, the schema would already be as solid as your Rails model. It just seems that as long as Rails is doing all the work to interface with the database, it should be using that database to it’s full capacity.