The Problem: We've built an application where each user has a large, always-growing amount of data -- thousands of records per user with a variety of data types. Right now, everybody's data is in a single MySQL database which is working great, but we expect to soon outgrow this setup, and are exploring the best ways to partition the data.
The Questions: Because our app is only ever dealing with one user's data at a time, would it make any kind of sense to create a separate database for each user? Not a separate server per user, just a separate database. We know we'd be working against conventions to make it work, and would be limited by the number of inodes on the server.
How expensive is it to setup MySQL connections on-the-fly? We couldn't use connection pooling in ActiveRecord, since that would mean having to maintain thousands of connections at once. Has anyone tried something like this? We know it sounds crazy but are wondering if it conceivably falls under the category "so crazy it just might work".
If there are some showstoppers, can anyone recommend a partitioning scheme that works well with Rails? MySQL 5.1 and later seems to have pretty impressive horizontal partitioning. The main reason we haven't jumped on that is because we're wondering what happens when you need to make a schema change. Does the whole server have to come offline, or can it be done partition-by-partition?
-Mike Subelsky
PS If anyone else has been wondering about this, I found a great article on maintaining five different PostgreSQL databases for a single app: http://tomayko.com/weblog/2007/04/13/rails-multiple-connections