←back to thread

SQLite async connection pool for high-performance

(github.com)
197 points slaily | 1 comments | 11 Jul 25 10:26 UTC | HN request time: 0s | 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 #
crazygringo ◴[14 Jul 25 21:40 UTC] No.44565664[source]▶
> If you're querying a multi-GB SQLite database

In which case SQLite is probably the wrong tool for the job, and you should be using Postgres or MySQL that is actually designed from the ground up for lots of concurrent connections.

SQLite is amazing. I love SQLite. But I love it for single-user single-machine scenarios. Not multi-user. Not over a network.

replies(4): >>44565686 #>>44566048 #>>44567163 #>>44570593 #
simonw ◴[14 Jul 25 21:42 UTC] No.44565686[source]▶
Multi-GB is tiny these days.

I didn't say anything about concurrent access. SQLite with WAL mode is fine for that these days for dozens of concurrent readers/writers (OK only one writer gets to write at a time, but if your writes queue for 1-2ms who cares?) - if you're dealing with hundreds or thousands over a network then yeah, use a server-based database engine.

replies(3): >>44565990 #>>44567652 #>>44570472 #
brulard ◴[14 Jul 25 22:22 UTC] No.44565990[source]▶
I always had troubles having multiple processes get write access to the sqlite file. For example if I have node.js backend work with that file, and I try to access the file with different tool (adminer for example) it fails (file in use or something like that). Should it work? I don't know if I'm doing something wrong, but this is my experience with multiple projects.
replies(4): >>44566089 #>>44566171 #>>44566960 #>>44566990 #
simonw ◴[15 Jul 25 01:18 UTC] No.44566960[source]▶
That is because the default SQLite mode is journal, but for concurrent reads and writes you need to switch it to WAL.
replies(1): >>44569287 #
brulard ◴[15 Jul 25 09:00 UTC] No.44569287[source]▶
I use WAL basically everywhere. I thought that would fix my problem some time ago, but it didn't
replies(1): >>44570498 #
simonw ◴[15 Jul 25 12:35 UTC] No.44570498[source]▶
Are you seeing SQLITE_BUSY errors?

Those are a nasty trap. The solution is non-obvious: you have to use BEGIN IMMEDIATE on any transaction that performs at least one write: https://simonwillison.net/tags/sqlite-busy/

replies(2): >>44575696 #>>44577256 #
1. asa400 ◴[16 Jul 25 00:03 UTC] No.44577256[source]▶
This is correct, and one of the things I tell anybody who is considering using SQLite to watch out for. The busy timeout and deferred write transactions interact in a really non intuitive way, and you have to use BEGIN IMMEDIATE for any transaction that performs any writes at all, otherwise SQLite gives up and throws an error without waiting if another traction is writing when your traction attempts to upgrade from a read to a write.