mySql or Postgresql, that is the question?

rubyonrails-talk
1

I have to decide witch database to use in a Rails project for the client. It has to do with some payment system and should be fast.

I have more knowledge about mySql but Postgresql looks like a better choice because of the transactions.

In most posts I find a mySql as used data base engine.

Is there any particular reason to prefer mySql over Postgresql?

Please don't make this in a "my choice is better than yours" war I need to make the right selection and from the clever reasons.

Thanks in advance.

2

I routinely use both for Rails projects. I notice a quirk with the
Postgres driver once in a while when putting together large dynamic
find queries, but you'll probably be fine with either.

Preston

3

dima wrote the following on 23.03.2007 23:48 :

I have to decide witch database to use in a Rails project for the client. It has to do with some payment system and should be fast.

I have more knowledge about mySql but Postgresql looks like a better choice because of the transactions.    MySQL supports transactions when using InnoDB. PostgreSQL and MySQL have roughly the same performance in this case.

PostgreSQL is more stable, standards compliant and uses less RAM. MySQL supports replication out of the box and can mix InnoDB and MyIsam, which can help with performance in some cases.

Lionel.

4

> I have to decide witch database to use in a Rails project for the > client. > It has to do with some payment system and should be fast. > > I have more knowledge about mySql but Postgresql looks like a better > choice because of the transactions. > > In most posts I find a mySql as used data base engine. > > Is there any particular reason to prefer mySql over Postgresql?

I think most people go with MySQL because it's more popular.. which makes it more popular, which makes more people go with MySQL, etc. :slight_smile:

If your database needs don't go beyond what ActiveRecord can do, then either is fine. For one of my projects, I am using Postgres, specifically because I want to use table triggers and stored procedures and the like, and postgres's procedural language ("plpgsql") is far better and closer to the SQL standard than MySQL's.

Oh, and you can use ruby as a database procedural langauge in postgres now too. :slight_smile: Although that kind of seems like putting the cart before the horse...

    - Tyler

5

In the cases that mattered to me, PostgreSQL syntax is closer to the SQL standard than MySQL's is.

If you declare a column as NOT NULL in MySQL (at least up to version 4), you get default values for that column whether you want them or not.

PostgreSQL supports transactional changes to the database schema. There's even a plugin that makes migrations transactional without any further work (http://agilewebdevelopment.com/plugins/transactional_migrations).

Michael

6

Thank you all for you time and effort to enlighten me.

I will go for Postgresql at the moment. With db migration it would be much of a problem to switch to any other database (mySql or DB2) if such need emerge.

As this project of mine progress I will share my experience with you.

7

If you are going to be dealing with money, then you need a formal transaction boundary. In that case I believe that Postgresql is the more appropriate choice.

I haven't used either much, yet. I'm just starting with a project that has the same question, and the choice we made is the suggestion I'm making to you. Postgresql and MySql are comparable in performance, so until the next version of Firebird is released and shaken out, Postgresql is likely the best choice for a free database that has defined transaction boundaries.