Shared Schema Multi-tenancy

[Zynde]

Hi,

I've been reviewing Reladomo for use in a project but see nothing about multi-tenancy.

I plan on implementing shared schema tenancy by using row level security in PostgreSQL. My original plan before coming across Reloadomo was to set a connection parameter when the connection was grabbed for use. This was to be used in PostgreSQL via a table policy that checked the value compared the the column value stored essentially creating a virtual database.

I think I can do the PostgreSQL part still and the connection parameter by overriding the getConnection in say the SourcelessConnectionManager.

Where I get confused is with the Caching in Reladomo.

I've read what I can on the caching and It would appear that there is a collection of indices and that matching an object in the cache must be done via a match with "=" or "in". So if the tenant id was part of an index this may work?

Is there a way I can do what I want to do?
How would one use Reladomo for multi-tenancy?

Any help would be greatly appreciated.

[gs-rezaem]

Can you explain exactly what you mean by multi-tenancy?

Just FYI, Reladomo natively supports sharding, where the data schema is the same, but it's physically separated by shard instance. This could be a single DB server with multiple, identical schemas. It can be multiple DB servers and it can even be different tables in the same DB.

[Zynde]

I'm developing a SAAS system and have potentially thousands of customers (that would be nice).

I was going to go with a shared schema so I have one schema, one set of tables and everyone uses that. There would be a tenant identifier on the data.

I could filter every query I guess. But the row-level-security option didn't rely on remembering to add the filter.

I suppose another option would be a similar attribute to the AsOfAttribute like "Tenanted" that when applied to a MithraObject definition in the XML would automatically add it to the query as the ProcessingDate filter does.

There are usually multiple ways to do things. I may make use of the current functionality for those customer who need total seperation at some point but initially I was hoping to keep them in the same tables. But doing the original way probably messes up the way the Reladomo caching works but I'm not sure. Perhaps if the Tenant is part of the Primary Key it might work, I'm not sure.

Is that any help?

[gs-rezaem]

When using the same table, the caching will not get messed up if your declared key (in the xml) matches your database's unique constraint, which presumably includes tenentId.

However, the connection/db level stuff gets interesting if you really want to put everything in the same table. The easiest thing to do would be not to enforce this database side and just do it application side. So use a single connection (pool) that has full access to the db and in the application, make sure every query has a tenentId.

For scalability, I would definitely recommend multiple schemas. Easier to backup/move/delete, etc.

[Zynde]

@gs-rezaem

I prefer the idea of multiple schemas for separation of data but I'm not sure about the management of this with a few thousand schemas or even if PostgreSQL (AWS Aurora) could happily deal with 100 tables or so copied a few thousand times. Do you have experience of that sort of scaling in PostgreSQL?

I agree that perhaps the easiest solution for my original shared schema is to just filter in the application and I may just go that route if the multiple schemas doesn't happen.

[gs-rezaem]

I don't think many schemas would be a problem. I've seen other databases handle 1000 tables x 30 schemas.

[gs-rezaem]

A couple of closing comments:

• We've never tested AWS Aurora. It might be worth running the TestPostgresGeneralTestCases on that setup.
• For schema maintenance, you should consider using Obevo. See this doc

[Zynde]

@gs-rezaem Hi. I thought I'd let you know that the tests worked fine on AWS Aurora (PostgreSQL)

[gs-rezaem]

@Zynde Thanks very much for trying it. We'll have to add that to the docs.