Any thoughts on Multi-tenant versus Multi-database apps in Rails

Question

Our app currently spawns a new database for each client. We're starting to wonder whether we should consider refactoring this to a multi-tenant system.

What benefits / trade-offs should we be considering? What are the best practices for implementing a multi-tenant app in Rails?

Answer 1

Multi-tenant systems will introduce a whole range of issues for you. My quick thoughts are below

• All SQL must be examined and refactored to include a ClientId value.

• All Indexes must be examined to determine if the ClientId needs to be included

• An error in a SQL statement by a developer/sysadmin in production will affect all of your customers.

• A database corruption/problem will affect all of your customers

• You have some data privacy issues whereby poor code/implementation could allow customerA to see data belonging to CustomerB

• A customer using your system in a heavy/agressive manner may affect other customers perception of performance

• Tailoring static data to an individual customers preference becomes more complex.

I'm sure there are a number of other issues but these were my initial thoughts.

Answer 2

I don't have any experience with this personally, but during the lightning talks at the 2009 Ruby Hoedown, Andrew Coleman presented a plugin he designed and uses for multi-tenant databases in rails w/ subdomains. You can check out the lightning talk slides and here's the acts_as_restricted_subdomain repository.

Answer 3

Why would you? Do you have heavy aggregation between users or are you spawning too many DBs? Have you considered using SQLite files per tenant instead of shared DB servers (since multitenant apps often are low-profile and don't need that much concurrency)?

Answer 4

It really depends upon what you're doing.

We are making a MIS program for the print industry that tracks inventory, employees, customers, equipment, and does some serious calculations to estimate costs of performing jobs based on a lot of input variables.

We are anticipating very large databases for each customer, and we currently have 170 tables. Adding another column to almost every table just to store the client_id hurts my brain.

We are currently in the beta stage of our program, and here are some things that we have encountered:

• Migrations: A Rails assumption is that you will only have 1 database. You can adapt it for multiple databases, and migrations is one of them. You need a custom rake task to apply migrations to all existing databases. Be prepared to do a lot of trouble shooting because a migration may succeed on one DB, but fail on another.
• Spawning Databases: How do you create a new db? From a SQL file, copying an existing db, or running all migrations? How do you keep you schema consistent between your table creation system, and your live databases?
• Connecting to the appropriate database: We use a cookie to store a unique value that maps to the correct DB. We use a before filter in an Authorized controller that inheirits from ActionController that gets the db from that unique value and uses the establish_connection method on a Subclass of ActiveRecord::Base. This allows us to have some models pull from a common db and others from the client's specific db.

If you have specific questions about any of these, I can help.

Answer 5

I've been researching the same thing and just found this presentation to offer an interesting solution: Using Postgre's schemas (a bit like namespaces) to separate data at the DB level while keeping all tenants in the same DB and staying (mostly) transparent to rails.

Writing Multi-Tenant Applications in Rails - Guy Naor