# Rails Benchmarks with PostgreSQL and MySQL

**URL:** <https://www.ruby-forum.com/t/rails-benchmarks-with-postgresql-and-mysql/114777>\
**Category:** Rails\
**Created:** [September 17, 2007, 1:25pm UTC](https://www.ruby-forum.com/t/rails-benchmarks-with-postgresql-and-mysql/114777 "2007-09-17T13:25:40Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mwalsh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/91b2a8/32.png) [@mwalsh](https://www.ruby-forum.com/u/mwalsh)\
**Post date:** [September 17, 2007, 1:25pm UTC](https://www.ruby-forum.com/t/rails-benchmarks-with-postgresql-and-mysql/114777/1 "2007-09-17T13:25:40Z")

</div>

Hello,

I did some simple benchmarking tests on a PostgreSQL database and  
MySQL database. This isn’t meant to prove that one is better than the  
other, but is just an experiment. The tests were run on my laptop with  
the database hosted on an idle quad-core Xeon server accessed over a  
LAN (latency is pretty low, \< 1ms). Not much extra configuration and  
tuning has been done on these databases so I’m sure they can both go  
faster under various circumstances. I didn’t spend a lot of time on  
this so I’m sure a lot of different optimizations can be done but it  
would be interesting to get feedback from people with ideas as to how  
I can get my Rails apps to perform faster. I’d be happy to re-run the  
benchmarks with new ideas. Some of my conclusions are at the bottom.  
If you have more to add or details to fill in, please do so. Thanks.

Benchmarking key:  
“Rails” indicates no optimization, just a basic Rails app with code to  
create the records looking like:

users.each do |user|  
User.create(user)  
end

“Rails with transaction” indicates the exact same Rails app except  
code creating the records looking like:

User.transaction do  
users.each do |user|  
User.create(user)  
end  
end

“Ruby” indicates using the specified DB adapter and the code to create  
the records looking like:

users.each do |user|  
db.query(“INSERT INTO users(name, address, city, state, zip,  
country, phone, email) VALUES(’#{user[:name]}’, ‘#{user[:address]}’,  
‘#{user[:city]}’, ‘#{user[:state]}’, ‘#{user[:zip]}’,  
‘#{user[:country]}’, ‘#{user[:phone]}’, ‘#{user[:email]}’)”)  
end

#### 

Creating 10000 records on a table with 1 int(11) column and 8  
varchar(255) columns, time measured in seconds:

MySQL (InnoDB engine)  
Rails: 255.546999931335  
Rails with transaction: 35.875  
Ruby: 109.18799996376

MySQL (MyISAM engine)  
Rails: 88.6099998950958  
Rails with transaction: 51.3910000324249  
Ruby: 1.2350001335144

PostgreSQL  
Rails: 260.375  
Rails with transaction: 144.546999931335  
Ruby: 36.2340002059937

#### 

Conclusions:

1. Transactions are good. Use them when you can.
2. MyISAM is usually faster than InnoDB, and sometimes WAY faster in  
the case of the simple Ruby script (you read that right, 1.235  
seconds), but certainly not when transactions come into play.
3. MySQL out of the box is often faster than PostgreSQL out of the  
box. I’ve heard, however, that with tuning and multiple cores,  
PostgreSQL has a marked advantage.

---

<div class="post-metadata">

**Author:** ![mwalsh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/91b2a8/32.png) [@mwalsh](https://www.ruby-forum.com/u/mwalsh)\
**Post date:** [September 25, 2007, 11:04pm UTC](https://www.ruby-forum.com/t/rails-benchmarks-with-postgresql-and-mysql/114777/2 "2007-09-25T23:04:06Z")

</div>

On Sep 13, 2007, at 1:59 PM, Matt W. wrote:

> 1. MySQL out of the box is often faster than PostgreSQL out of the  
> box. I’ve heard, however, that with tuning and multiple cores,  
> PostgreSQL has a marked advantage.

In my tests, PostgreSQL starts to pass MySQL as your queries grow  
more complicated and your concurrent load increases. A lot depends  
on application.

-faisal

---

<div class="post-metadata">

**Author:** ![mwalsh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/91b2a8/32.png) [@mwalsh](https://www.ruby-forum.com/u/mwalsh)\
**Post date:** [September 25, 2007, 11:04pm UTC](https://www.ruby-forum.com/t/rails-benchmarks-with-postgresql-and-mysql/114777/3 "2007-09-25T23:04:10Z")

</div>

Rather than quoting feature lists and performance figures that could  
be questioned endlessly, how about this approach?

Ask whoever’s going to fill the DBA role for your project, and go with  
their recommendation.

I’m not a fulltime DBA, but I play one sometimes as the situation  
arises. If you were my customer and came to me and asked “I’ve got a  
startup software project, and I can’t decide whether I should use  
Postgres or MySQL. Give me the answer, oh anointed database expert  
and wise consultant”, this would be the gist of my response:

Go with Postgres.

My reasons:

- I know Postgres much better than I know MySQL. In particular, I  
know that the out of the box config is crap, and I know how to quickly  
apply some generic tuning fixes that’ll generally make it zing. If it  
still works badly for your application, I know how to go about  
tracking down the cause and solving it
- for just about any question involving the functionality of Postgres,  
I know how to find an answer. It may not be the best answer, but at  
least I’ll be able to give you an answer
- I know how to maintain Postgres, how to keep it running, how to do  
backups with minimal impact and how to verify that those backups are  
OK. In short, if you choose Postgres, I can keep it running for you
- if/when your app reaches the limits of Postgres’ single-box  
scalability, I can help you with scaling it horizontally, and I can  
even help you migrate from Postgres to e.g. Oracle or DB2 if that  
rocks your boat. In other words, you won’t hit scalability issues  
that can’t be resolved on my watch, sailor. It may cost you money,  
but that’s part of the cost of having a successful app
- if you ask for my advice, then go against my advice and use MySQL,  
don’t expect me to help you out later when you’ve got problems unless  
you’re rich. If you’re rich _and_ make a habit of taking bad advice,  
well I’ve got some prime swampland real estate to sell you…
- if you decide to use MySQL, make sure you go find a DBA that you  
trust and who can say exactly the same things as I did but  
substituting Postgres for MySQL and vice versa

Honestly, it’s not like your startup project is going to sink or fly  
based on whether you’ve chosen MySQL or Postgres - they both work  
reliably, they both work well, 95%+ of their features apply to both  
products, and both are likely to be perfectly capable of meeting the  
needs of 99.9% of startup projects where an open-source database is  
actually an appropriate choice. If you have to change from one to the  
other, it’s generally not a major nightmare provided you haven’t got  
100,000 users expecting 24x7 availability (and even then the  
challenges are primarily logistical rather than technical). Your  
project’s far more likely to sink if you’ve chosen technology that you  
don’t know how to maintain, or you don’t have access to someone with  
the skills and commitment to maintain it for you.

Regards

Dave M.

---

<div class="post-metadata">

**Author:** ![mwalsh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/91b2a8/32.png) [@mwalsh](https://www.ruby-forum.com/u/mwalsh)\
**Post date:** [September 25, 2007, 11:10pm UTC](https://www.ruby-forum.com/t/rails-benchmarks-with-postgresql-and-mysql/114777/4 "2007-09-25T23:10:29Z")

</div>

On Sep 13, 11:36 am, Philip H. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

probably won’t shock to find that benchmarking pg vs. my is a major  
cottage industry. From google:

> **[Database test: dual Intel Xeon 5160](https://tweakers.net/reviews/657/6/)**

[http://feedlounge.com/blog/2005/11/20/switched-to-postgresql/](http://feedlounge.com/blog/2005/11/20/switched-to-postgresql/)

[http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html](http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html)  
[http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html](http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html)

---

<div class="post-metadata">

**Author:** ![mwalsh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/91b2a8/32.png) [@mwalsh](https://www.ruby-forum.com/u/mwalsh)\
**Post date:** [September 25, 2007, 11:10pm UTC](https://www.ruby-forum.com/t/rails-benchmarks-with-postgresql-and-mysql/114777/5 "2007-09-25T23:10:46Z")

</div>

Quoting gene tani [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid), who spaketh thusly:

> probably won’t shock to find that benchmarking pg vs. my is a major  
> cottage industry.

…and is also the database analogue to the whole vi-emacs debate. I’d  
say each have their advantages, so it’s more a decision based on your  
requirements rather than a one-size-fits-all type of judgement.

I am a big fat PostgreSQL homer though _blush_

– Mitch

---

<div class="post-metadata">

**Author:** ![mwalsh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/91b2a8/32.png) [@mwalsh](https://www.ruby-forum.com/u/mwalsh)\
**Post date:** [September 25, 2007, 11:10pm UTC](https://www.ruby-forum.com/t/rails-benchmarks-with-postgresql-and-mysql/114777/6 "2007-09-25T23:10:12Z")

</div>

> benchmarks with new ideas. Some of my conclusions are at the bottom.  
> If you have more to add or details to fill in, please do so. Thanks.

Re-run your tests, but instead of having a single rails app do this,  
spawn  
50-100 apps and have them do it concurrently. I suspect you’ll find  
that  
the results are just about opposite of what you got below…