Should I use Multitenancy or multiple sqld processes to optimize for concurrency?

[jakelowen]

Greetings - I Love this project.

I'm exploring the feasibility of transitioning to libsql for an application architecture that extensively relies on interacting with multiple databases concurrently. Our current setup involves managing a collection of discrete SQLite databases. When processing user actions, our system often performs simultaneous queries across these distinct databases, aggregating the results before presenting them to the user. Traditionally, this has been handled through multiple Node.js worker threads, each querying separate SQLite databases.

With the introduction of libsql and its network interface, I'm considering a shift towards a more streamlined approach. Specifically, I'm intrigued by the potential to utilize Promise.all() for executing concurrent queries, as opposed to managing multiple worker threads. This adjustment seems promising for simplifying our data retrieval logic significantly.

Given this context, I am weighing the benefits of adopting a multi-tenancy architecture within libsql against the alternative of deploying separate sqld instances for each database. My core inquiry revolves around performance implications:

• Would leveraging libsql's multi-tenancy capabilities offer comparable, if not superior, performance when executing concurrent queries across numerous databases (e.g., 100 databases), relative to managing an equivalent number of separate sqld instances each on their own port?
• Are there notable efficiency gains or trade-offs in network overhead, resource utilization, or query processing times between these two approaches?

Understanding the performance dynamics in these scenarios is crucial for my migration strategy, especially considering the potential scale of databases and the concurrency of operations involved.

I appreciate your insights and look forward to your recommendations.

[haaawk]

Hi @jakelowen
Let me try to address some of this.

It is usually recommended to use multi-tenancy instead of multi sqld processes. That's usually more economical. If you don't care about the cost then having each db in a separate sqld process might allow to reach higher throughput. On the other hand, sqld allows ATTACH so when all dbs are in a single sqld process you can potentially query them together with a single SQL.

Network is a complicated matter. The lowest overhead caused by the network is probably in single digit ms.