←back to thread

SQLite async connection pool for high-performance

(github.com)
197 points slaily | 5 comments | 11 Jul 25 10:26 UTC | HN request time: 0.729s | source
Show context
d1l ◴[14 Jul 25 21:08 UTC] No.44565326[source]▶
This is strange on so many levels.

SQLite does not even do network I/O.

How does sharing a connection (and transaction scope) in an asyncio environment even work? Won’t you still need a connection per asyncio context?

Does sqlite_open really take long compared to the inevitable contention for the write lock you’ll see when you have many concurrent contexts?

Does sqlite_open even register in comparison with the overhead of the python interpreter?

What is an asyncio SQLite connection anyways? Isn’t it just a regular one that gets hucked into a separate thread?

replies(4): >>44565652 #>>44568010 #>>44570930 #>>44572931 #
simonw ◴[14 Jul 25 21:37 UTC] No.44565652[source]▶
If you're talking to a 100KB SQLite database file this kind of thing is likely unnecessary, just opening and closing a connection for each query is probably fine.

If you're querying a multi-GB SQLite database there are things like per-connection caches that may benefit from a connection pool.

> What is an asyncio SQLite connection anyways? Isn’t it just a regular one that gets hucked into a separate thread?

Basically yes - aiosqlite works by opening each connection in a dedicated thread and then sending async queries to it and waiting for a response that gets sent to a Future.

https://github.com/omnilib/aiosqlite/blob/895fd9183b43cecce8...

replies(2): >>44565664 #>>44565791 #
d1l ◴[14 Jul 25 21:58 UTC] No.44565791[source]▶
That's even crazier - so you're using asyncio because you have a ton of slow network-bound stuff - but for your database access you are running every sqlite connection in it's own thread and just managing those threads via the asyncio event loop?
replies(3): >>44565996 #>>44567058 #>>44567114 #
1. quietbritishjim ◴[14 Jul 25 22:22 UTC] No.44565996[source]▶
What is crazy about that?
replies(1): >>44566123 #
2. lttlrck ◴[14 Jul 25 22:39 UTC] No.44566123[source]▶
Of course I don't know what the parent is thinking, but my thought is: why can't it be entirely event loop driven? What are the threads adding here?

(I don't know anything about that project and this isn't meant as a criticism of its design or a challenge - cos I'd probably lose :-) )

replies(3): >>44566276 #>>44566454 #>>44567547 #
3. maxbond ◴[14 Jul 25 22:57 UTC] No.44566276[source]▶
Python's asyncio is single threaded. If you didn't send them into a different thread, the entire event loop would block, and it would degenerate to a fully synchronous single threaded program with additional overhead.
4. mayli ◴[14 Jul 25 23:29 UTC] No.44566454[source]▶
Cause the sqlite-lib that python ships isn't async, and sqlite itself usually doesn't give an async API.
5. eurleif ◴[15 Jul 25 03:15 UTC] No.44567547[source]▶
SQLite doesn't have a separate server process; it does all of the work for queries in your process. So it's intrinsically CPU-heavy, and it needs threads to avoid blocking the event loop.

One way to look at is that with a client-server database and an async client library, you have a thread pool in the database server process to do the heavy lifting, and async clients talk to it via TCP. With SQLite, you have that "server" thread pool in the same process instead, and async "clients" talk to it via in-process communication.