-1

I am working on a multi-tenant application that's using a shared Postgres db for all tenants.

All entities in the DB are connected (directly or indirectly) to a tenant, for example:

Tenant --has many--> Buildings --has many--> apartments -- has many--> people (just an example, let's not get into X shouldn't be an entity kind of discussion)

So if a client is trying to update the info of a person, I have to do few joins to link Person entity to Tenant entity to verify this Person belongs to this client's tenant.

I haven't reached any performance issues (yet), but if I am going down a path with a dead end I would prefer to know while data migration is relatively easier.

My question: Should I link all entities directly to a Tenant and end up with a de-normalized database? or the current design is a good one ?

Improve this question
3
  • 1
    If each Tenant had it's own DB it would be more scalable. This way, if you have one Tenant that gets too large it could easily be offloaded to a different server of it's own. Then you could look at all users in the users table and know they all belong to the tenant. This is also more secure. You don't want a mistake to allow tenants to see each-other's data.
    – HackSlash
    Commented Aug 31, 2020 at 22:24
  • @HackSlash, I am most likely going to have a lot of tenants (order of 10 k) and not a lot of data. Marinating all those databases can become a real headache.
    – Moha the almighty camel
    Commented Aug 31, 2020 at 22:27
  • @HackSlash: You're not wrong, but just to refine: tenanted applications that currently share a DB are generally compatible with splitting one tenant off to its own ecosystem. Each tenanted app should already only be caring about its data anyway. Separate DB's are indeed better for scalability but you can generally get away with keeping them on the same DB until one tenant clearly needs individual scaling, at which point you can split them off. You don't have to start out by keeping them separate from the get go.
    – Flater
    Commented Sep 1, 2020 at 14:04

3 Answers 3

Reset to default
4

There is a linguistic trap in your question. Multi-tenant can mean different things in your example:

  • If tenant just happens to be an entity like any other and “multi-tenant” is about indirect one-to-many relationship: do not denormalize. Because nothing guarantees you that people only have appartments of the same tenant. Moreover, you may want to relate appartement directly to a tenant and cut the direct link between building and tenant, if you would like to allow a tenant to sell an appartement to another tenant.
  • If tenant represents the customer of your service (e.g. a renting platform) and every tenant shall be able to manage its data independently, then you could add the tenant id in every such table. It’s not denormalization: it’s about representing that each and every object is always owned by one tenant independently of how it is related to the tenant. You could even go farther and make all the keys composite, including the tenant in the primary key: this guarantees totally independent sets of data and reduces significantly the risk of accidentally showing data to a tenant that is not his/hers.
Improve this answer
2
  • I agree, the example of building is not the best one. tenant represents one customer of my service, the reason I am worried about the second approach is that I will have two ways to get the tenant of an entity, directly sing tenantId, and indirectly through joins.I started worrying that I don't have a Single Source of Truth anymore and I need to be more careful to keep the data consistent. Thanks for the answer !
    – Moha the almighty camel
    Commented Sep 1, 2020 at 7:04
  • @Mohathealmightycamel You still have A single source of truth, but you’ll have to cope with two different truths: the one is about relationships, the other is about data ownership. For example, the tenant info in a person (people) record, means that this record belongs to that tenant and not another. This does not mean that the person has a current relationship with the tenant: it could be a past renter (who no longer lives in an apartment) or a future renter (waiting for an apartment to be free).
    – Christophe
    Commented Sep 1, 2020 at 8:36
2

Denormalization is most of the times a practical solution, so it is bound to what you observe happening on a real system. I wouldn't apply it as a theoretical solution based on assumptions, if you think that over time the queries full of joins might take a heavy toll on the DB the best way to find out is to try it. Set up a test server, fill it up with a lot of dummy data and do some performance testing.

Improve this answer
1

Not quite. Assuming you run into performance problem (and multi-tenancy has nothing to do with that, just the time complexity of the joins involved and data range, so let's pretend we are single-tenant), there three fundamental choices:

  1. Partitioning. Split your big table it two independent tables on two independent machines united into a single PostgreSQL cluster. By doing that you roughly improving your performance by half having the same algorithm doing only half of the job.
  2. Pre-computation, sort of smarter caching. The idea is to compute data that you might need in advance; that may be a waste of memory and CPUs - because no one will actually go and ask for it, but yet, done rightly, it can bring quite significant improve.
  3. Denormalization. Avoiding joins.

Pick the most right for you and don't trust your intuition: rather measure.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.