Why sqlx-sqlite use std::thread as background thread directly?

[SoloJiang]

In my view, every sql operation will create a new thread to execute statement. Wouldn't that create a lot of waste of resources? I think there might be a need to manage through thread pools here.
source code:

sqlx/sqlx-sqlite/src/connection/worker.rs

Line 82 in 42ce24d

thread::Builder::new()

[abonander]

Answered on Discord:

The problem with using a threadpool is that if you have fewer threads than connections, then some queries will have to wait to run until another one completes.

And if you use Tokio's blocking threadpool, keep in mind that if there are more blocking tasks than there are threads, it spawns more threads up to a very high limit, 512 by default. If you're trying to limit the number of threads that are spawned, using the blocking task pool isn't it.

You could spawn and shut down threads when connections aren't being used, but we already have a way to control that, by configuring an idle timeout on Pool. And if you're worried about how many threads get spawned, just use fewer connections, or just one; SQLite doesn't scale to very well to lots of threads anyway.

Using Pool<Sqlite> is effectively already using a thread pool.

[SoloJiang]

I understand your point, but what I want to say is that SQLite is primarily used in embedded device scenarios, where frequent thread creation can be resource-intensive, and these devices are particularly sensitive to such overhead. Additionally, the database itself doesn’t have optimizations related to max-connections. The max-connections concept is more of a design choice in sqlx to standardize behavior across different databases and help users control resource consumption at the application layer.

Furthermore, as you mentioned, if the number of threads is less than max-connections, it could indeed cause queuing, which might confuse users. However, is there a way to optimize this scenario? For example, could we align the maximum number of threads with the max-connections setting?
In our scenario, when a user’s app is used for more than an hour, it might create over 1,000 threads. Of course, these threads are terminated as business operations complete, so the number of truly persistent threads is likely far lower. However, as I mentioned earlier, this kind of design could still lead to unnecessary waste of power and resources for the user.
You mentioned using spawn_blocking to control the total number of threads. I’m currently using sqlx indirectly through SeaORM. How can I implement this control with as few code changes as possible?