3

My team is building an application and a question came up during a brainstorming meeting that I was unsure about so I figured I'd reach out to the Stack Overflow community to get some more opinions. Here's the gist of it:

  • Our application creates an account for organizations which have multiple users. Those users have posts and comments.
  • The original database design was to have a table for organizations and then each user would be related by the organization's id.

One of the developers suggested that we use a separate database for each organization's account to separate data between organizations and to increase performance. I'd never seen any Rails app that used multiple databases that way and I wasn't sure how to even do that in Rails.

My question to you then is:

Would we gain any benefit from using separate databases or is this adding an unneccessary level of complexity to the application?

Example

Say there are 100 organizations. Each organization has 100 users. Each user has 100 posts and 100 comments.

Would querying through these tables be a major performance drain or would having 100 separate databases be unwieldy and cause more issues than it would be worth? Does this cause issues with migrations? The schema would be identical between organizations.

I'm not sure if that was a clear enough question so let me know if you need more information before answering.

I did read the following Stack Overflow articles but they really didn't help me with this decision.

Community
  • 1
  • 1
Zachary Abresch
  • 756
  • 3
  • 8
  • 20
  • You might consider running a unique instance of your *entire app*, including database, for each of your clients. But you definitely shouldn't split up one application's data into multiple databases for "organizational" purposes. – user229044 Jan 31 '12 at 17:41
  • That's a good idea. Since this is our first major app developer in Rails we're still learning the best way to set this kind of thing up. I'm not entirely sure how to run separate app instances but I'll look into it. Thanks! – Zachary Abresch Jan 31 '12 at 23:23
  • If you're supposed to keep the data for each organization isolated from other organizations, look at SO questions tagged "multi-tenant". Also see [this SO answer](http://stackoverflow.com/a/8343142/562459). – Mike Sherrill 'Cat Recall' Feb 04 '12 at 00:54

2 Answers2

4

Don't do it. It will just add a level of unnecessary complexity.

Don't optimize for a problem before it becomes a problem. If your application gets to a point where database performance is a huge bottleneck, address that issue then. For now, concentrate on writing good, fast queries

37 Signals just posted this article about how, with good hardware, they have managed to avoid sharding and the associated sys admin overhead.

Matthew Lehner
  • 3,967
  • 3
  • 26
  • 35
  • That was my take on it. I think it is a little out of bounds to think about edge case performance issues when you don't even have an app you can test on. I appreciate the input and will probably stick with a single database and cross the performance bridge when (and if) we get to it. – Zachary Abresch Jan 31 '12 at 23:24
0

You can shard database there are various gem is also available,but as per my point of view there is no need to create separate database for your app ,but it's up to you ,you can improve DB performance by various ways

Amar
  • 6,874
  • 4
  • 25
  • 23
  • I saw a few gems that mentioned sharding. That is a new concept for me and I'll need to do some more R&D to determine if that would be of use to us. – Zachary Abresch Jan 31 '12 at 23:25