Multitenancy means serving multiple independent customers from one app. Pretty typical for SaaS model. You can implement it on several different levels:
- Row level - you put a
tenant_idcolumn into every DB table and filter by
tenant_idin every query.
- Schema level - for every tenant you create a separate namespaced set of tables inside one database. Easily achievable with PostgreSQL schemas. See next paragraph on how this relates to MySQL.
- Database level - you setup a whole new DB for every tenant. Rarely practical, including just for comparison.
Here’s how they compare to each other:
|Tenant setup time||⚡️ Just create a record||🐢 Slower (need to create schema, create tables)||🐌 Even slower + possible operational overhead|
|Leaking data between tenants||💥 If you forget a
||✅ Get a couple things right and you’ll be fine||✅ You’d need to try hard to get one|
||👍 Fine||👍 Fine|
|Need shared tables or merging data across tenants||✅ No brainer||👍 Can still be done in SQL||🚫 In-app only, cannot do in SQL|
|Running DB migrations||⚡️ O(1)||🐢 O(n)||🐌 O(n)|
|Conventionality||👍 Standard Rails||🛠 Occasionally at odds with Rails assumptions||🤔|
|Additional costs||👍 Not really||👍 Not really||❓ What if pricing depends on the # of DBs?|
|Operational overhead||✅ No||👍 Occasionally. You have an ever growing number of db tables.||🛠 You now have a lot of databases|
||🌴 an exotic PG feature & stateful
|Where possible||🌍 Pretty much anywhere||⚠️ Are you on a managed DB? Double check if all features and ops possible||⚠️ Got rights to create databases on the fly?|
|Cost of switching||⚡️ Just set a variable||⚡️ Set the
||🐢 You need to establish a separate db connection|
|Extract a single tenant’s data||🛠 Cumbersome||👍 Easy||👍 No brainer|
MySQL vs PostgreSQL schemas
MySQL has no feature like PostgreSQL schemas, but MySQL databases can be used in a similar way. You don’t need to establish another connection to change the database in MySQL - you can switch via the
use statement, similarly to what you’d do with PostgreSQL’s
set search_path. You can also similarly mix data from different databases by prefixing the table names.
The drawback is that in MySQL you need to make sure there’s no name collisions with other DBs. You also need to have create-database privileges to setup a new tenant. This can be a substantial difference if you don’t fully control the DB server. In case of PostgreSQL you only need the privilege to create new schemas inside your existing DB (and name collisions are constrained to it). This can work fine even on managed databases.
Quick reasons to pick one or another
|A lot of tenants?||consider row-level|
|A lot of low-value tenants? (like abandoned accounts or free tiers)||consider row-level|
|Less tenants and they’re high-value?||schema-level more viable|
|Anxious about data isolation? (ensuring no data leaks between tenants)||consider schema-level|
|Customers might require more data isolation for legal reasons?||consider schema-level or even db-level|
|On a managed or cloud hosted database?||if you wanna go for schema-level make sure it all works for you|
|Multitenantizing an existing single-tenant code base?||schema-level might be easier to introduce|
|Greenfield project?||row-level more viable|
|Need to combine a lot of data across tenants||schema-level possible, but row-level is a safer bet|
Feel like contributing to this blogpost?
🛠 Feel free to submit a pull request to this blogpost. It can be a nuanced remark, better wording or just a typo.
🗞 There are at least two other multitenancy-related blogposts we’re going to publish soon: Caveats and pitfalls of PostgreSQL schema-based multitenancy and A gentle introduction to schema-based multitenancy with basic concepts explained. If you don’t want to miss anything, subscribe to our newsletter.