This post is a part of the series of articles about choices available to a rails developer. It is inspired by a blog post by Josh Susser, where he asserts that there are too many choices available for a new ruby-on-rail developer now, which can get him scared/confused. This series is not about “choice or no choice?”, and not about “are the defaults correct?”. It just answers the newbie developer’s curiosity of what lies beyond the defaults. Lets begin with databases.

Database

Rails is used for writing database backed web applications using the MVC design pattern. The choice of database depends on the kind of capacity you expect from the database and the budget. If you are developing a large enterprise application, you might need to research Oracle/MS-SQL and the like, along with those described here. Another choice you might have to make is whether to go for a relational DBMS. The rails framework provides adapters (programs that connect your app to the database) for three popular open source relational database management systems – sqlite, mysql and postgresql. So if you are looking for an open source RDBMS, you have have the best to choose from.

Sqlite

Is the rails default. As the name suggests, sqlite is light-weight sql. Unlike the other two, it doesn’t work on the client-server model. It runs as a single library, attached to the executing rails app. Due to this, it saves the overhead of client-server communication, hence lite. A common perception is that it is suitable for development environments only and for live websites, it is a small tool (official perception). But lot of people swear by its usability in production – they say it is fine if your site receives up to 100k hits a day. Also, as it is light, it is the best suited database for/as embedded systems. Sqlite is used by some big names like Apple and Firefox. A more complete list can be seen on the sqlite website. More on sqlite performance -a stackoverflow question , official figures. However, as sqlite is not designed to handle heavy concurrency – it might not be a good choice if your app has 100 users per second all performing database operations.

mysql

Mysql is client-server based, fast, easy to use and very popular. It is available for most platforms (look at this comparison on wikipedia – mysql is all green), and adapters and tools are widely available. Quite full featured, can be configured to run well for different goals and environments through its 9 storage engines. And works reasonably fast by default. Do look at this discussion on stackoverlow if you are torn between mysql and sqlite. The only catch with mysql is in the license. Its free and fine for use, but if you are planning to distribute mysql with your close-sourced product, you may have to pay license fee. Some useful information on the licensing can be found at stackoverflow, wikipedia and the official website

PostgreSQL

PostgresSQL is another great RDBMS (actually an O-RDBMS) available for rails developers. It is almost as good as mysql, with some differences in technology, and notable difference in license. Architecture is different (this has a single engine), so is ACID compliance (full here, partial in mysql), and there are some feature differences (partial indices are supported here, not in mysql) as well. The license in postgres is a much simpler MIT style ‘postgres’ license. So which one to go with – mysql or postgres ? Most people who have used both are inclined (see this stackoverflow discussion) towards postgres. Mysql has some limitations (like – no partial indices), which should be understood, especially if your application might need some hard-core database operations, like, say multiple level sub-querying. Plus, postgres is controlled/supported by the developer community, while mysql (now) belongs to Oracle. A few more good comparison resources – tarangana blog, wikivs. Some relevent stackoverlfow questions – pros/cons,  mysql-> postgres switch, reason for mysql popularity.

Our take

Conclusion

This entry was posted on Friday, August 6th, 2010 at 6:28 pm and is filed under Programming. You can follow any comments to this entry through the RSS 2.0 feed. You can leave a comment, or trackback from your own site.