1

I need to build a multi-tenant solution with a requirement for strict data isolation (meaning isolated Databases at tenant level). Even the authentication is different for each tenant?

I find that SQLite with all its compromises to be a right solution. But I understand the maintenance perils of having dedicated instance for each application and the costs it brings. So, I intend to have a multi-tenant single instance solution for the system where all users will use the same instance on the same domain (separated by url-paths). The database connection will be kept in memory for using LRU cache.

That brings me to my question:

  • Will this frequent opening of SQLite file cause considerable penalty if load increases moderately? Are there any benchmarks I can refer to?
  • Will this corrupt my DB files for any unforeseeable reasons I am not taking into account?

Note: My backend will be either Node.js or Elixir/OTP considering that application is IO bound CPU bound.

Harshal Patil
  • 119
  • 3

1 Answers1

1

Will this frequent opening of SQLite file cause considerable penalty if load increases moderately?

As great as SQLite is, it wasn't necessarily designed with concurrency built in mind as much as most other modern RDBMS. It's a lighter database, as the name implies. While it can handle parallelized writes, at a larger scale (depending on the workload), you may run into concurrency bottlenecks and issues due to file locking and write speed contention.

Will this corrupt my DB files for any unforeseeable reasons I am not taking into account?

No, not by the true meaning of database corruption or file corruption. But you can run into some unexpected errors that you would need to handle in your application appropriately perhaps with proper retry logic, to ensure logical data consistency.

J.D.
  • 40,776
  • 12
  • 62
  • 141