Forum: Ruby on Rails Same table, multiple DBs?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Brian (Guest)
on 2009-06-01 03:52
(Received via mailing list)
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.
Jonathan R. (Guest)
on 2009-06-01 06:24
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
Marnen L. (Guest)
on 2009-06-01 06:34
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
removed_email_address@domain.invalid
Brian (Guest)
on 2009-06-01 07:29
(Received via mailing list)
On May 31, 10:34 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> 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.  :)
Frederick C. (Guest)
on 2009-06-01 11:19
(Received via mailing list)
On Jun 1, 3:34 am, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> 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
This topic is locked and can not be replied to.