Pros/Cons of MySQL vs Postgresql for production Ruby on Rails environment?

Question

I will soon be switching from sqlite3 to either postgres or mysql. What should I consider when making this decision? Is mysql more suited for Rails than postgres in some areas and/or vice versa? Or, as I somewhat suspect, does it not really matter either way?

Another factor that might play into my decision is the availability of tools to data pump my test data from the sqlite3 db to my new one. Is there anything that ActiveRecord provides natively to do this or any decent plugins/gems to help with this task?

BONUS: How do I pronounce "Postgresql" and sound like I know what I'm talking about? :)

Thanks Greg Smith for providing the following link that shows the most common pronunciations:

http://www.postgresql.org/community/survey.33

UPDATE:

Reference this question for more:

http://stackoverflow.com/questions/110927/do-you-recommend-postgresql-over-mysql

FYI:

I ended up using MySQL. There is a neat plugin called yamldb that really saved me some time with the data transfer from my sqlite db to my new mysql one. Instructions on how to install and use it can be found here:

http://accidentaltechnologist.com/ruby/change-databases-in-rails-with-yamldb/

Thanks

Tom

Answer 1

Rails should be using standard SQL statements to access your data. If this is true, conceptually there should be little difference between the two databases.

Anecdotally, there is evidence that MySQL is somewhat faster, but that Postgresql scales better as the database gets larger.

If I understand the licenses correctly, MySQL is more of a "free as in speech" database, where Postgresql is "free as in speech" and "free as in beer."

SQLite has lots of ways to get data INTO SQLite, including a Postgresql process. I didn't see anything going the other way though. It should be simple enough to work up some SQL statements.

Answer 2

I'm not sure of the technical comparison between the most recent versions of the systems, but I do know there are non-technical factors which you might want to weigh in your decision.

MySQL got bought by Sun which subsequently got bought by Oracle. There were quite a few articles on the decline of quality of MySQL since the founders sold it.

There are other free dbs as well, but i think Postgresql sounds like a good choice.

Answer 3

MySQL is a lot more popular than PostgreSQL. That's not an important technical consideration, but when it comes to finding people who can help you solve problems, it can be a big deal. Up to a certain size database, it won't matter, but when your database starts getting big and you run into performance problems, it can be advantageous to be able to tap into the MySQL community.

Answer 4

It depends on the projected scale of your web application. For relatively small databases, MySQL is fine and is slightly faster for simple selects, etc. Once your database gets large however, MySQL hits a wall where PostgreSQL just keeps on driving through it.

As long as you're using ActiveRecord for your middleman, it won't matter what database you use, as it builds Standards Compliant SQL Statements, which will work on both databases. As soon as you specify any triggers, stored procedures, etc, you'll be stuck with one database.

I personally prefer PostgreSQL, but that's just because I was never a big fan of MySQL.

PS: I pronounce it as "Postgres".

Answer 5

It's pronounced Post-gres-Q-L; see the official recording. A good sized chunk of people just use "Postgres" all the time instead. There's a survey showing the relative popularities of the various other and less popular incorrect pronunciations, most of these are accepted by community members anyway, except for calling it "Postgre" which annoys all the regulars.

I wrote a long piece comparing MySQL vs. PostgreSQL in terms of speed and reliability, touching briefly on development model. Since then, Postgres has continued to make steady forward progress with a unified community producing completely free software. Meanwhile, MySQL has wandered into this split community going in all directions and with a downright frightening owner of the original code. It's pretty crazy, and if you value really free software the only reason I could see for using MySQL at this point if is you already have an app that only runs on that database.

Answer 6

Either choice is not going to be a bad one.

If you have no legacy and are starting fresh, I would recommend PostgreSQL mainly since it's quite mature, and have nifty features.

One thing that you might want to consider is if you need replication though, which really requires some black magic(or commercial support) with PostgreSQL. Repplication in mysql is prety straight forward though

Answer 7

@Rafe thanks guys! Any technical details you can add? Replication solutions, full text search, any oddities you ran into, anything like that would be appreciated!