Read my latest article: Announcing RailsDeveloper (posted Wed, 01 Sep 2010 17:01:00 GMT)

PostgreSQL vs MySQL with Rails

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

39 comments Latest by chunagyi002@hotmail.com Tue, 24 Aug 2010 05:29:26 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.

Subscribe to my RSS feed Enjoying the content? Be sure to subscribe to my RSS feed.
Comments

Leave a response

  1. Avatar
    Ashish Ranjan Tue, 17 Oct 2006 05:40:36 GMT

    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

  2. Avatar
    Daniel Aleksandersen Mon, 30 Jul 2007 11:44:00 GMT

    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!?)

  3. Avatar
    Robby Russell Mon, 30 Jul 2007 14:30:55 GMT Recommend me on Working with Rails

    Daniel,

    PostgreSQL is still our primary database engine for all of our client work. It works well with Ruby on Rails.

  4. Avatar
    Aamer Abbas Sun, 02 Sep 2007 02:54:29 GMT

    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.

  5. Avatar
    Aamer Abbas Sun, 02 Sep 2007 02:54:36 GMT

    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.

  6. Avatar
    Konstantin Gredeskoul Tue, 25 Sep 2007 22:58:46 GMT

    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

  7. Avatar
    flowers to india Mon, 15 Oct 2007 09:10:32 GMT

    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

  8. Avatar
    jan Wed, 28 Nov 2007 12:29:58 GMT

    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

  9. Avatar
    David Lee Mon, 04 Feb 2008 06:35:06 GMT

    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!

  10. Avatar
    Rainsford Tue, 01 Apr 2008 03:20:35 GMT

    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!

  11. Avatar
    Rainsford Tue, 01 Apr 2008 03:20:43 GMT

    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!

  12. Avatar
    Ray Fri, 16 May 2008 20:38:22 GMT

    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.

  13. Avatar
    Bob Wed, 10 Jun 2009 22:24:45 GMT

    Robby,

    You hit the nail on the head:

    "... I try to develop applications under the assumption that it will
    never be the only interface to the data set that it uses."

    If your application is successful the data it accumulates will become valuable in its own right. Once that happens, somebody somewhere will get a bright idea about how to use that data. If you haven’t made the schema’s referential integrity airtight then I feel sorry for you. Other users will break your application’s expectations of how the data should hang together, and good luck ever getting it straightened out.

  14. Avatar
    fart Mon, 15 Jun 2009 22:10:58 GMT

    bubble

  15. Avatar
    abercrombie clothes Thu, 18 Mar 2010 06:34:12 GMT

    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!

  16. Avatar
    laptop adapter china Thu, 13 May 2010 07:09:08 GMT

    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?

  17. Avatar
    air jordan IX Mon, 07 Jun 2010 03:41:24 GMT

    EWR EW

  18. Avatar
    air jordan 2 Mon, 07 Jun 2010 03:41:37 GMT

    WER

  19. Avatar
    2010 germany 16 lahm Wed, 09 Jun 2010 02:57:36 GMT

    trytr

  20. Avatar
    2010 germany 16 lahm Wed, 09 Jun 2010 02:59:22 GMT

    trytr

  21. Avatar
    designer handbags reviews Sat, 12 Jun 2010 10:00:22 GMT

    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?

  22. Avatar
    christian louboutin shoes Fri, 18 Jun 2010 07:14:18 GMT

    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!

  23. Avatar
    air jordan 11 Wed, 30 Jun 2010 08:30:50 GMT

    Demonstrate a unique new conceptjordan shoesAwq10

  24. Avatar
    Louis Vuitton Wallets Wed, 21 Jul 2010 15:51:04 GMT

    Louis Vuitton 3 Card Bill Holder M92996 Louis Vuitton 3 Card Bill Holder Louis Vuitton Six Card And Bill Holder M92074 Louis Vuitton Six Card And Bill Holder Louis Vuitton Pince Wallet M95454 Louis Vuitton Pince Wallet Louis Vuitton Porte Valeurs Organizer M92999 Louis Vuitton Porte Valeurs Organizer Louis Vuitton Porte Valeurs Organizer M97020 Louis Vuitton Porte Valeurs Organizer Louis Vuitton Utah Compact Wallet M92575 Louis Vuitton Utah Compact Wallet Louis Vuitton Utah Compact Wallet M97021 Louis Vuitton Utah Compact Wallet Louis Vuitton Utah Zippy Organizer M97025 Louis Vuitton Utah Zippy Organizer Louis Vuitton Utah Zippy Organizer M97026 Louis Vuitton Utah Zippy Organizer

    Louis Vuitton Compact Wallet M60167 Louis Vuitton Compact Wallet Louis Vuitton Long Wallet M60168 Louis Vuitton Long Wallet Louis Vuitton 6 Key Holder M60165 Louis Vuitton 6 Key Holder Louis Vuitton Neo Card Holder M60166 Louis Vuitton Neo Card Holder

  25. Avatar
    http://www.52louis-vuitton.com Thu, 22 Jul 2010 04:22:04 GMT
  26. Avatar
    vibram fivefingers Thu, 29 Jul 2010 02:24:22 GMT
  27. Avatar
    five fingers Thu, 29 Jul 2010 09:24:56 GMT
  28. Avatar
    wholesale nfl jerseys Thu, 05 Aug 2010 05:42:02 GMT

    Just want to say what a great blog you got here!information is very clear. good and understandable explanation. super-topics. Thank you for sharing a very nice web site

  29. Avatar
    Vibram Five Fingers Fri, 06 Aug 2010 05:45:07 GMT

    Well said. I never thought I would agree with this opinion, but I’m starting to view things from a different view. I have to research more on this as it seems very interesting. One thing I don’t understand though is how everything is related together. Christian Louboutin Discount Jimmy Choo Christian Louboutin Mary Janes Christian Louboutin Pumps vibram bikila Vibram Five Fingers KSO Vibram Five Fingers Classic Vibram Five Fingers Sprint

  30. Avatar
    bag supplier Mon, 09 Aug 2010 03:47:57 GMT

    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 somethi

  31. Avatar
    http://www.bestretroshoes.com Tue, 10 Aug 2010 09:42:20 GMT
    Wholesale Women Parda shoes,Wholesale Women Parda shoes
    Wholesale Women Timberlands,Wholesale Women Timberlands
    air jordan force 9,air jordan force 9
    air jordan force 20,air jordan force 20
    air jordan spiz ike,air jordan spiz ike
    Wholesale Nike air Max,Wholesale Nike air Max
    Air Jordan 1,Air Jordan 1
    Air Jordan 2,Air Jordan 2
    Air Jordan 3,Air Jordan 3
  32. Avatar
    wifi i9 Wed, 11 Aug 2010 02:19:00 GMT
  33. Avatar
    Coach Handbags Wed, 11 Aug 2010 03:44:14 GMT

    We are a professional exporter and wholesaler of brand fashion products,Coach Poppy Op Art Glam 13826 Black Coach Poppy Op Art Glam 13826 Black White Coach Poppy Signature Sateen Pocket HOBO 14570 Apricot Coach Poppy Signature Spotlight Tote 13843 Apricot Coach Poppy Signature Spotlight Tote 13843 Black Coach Poppy Signature Spotlight Tote 13843 BlueAll products have good quality,fast and safe delivery without shipping fee. Our primary goal is to meet our clients’ requirement and establish mutually pleasant business relationships with you.If you are interested, please do not hesitate to contact us.

  34. Avatar
    Shure microphones Sat, 14 Aug 2010 08:57:12 GMT

    We are a professional exporter and wholesaler of brand fashion products Sennheiser EW-155G2 Sennheiser EW-100G2 nnheiser EW-122G2 Sennheiser EW-135G2 Sennheiser E-845S All products have good quality,fast and safe delivery without shipping fee. Our primary goal is to meet our clients’ requirement and establish mutually pleasant business relationships with you.If you are interested, please do not hesitate to contact us.

  35. Avatar
    herve leger Mon, 23 Aug 2010 01:58:27 GMT
  36. Avatar
    herve leger Mon, 23 Aug 2010 02:00:14 GMT
  37. Avatar
    herve leger Mon, 23 Aug 2010 02:01:14 GMT
  38. Avatar
    sfds Mon, 23 Aug 2010 07:42:22 GMT

    wholesale nike shoesShop a great selection of authentic Nike shoes&Nike Air Max with reasonable price for the entire families at nike-shoes-max.com.nike shoes 100% quality guaranteed and smooth customer service.UGG Women’s Classic Cardy Boots 5819 are available with colorful knit uppers (composed of a wool blend) and a sheepskin sock liner for extra comfort.ugg boots It is detailed with three oversized wood buttons, allowing it to be styled buttoned up, australia uggslouched down, slightly unbuttoned, or completely cuffed down. They have a light and flexible EVA outsole along with a suede heel guard provides durable wear all season long. That is why it is one of several styles that have been all time favorites with women.

  39. Avatar
    chunagyi002@hotmail.com Tue, 24 Aug 2010 05:29:26 GMT

    Any member of your group can post to your trip blog. This is a great way to share information with your team and your supporters.power strip |booster cable | tow rope |ratchet tiedown

Share your thoughts... (really...I want to hear them)

Comments