Multi-Tenant Rails 3 App on Heroku using PostgreSQL

Question

I have a multi-tenant app on Heroku (multiple Accounts that know nothing about each other), and I'm unsure as how to best architect my database. Postgres's schemas look awesome, but heroku doesn't handle large numbers of them well.

Now, my Rails app is mainly serving JSON, as most of the templates are rendering client-side (using Backbone.js). So I'm considering moving to MongoDB because 1) each tenant could get one top level 'account' document, and everything could be nested below and 2) its storage format so closely resembles JSON. We are still in beta, so it could be pulled off. Are those valid reasons to use Mongo? Is former even an intelligent way to use Mongo?

If I were to stick with postgres, should everything belong_to the top level Account model (with indexes)? If so, how would I handle joins? Is it possible to perform mult-index joins (total postgres noob)?

We have about 60k entries in one table so far, yet a single account may only have 200-1000, so I'm worried about joining on the entire table.

Really appreciate any help.

Update:

We ended up moving to a VPS (Rackspace Cloud) and implementing postgres's schemas. No regrets with this move as it runs much faster than it did on Heroku, and we have more control of the server.

When doing multi-tenant apps everyone everyone just scopes everything under an account-like table, nothing new on that, it's much simpler to handle replication and even sharding when you do it like this instead of the multi-schema solution. About joins, it does, but joins are always expensive, no matter what. — Maurício Linhares
– Maurício Linhares, Commented Jun 20, 2012 at 1:29
There's not enough detail in the question to answer it. Just telling us that you "have a multi-tenant app" is not enough information to help you decide what kind of data store to use. Either may be perfectly appropriate. How many tables do you have? How "relational" is your data? — Dondi Michael Stroma
– Dondi Michael Stroma, Commented Jun 20, 2012 at 4:04
@MaurícioLinhares thanks. So, just add an account_id to every table and index them is what you are saying? — kmurph79
– kmurph79, Commented Jun 20, 2012 at 5:09
That creates a lot of additional indexes. Now every query you're running against almost every field on those tables has to be filtered by account id adding more complexity and size to your index structure and query plans. Schemas avoid the need for all of those indexes and simplify all of your queries dramatically. Schemas are the optimal architecture, Heroku just needs to find a way to solve their backups issue. The other option is rather than relying on their backups for your main database, to create a low powered follower to be backed up. This would provide a live backup too. — brightball
– brightball, Commented Jul 20, 2013 at 3:26

Will · Accepted Answer · 2012-06-20 06:29:39Z

2

Check out this gem which was written to do multi-tenancy with postgres http://railscraft.tumblr.com/post/21421806379/multi-tenanting-ruby-on-rails-applications-on-heroku

answered Jun 20, 2012 at 6:29

Will

2,96119 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multi-Tenant Rails 3 App on Heroku using PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related