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.

1 Answer 1

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.

3
  • I heard about the WAL mode which seems to help with concurrency. I am expecting multiple concurrent reads but not the too many concurrent rights. Some latency should be acceptable! Nov 2, 2023 at 13:47
  • 1
    @HarshalPatil Yea, best way to find out what you're up against will be to test with your normal workload. Best of luck!
    – J.D.
    Nov 2, 2023 at 13:51
  • @HarshalPatil Not required, but if you found this answer helpful, feel free to accept it so other users see it was helpful too.
    – J.D.
    Nov 4, 2023 at 12:25

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.