Both approaches have pros and cons. This is exactly what Nile is solving. We want users to not worry about all the operational complexity of picking a choice. You can choose any approach in Nile.
In Nile, the tenant DB is a virtual concept. You can choose to place it on a multitenant DB along with all the other tenants or choose to place it on a dedicated DB. Nile provides a single experience irrespective of your choice and takes care of all the operational complexity. We also go one step further and also let you place a tenant in any location worldwide but still have one Postgres experience.
You can read more about it here https://www.thenile.dev/docs/tenant-management https://www.thenile.dev/docs/tenant-placement
Would love the feedback
I guess that's why this was created though. It's definitely an intriguing tool.
I did shift in past from single database to sharded via citus. The actual migration was smooth, but 6+ months of work went into ensuring compatibility.
There's ways to nearly guarantee compatibility without actually using a sharded approach, like enforcing all tables have a uniform shard key.
Curious if the sharding strategy that you were shifting to was company-based, as is implemented in this case?
There's a few different approaches and deciding which to use can be tough. When I was adapting a formerly single tenant Django application to a multi-tenant solution, I went for a "db-per-customer" (with many databases on a single db server) for a few main reasons:
1) Allows you to backup and restore databases on a per customer basis.
2) Helps with IT reviews if you can say "your data exists in its own DB and is not co-mingled with other customers data"
3) Implementation seemed like it was going to be simpler, especially for adapting an existing single tenant application to a multi-tenant one.
4) Makes it easy to migrate existing single tenant-db's to the multi-tenant app.
The main drawback for us I would say is that it makes managing database migrations/schema changes a bit more challenging since you now need to update N db's every time there's a schema change. This was not super difficult though.
Overall I am very happy we decided to go with a multi-db multi-tenancy solution.
Additional benefits: easy to support customer-managed encryption keys and can give their BI people direct access.
> brittle permission logic at the application level or complex, hard-to-debug, row-level security policies in databases like Postgres
Separating them into separate databases has its own problems with connection pooling, resource management, and query speed. It's also a pain when you have to combine the data. Then you go down the rabbit hole of ETL and SaaS products with a "scratch" interface that promises to speed things up through (yet another) customer DSL and new naming convention for everything you already know.
A better option is separating tenants by schema. Think about how you switch between schemas in Postgres. Queries look something like this:
> set search_path to public, tenantA; > select * from somewhere as smw inner join elsewhere lsw on smw.id = lsw.somewhere_id;
The somewhere table is in the public schema, elsewhere is in the tenantA schema.
In Rails, there are a couple gems that come to mind for multi-tenancy. acts_as_tenant works exactly like this, by swapping the tenant schema in ActiveRecord (the Rails ORM layer). The ros-apartment gem is another (formerly just 'apartment'). I ran across another gem called 'roomer' too but it is outdated. It works the same way though. You could even roll your own if you insert the logic at the right layer of whatever stack you're using.
Set the tenant at the request level and the ORM auto switches the schema for you. Match a subdomain to the tenant and away you go. You got a SaaS application. edit: And, it'll pass a security audit because of the separation. And you can query across LOB tenant data in a BI front end if you can live without real-time data and build some materialized views; if Redshift and Snowflake are just too expensive.
Using schema separation you get the best of both worlds; smaller data in tenant specific tables with the ability to cross tenant query because they're in the same database. And yes, I'm well aware foreign servers are a thing in Postgres but with sufficiently large data you have to spend more time tuning them for fetch size and indexes to get decent performance. Forget the fact that sometimes you have to implement dynamic SQL using SQL which is a nightmare of repetitious quotation marks with any complex data structure. Foreign servers amplify the escaped quotes. My stomach turns remembering having to write that code...
Is there a npm package for something like this? React/node is not my daily driver.
We build our JS SDK by wrapping Knex (nice query builder, not quite an ORM) and injecting our logic to the connection process. You could do the same.
The issue is that there are at least 5 popular JS ORMs, so we don't get the same solve-it-once that we get in the Rails ecosystem.