Database Selection

Rails
1

Hi,

I am a RoR newbie and I’ve been experimenting with different databases
(mySQL, PostgreSQL, etc.) and different table types (MyISAM vs. InnoDB).
Is there a preferred database/table type for RoR development. I like
how MySQL’s InnoDB tables enforce referential integrity, but these
tables require a lot more storage space then MyISAM tables.

For everyone with a lot of RoR experience, what database setup do you
prefer?

B

2

I’d imagine that’s a dangerous question to ask. Regardless of what
everyone’s opinions and personal preferences are, the bottom line is
that
the best one is the one that works best for you.

You can’t go wrong with Postgres or MySQL. If using MySQL, use the
INNODB
tables, it supports better application design albeit with a higher
maintenance cost.

I prefer MySQL simply for the reason that I’ve been using it for 9
years. If
I had business reasons for needing a full-featured database, then
Postgres
would be my choice.

Bob S.
http://www.railtie.net/

3

You can use either, I don’t think it really matters a lot to be
honest. I love postgresql, but I can’t really articulate why. If you
are really interested in DB constraints I’d recommend postgres simply
because it has excellent support (I admit I’ve never used any
constraints in MySQL).

Some Rails users say you shouldn’t have any kind of business logic in
the DB, while others advocate including some to ensure that the most
important part of your app - the data - is valid, even if you
sometimes violate DRY.

Rails makes it really easy to use either, and to switch out without
noticing a difference really. I imagine when you have to make a
decision between either one, it will be guided more by how you can
optimize it for your OS/hardware, you or your support team’s knowledge
of either db, etc. I don’t think it makes a difference which one to
go with for the vast majority of general Rails users.

Pat

4

Thanks for everyone’s comments so far. I’d feel the most comfortable
using mySQL, but my only concern is the overhead associated with InnoDB
tables. Are they really that much larger than MyISAM tables? Do InnoDB
tables slow down when a lot of records are present?

5

I agree, INNODB all the way.

6

I’ve always used Postgres over MySQL, and all things being equal would
continue to do so, but Dreamhost has a killer hosting deal on that
makes it worth my while to switch to MySQL for at least one project
(DH doesn’t support Postgres).

Regards

Dave M.

7

David M. wrote:

I’ve always used Postgres over MySQL, and all things being equal would
continue to do so, but Dreamhost has a killer hosting deal on that
makes it worth my while to switch to MySQL for at least one project
(DH doesn’t support Postgres).

Regards

Dave M.

Dave,

If you haven’t signed up for DH, I highly recommend it. I’ve been with
them for over three years and have never had any major problems with
hosting/email/etc. Their support is top notch also.

Brandon

8

Thanks Brandon,

That’s what I did - a bit of Google digging showed that any bad
experiences that have been reported with Dreamhost seem to have been
well in the past, and that they’ve now got a good offering at a pretty
wonderful price. In the few days since I signed up, I haven’t had a
need to seek their support, so I can’t comment on their performance
personally at this stage.

Having Rails be so database-agnostic will (hopefully!) make it fairly
easy to transition this app from its current Postgres backend to
MySQL. Plus it gives me a good reason to learn about migrations,
which has been on the never-ending shortlist of Things I Need To Learn
for a while now.

I’ll still use Postgres for anything that doesn’t get hosted on
Dreamhost though. Having come from an Oracle/DB2/SQL Server
background, Postgres fits my sensibilities much better than MySQL
does. YMMV of course, and what’s right for me won’t necessarily be
right for you.

Regards

Dave M.

9

Dominique P. wrote:

FYI, at the recent Pasadena Rails Studio by Dave T. and Mike C.,
one of them mentioned that transactions won’t work the way you expect
with MySQL ISAM tables… even though you may explicity mark that you
want something to execute in a transaction, it will not execute in a
transaction.
I can confirm that. MyISAMs have got a few nasties up their sleeves.
The only thing keeping them around in any of my projects is the lack of
FULLTEXT indices on InnoDBs. That’s not so much of a problem in Rails,
now that we’ve got Ferret, but it’s still annoying.

10

Yah, MyISAM has no transaction support - has nothing to do with Rails.

InnoDB does have transactional support. Hope that Oracle had something
in
mind when they bought InnoDB, besides gripping MySQL by the short
hairs…
SK

11

FYI, at the recent Pasadena Rails Studio by Dave T. and Mike C.,
one of them mentioned that transactions won’t work the way you expect
with MySQL ISAM tables… even though you may explicity mark that you
want something to execute in a transaction, it will not execute in a
transaction.

Hope this helps!
Dominique

Brandon wrote:

Hi,

I am a RoR newbie and I’ve been experimenting with different databases
(mySQL, PostgreSQL, etc.) and different table types (MyISAM vs. InnoDB).
Is there a preferred database/table type for RoR development. I like
how MySQL’s InnoDB tables enforce referential integrity, but these
tables require a lot more storage space then MyISAM tables.

For everyone with a lot of RoR experience, what database setup do you
prefer?

B

12

People should be aware that Oracle has purchased the InnoDB engine
used in MySQL.
http://www.databasejournal.com/features/mysql/article.php/3561731

Oracle has stated that when the current contract expires next year it
is going to change the terms of the InnoDB contract. It has not stated
how they will be changed.

–
Jon S.
[email protected]