# Same table, multiple DBs? **URL:** \ **Category:** Rails\ **Created:** [June 1, 2009, 1:52am UTC](https://www.ruby-forum.com/t/same-table-multiple-dbs/169226 "2009-06-01T01:52:01Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [June 1, 2009, 1:52am UTC](https://www.ruby-forum.com/t/same-table-multiple-dbs/169226/1 "2009-06-01T01:52:01Z")
I’m starting to think about how my application can scale if it needs to. This is all very premature - I don’t even have clear performance goals yet, much lest perf testing in place, so it may be unnecessary. However, one quality my data has is that it’s isolated into groups of records that never reference anything outside of that group. This leads me to think I can have a small group of tables in a main DB to store user info, and the rest of my tables each have an instance in multiple databases: Main: Users Auxilliary #1: Table1, Table2, Table3, … Auxilliary #2: Table1, Table2, Table3, … … The nice thing about this design is that it would scale very nicely. If things are slowing down, I can easily bring up another server and balance the data. Users, would of course track which DB instance the user connects to when using the app. The users table is referenced frequently, but rarely updated, so if that becomes a bottleneck I can probably have a second instance with some reasonable data replication between the two. Is there any built-in way to handle this in rails (or a popular plugin that can do this)? My very initial investigation seems to indicate a specific table can be stored on another DB, but that ALL of the rows are stored on that DB. I can’t split this up across multiple servers. As I said, this is premature, so it’s more of a curiosity than a critical problem I need to solve. Just curious if anyone has done anything along these lines alread. ---
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [June 1, 2009, 4:24am UTC](https://www.ruby-forum.com/t/same-table-multiple-dbs/169226/2 "2009-06-01T04:24:33Z")
Brian wrote: > Is there any built-in way to handle this in rails (or a popular plugin > that can do this)? My very initial investigation seems to indicate a > specific table can be stored on another DB, but that ALL of the rows > are stored on that DB. I can’t split this up across multiple servers. As far as I know, you’re right that you can only specify a different DB on a per-model basis. But you could make sub-classes of each of your models, one per each db, and then specify the different DB on each of those sub-classes. But then when doing a fetch, you’d have to choose the right model to do the fetch from – but of course, that’s kind of the point of why you can’t have more than one db on just ONE model – how would Rails know what DB to go to when you simply tried to do a Model.find on that model? I’m not really feeling confident that the architecture you’re proposing really is the best solution to scaling up, but if you wanted it, I think ActiveRecord could support it in that manner – by first writing your models, and then providing N sub-classes of each model you want to ‘partition’ to N databases. You could even provide some code to do this ‘dynamically’, you wouldn’t need to (and wouldn’t want to) actually hand-code a separate file on disk for each model. All the associations defined on the ‘base’ model would need to be re-defined for each model sub-class to use the appropriate destination model-subclass. This could also be done in an automated dynamic way. And then you’d need to provide some helper methods to figure out _which_ model to use for a given fetch (Model.find) operation. I’m not aware of any plug-in that already does this. I’m not sure it’s a good idea, like I said, but I’m no expert. I think you’d have to write the logic yourself, but I think it’s perfectly do-able within ActiveRecord’s architecture. But no doubt it will end up somewhat more complicated to get working right than it seems at first, such things always do. Jonathan ---
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [June 1, 2009, 4:34am UTC](https://www.ruby-forum.com/t/same-table-multiple-dbs/169226/3 "2009-06-01T04:34:33Z")
Brian wrote: > I’m starting to think about how my application can scale if it needs > to. This is all very premature - I don’t even have clear performance > goals yet, much lest perf testing in place, so it may be unnecessary. Yeah…it may be premature to act on this, although thinking never hurts. 🙂 > However, one quality my data has is that it’s isolated into groups of > records that never reference anything outside of that group. This > leads me to think I can have a small group of tables in a main DB to > store user info, > […] > Is there any built-in way to handle this in rails (or a popular plugin > that can do this)? I think you will want to do this on the DB side rather than in the app. The various DB servers out there have ways of breaking up tables for performance reasons and still having them look the same to the application (I think MERGE tables in mySQL fit this description). So investigate what your DB server can do for you before you start trying to get clever with table-based subclasses. ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [June 1, 2009, 9:19am UTC](https://www.ruby-forum.com/t/same-table-multiple-dbs/169226/4 "2009-06-01T09:19:35Z")
On Jun 1, 3:34 am, Marnen Laibow-Koser \ wrote: > > Is there any built-in way to handle this in rails (or a popular plugin > > that can do this)? > > I think you will want to do this on the DB side rather than in the app. > The various DB servers out there have ways of breaking up tables for > performance reasons and still having them look the same to the > application (I think MERGE tables in mySQL fit this description). So > investigate what your DB server can do for you before you start trying > to get clever with table-based subclasses. Although completely splitting up tables (ie sharding) is a well known technique when having a single database server becomes a bottleneck. Depending on the balance of reads and writes in your application, there can be gains to be had by doing your writes to the master server and doing reads from one (or more) slave servers that replicate the master. There is a plugin (masochism) that used to handle some of this, I haven’t looked at it in a while though. Fred ---
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [June 1, 2009, 5:29am UTC](https://www.ruby-forum.com/t/same-table-multiple-dbs/169226/5 "2009-06-01T05:29:40Z")
On May 31, 10:34 pm, Marnen Laibow-Koser \ wrote: > ## removed\_email\_address@domain.invalid > > Posted viahttp://www.ruby-forum.com/. Oh! I hadn’t even considered that. I will look into MERGE tables and see if that’s going to do what I need. I’m not set on this as the best solution either, just running through possibilities and making sure I don’t exclude any reasonable options with poor design. I’ve also put some thought into having specific tables groups of tables hosted on other DB servers, but that is a much less mysterious implementation so I didn’t need to ask if anyone has tried it before. 🙂