PostgreSQL vs MySQL with Rails
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.
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.
Robby,
You hit the nail on the head:
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.
bubble
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!
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?
EWR EW
WER
trytr
trytr
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?
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!
Demonstrate a unique new conceptjordan shoesAwq10
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
Surprise! you will get the bag! ed hardy clothing ed hardy clothing ed hardy shirts louis vuitton louis vuitton blog louis vuitton christian louboutin Rosetta stone louis vuitton christian louboutin Rosetta stone
www.airjordanshoesdiscount.com > jordans www.vibram5fs.com/ > vibram www.fivefingersvibramdiscount.com/ > five fingers vibram www.uggaustralianew.com/ > boots shoes www.airjordanshoesdiscount.com > air jordan www.vibram5fs.com/ > five fingers
www.airjordanshoesdiscount.com > jordans www.vibram5fs.com/ > vibram
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
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
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
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
i9 sciphone i9 wifi i9 ciphone i9 i9 phone i9 3g i9 cell phone i9 touch
2-3-lcd-dual-sim-dual-network-standby-quadband-gsm-tv-cell-phone-w-wifi-+-java”>9700 phone 2-4-lcd-dual-sim-dual-network-standby-quadband-gsm-tv-cell-phone-w-wifi-+-java”>9700 cell phone
900-1800-1900mhz-gray”>e66 900-1800-1900mhz-gray”>E66 2.4 900-1800-1900mhz-gray”>E66 Dual SIM 900-1800-1900mhz-gray”>e66 phone
touch-screen-dual-sim-dual-network-standby-quadband-gsm-cell-phone-w-java-black”>Mphone
touch-screen-dual-sim-dual-network-standby-quadband-gsm-cell-phone-w-wifi+java—black”>Mini i9
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.
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.
baidu
baidu
Christian Louboutin Christian Louboutin hermes birkin Hermes handbags moncler moncler herve leger herve leger
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.
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