Switching your Rails Database from SQLite3 to PostgreSQL or MySQL

I deployed a minimal new Rails application I wrote over a period of a couple of hours and started testing it with valid production data.  It's basically a very simple CRUD application for a very specific audience.  Very basic stress testing (using Apache's ab) showed me that it didn't work well when requests were coming in with concurrency > 2. I realized I'd started the project using SQLite3 as the database and that's probably where the bottleneck is since it can handle requests with a concurrency of 1 just fine.  The application is deployed using mod_passenger (my first experience with it) and is configured to never tear down application instances due to idle timeouts.  With 6 application instances listening, a concurrency of 6 should have been a cinch.

In any case, now that I need to switch it to using a real database like PostgreSQL or MySQL, there were no obvious solutions that would allow me to keep the data I already had in my production database.  Everything out there talks about going from development to test to production each of which environments gets their own schema but nothing else.  Migrations allow you to keep your production data in place but that's not the same as dumping it and loading it.  Using database-specific dump/load utilities might result in SQL that needs to be tweaked before it can be loaded into another database type.

In comes the Yaml Db plugin by Orion Henry and Adam Wiggins.  It does exactly what one would expect.  Similar to the way schema.db is database-agnostic, the Yaml Db has a database-agnostic dump format and a similar load format.  One of the specific use cases mentioned on that site is "One common use would be to switch your data from one database backend to another."

Excellent!  Btw, thank you George for the tip!  You know who you are :)

A Scientific View on Database Indices

I've been playing with Ruby on Rails by seeing how easy (or difficult) it would be for me to re-write an existing mod_perl2 application within RoR. While the original application uses PostgreSQL as its backend database (numerous reasons but the top few are: timestamptz, multiple columns in a table that can default to now(), support for transactions - even nested ones), I decided to use sqlite3 for the initial prototype of the application before it grew beyond 2-3 models.

It seems that either sqlite3 or RoR 2.0.5 doesn't support multi-column indices on a table. One of those two converts a single two-column index into 2 one-column indices - not quite the behavior I wanted. I'll try to switch to PostgreSQL to see if the behavior re-surfaces.

While I was scouring Google for sqlite3's limitations, however, I came across this interesting article that details the kind of scientific analysis that should be conducted on your data to determine the order of columns in a multi-column index. Very informative.