21–30 of 264 posts
We do it, but everyone gets the same schema and same app. .net/IIS/sql server and when we update the schema, we apply to all dbs via in-house scripts. It provides security warm fuzzies to our clients that their data is separated from other clients. If you want to try and version the app/schema for different accounts, that’s where your headaches are, regardless of db model
I currently work at a company where customers have similar concerns around data privacy, but we've been to continue using a single multitenant DB instance by using PostgreSQL's row level security capabilities where rows in a table are only accessible by a given client's database user:
https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html
We customized both ActiveRecord and Hibernate to accommodate this requirement.
That said, there are some interesting challenges with this model like schema migration and DB backups etc. some of which can be easily overcome by smartly using workers and queuing. We run migration per schema using a queue to track progress and handle failures. We also avoid migrations by using Postgres JSON fields as much as possible. For instance, creating two placeholder fields in every table like metadata and data. To validate data in JSON fields we use JSONSchema extensively and it works really well.
Probably you also need to consider application caching scenarios. Even you managed to do one database per customer running Redis instance per customer will be a challenge. Probably you can run Redis as a docker container for each customer.
MySQL connections can be reused with database per tenant. Rack middleware (apartment gem) helps with managing applying migrations across all databases, and with the mechanics of configuring connections to use a tenant based on Host header as requests come in.
For async jobs in Java, allocating a connection can idiomatically only be done via a callback - e.g. `executeInTenantContext(Tenant tenant, Runnable block)` - which ensures that all connections handed out have the right tenant selected, and everything is reset to the default tenant (an empty database) when exiting. Per-tenant jobs either iterate through all tenants, or have a tenant parameter under which they get executed, and the rest of the code can be more or less tenant unaware.
It gives you the "freedom" to move tenants to separate servers, or consolidate them into single servers, if tenants are much larger or much smaller than expected. In reality this is sufficiently painful it's mostly illusory. We're looking at Vitess to help scale out, and move away from a mix of multi-tenant servers and single-tenant servers.
The inability to reuse database connections would be a huge performance hit. In a traditional webapp backend, you have a pool of connections to the database. User01 hits your service, and grabs a connection off the pool. User02 does the same, and so on. These connections get put back in the pool for reuse once a user is done with them. In your design, every time a user hits your service, a new connection, specific to…
Rails migrations work reasonably well with apartment gem. Never had a problem with inconsistent database migrations. Sometimes a migration will fail for a tenant, but ActiveRecord migrations records that, you fix the migration, and reapply, a no-op where it's already done.
We don't use a single mysqld for every tenant mind, it's not like migrating tenants is completely serialized.
The inability to reuse database connections would be a huge performance hit. In a traditional webapp backend, you have a pool of connections to the database. User01 hits your service, and grabs a connection off the pool. User02 does the same, and so on. These connections get put back in the pool for reuse once a user is done with them. In your design, every time a user hits your service, a new connection, specific to…
If connecting to your DB is significantly increasing your page times, you've got seriously fast pages. Even back when I was working with a MySQL database regularly in 2010, connect + login was 5 ms at maximum (and I think it was much less, I just don't remember that far).
> If you're thinking about using something like SQLite, you will hit a hard wall when the OS isn't able to open any more file descriptors, as well.
You could always just only keep open the most recently used 1 Million databases. It's pretty easy to tune the FD limit, FreeBSD lets you set it to one FD per 16k of ram without modifying the kernel, but it's a challenge to be so memory and cpu efficient that that's a limit.
All that said, it really depends on the application. If this is consumer facing, and each account is an end user, one database per user is probably excessive overhead; one database (or sharded, if you've got the users) or one (flatish) file per user makes a lot more sense.
If it's business facing, than one database per account could make sense. You would have isolation between customers and could put big customers on dedicated boxes and let the customer drive upgrades and migrations etc. Just please please please consider that corporations merge and divide all the time, don't be like G Suite and not offer a way to merge and divide accounts to reflect their corporate ownership.
Scaling is super easy since you can move a db to another host.