Ask HN: How to deal with multi-tenant SaaS database structure?

#1

Post by shahinghasemi » Wed, Nov 23, 2022, 5:14 PM UTC

Hello HN, I'm building a SaaS product. Each tenant can have their own database structure/fields while many tables are common between the tenants. For example table A is common for all of them while table B might have different fields upon organization/tenant's need, in other words we have table B for all orgs with different fields.

I'm thinking of shared-codebase and shared-database paradigm for it's simplicity(no synchronization needed). Have you encountered this situation? Is it possible/and how to handle this objective with single database for all tenants? Further, I'm using PostgreSQL database.

Thanks in advance.

Re: Ask HN: How to deal with multi-tenant SaaS database structure?

#2

Post by codingdave » Wed, Nov 23, 2022, 5:35 PM UTC

Possible to do with one database, yes - Add JSON columns to your tables to hold tenant-specific metadata. Deal with it in your app layer.

But I'd re-think whether a single database is truly the right answer. What is driving you to that solution?

Re: Ask HN: How to deal with multi-tenant SaaS database structure?

#3

Post by shahinghasemi » Wed, Nov 23, 2022, 5:44 PM UTC

codingdave wrote: post #2

Possible to do with one database, yes - Add JSON columns to your tables to hold tenant-specific metadata. Deal with it in your app layer. But I'd re-think whether a single database is truly the right answer. What is driving you to that solution?

The main reason: getting rid of synchronization/migration process for many databases.

Re: Ask HN: How to deal with multi-tenant SaaS database structure?

#4

Post by PaulWaldman » Wed, Nov 23, 2022, 7:15 PM UTC

How many tenants and how many clients per tenant will you support?

Also how much data will each tenant consume?

Re: Ask HN: How to deal with multi-tenant SaaS database structure?

#5

Post by satya71 » Wed, Nov 23, 2022, 7:16 PM UTC

You're heading the right way. A correct design is not simple, but it can be done. You might be able to buy a boilerplate that'll work as a starting point.

Separate database will only work if you'll have a handful of customers, and even then a pain.

Re: Ask HN: How to deal with multi-tenant SaaS database structure?

#6

Post by shahinghasemi » Wed, Nov 23, 2022, 7:23 PM UTC

PaulWaldman wrote: post #4

How many tenants and how many clients per tenant will you support? Also how much data will each tenant consume?

It differs for each tenants. Preferably there should be no limit for the number of orgs/tenants. The traffic for each tenant is also varying but it's safe to consider 500req/s.

Re: Ask HN: How to deal with multi-tenant SaaS database structure?

#7

Post by joshxyz » Thu, Nov 24, 2022, 12:03 AM UTC

postgresql row level security excels on this right

Re: Ask HN: How to deal with multi-tenant SaaS database structure?

#8

Post by ozzythecat » Thu, Nov 24, 2022, 7:45 AM UTC

shahinghasemi wrote: post #3

codingdave wrote: post #2

Possible to do with one database, yes - Add JSON columns to your tables to hold tenant-specific metadata. Deal with it in your app layer. But I'd re-think whether a single database is truly the right answer. What is driving you to that solution?

The main reason: getting rid of synchronization/migration process for many databases.

What do you have to keep synchronized across tenants?

And is consistency across tenants really a hard requirement? Would an eventual consistency model work?

How does migration factor in? Do you mean migrating one database vs multiple databases?

What exactly would you be migrating? And if it’s written in code, migrating individual tenants one at a time, at your pace or when a particular tenant is ready to migrate, might be a feature and not a bug.

Re: Ask HN: How to deal with multi-tenant SaaS database structure?

#9

Post by ozzythecat » Thu, Nov 24, 2022, 8:55 PM UTC

If one tenant misbehaves and takes down your database, how will your other tenants feel?

Re: Ask HN: How to deal with multi-tenant SaaS database structure?

#10

Post by shahinghasemi » Thu, Nov 24, 2022, 10:27 PM UTC

ozzythecat wrote: post #9

If one tenant misbehaves and takes down your database, how will your other tenants feel?

What misbehaving do you mean? If traffic is what you mean, it's okay since at the first stage of the software we have time to test/scale accordingly. Therefore in that stage it might make sense to change the architecture to per-tenant database.