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
on 2006-01-30 03:36
on 2006-01-30 03:55
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 Silva http://www.railtie.net/
on 2006-01-30 04:01
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
on 2006-01-30 04:28
I agree, INNODB all the way.
on 2006-01-30 05:00
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?
on 2006-01-30 05:01
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.
on 2006-01-30 05:04
David Mitchell 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
on 2006-01-30 05:35
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.
on 2006-01-30 09:59
FYI, at the recent Pasadena Rails Studio by Dave Thomas and Mike Clark, 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
on 2006-01-30 10:50
Dominique Plante wrote: > FYI, at the recent Pasadena Rails Studio by Dave Thomas and Mike Clark, > 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.
on 2006-02-01 22:27
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
on 2006-02-01 23:37
People should be aware that Oracle has purchased the InnoDB engine used in MySQL. http://www.databasejournal.com/features/mysql/arti... 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 Smirl email@example.com