4 ms·
If you’re building Python async apps (FastAPI, background jobs, etc.) with SQLite, you’ll eventually hit two issues - Opening/closing connections is fast, but
by slaily 1y ago
If you’re building Python async apps (FastAPI, background jobs, etc.) with SQLite, you’ll eventually hit two issues
- Opening/closing connections is fast, but not free—overhead adds up under load
- SQLite writes are globally locked
aiosqlitepool is a tiny library that adds connection pooling for any asyncio SQLite driver (like aiosqlite):
- It avoids repeated database connection setup (syscalls, memory allocation) and teardown (syscalls, deallocation) by reusing long-lived connections
- Long-lived connections keep SQLite's in-memory page cache "hot." This serves frequently requested data directly from memory, speeding up repetitive queries and reducing I/O operations
- Allows your application to process significantly more database queries per second under heavy load
Enjoy!
- slashdev 1y agoHow does this help with the second issue, the write locks?
- ncruces 1y agoNo idea if it applies, but one way would be to direct all writes (including any transaction that may eventually write) to a single connection. Then writers queue up, while readers are unimpeded.
- dathinab 1y agoif you enable WAL mode with sqlite then readers are not blocked by writer so only writers queue up without needing any special case handling to archive it (in general you _really_ should use WAL mode if using sqlite concurrently, you also should read the documentation about WAL mode tho)
- ncruces 1y agoWriters won't queue up, rather they'll storm the place, taking turns at asking “can I go now” and sleeping for (tens, hundreds of) milliseconds at a time. This only gets “worse” as computers get faster: imagine how many write transactions a serial writer could complete (WAL mode and normal synchronous mode) while all your writers are sleeping after the previous one left, because they didn't line up? And, if you have a single limited pool, your readers will now be stuck waiting for an available connection too (because they're all taken by sleeping writers). It's much fairer and more efficient for writers to line up with blocking application locks.
- rich_sasha 1y agoI was running into some horrendous issues with WAL, where the WAL file would grow boundlessly, eventually leading to veery slow reads and writes. It's fixable by periodically forcing the WAL to be truncated, but it took me a lot of time and pain to figure it out.
- normie3000 1y agoInteresting, were there any warning signs beyond general query slowdown?
- rich_sasha 1y agoNo warning signs and very little about it on the Internet. Just performance slows to a grind. Also hard to replicate. If I had a blog, I'd be writing about it.
- normie3000 1y agoAnd how big was the WAL file getting compared to normal? As someone running SQLite in prod it would be comforting at least to have some heuristics to help detect this situation!
- bawolff 1y agoI think this is mentioned in the docs https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- dathinab 1y agoThe is why I said read the WAL doc page in a different answer ;) They do point out the risks here: https://sqlite.org/wal.html#avoiding_excessively_large_wal_files https://sqlite.org/wal.html#avoiding_excessively_large_wal_f... sqlites design makes a lot of SQL concurrency synchronization edge cases much simpler as you can rely on the single writer at a time limitation. And it has some grate hidden features for using it as client application state storage. But there are use-cases it's just not very good at and moving from sqlite to other DBs can be tricky (if you ever relied on the exclusive write transaction or the way cells are blobs which can mix data types, even it it was by accident)
- le-mark 1y agoWAL doesn’t cure concurrency issues for SQLite. WAL plus single writer, multiple reader threaded is required. It’s blazing fast though.
- mostlysimilar 1y agoAround what amount of load would you say the overhead of opening/closing becomes a problem?
- jitl 1y agoIt depends hugely on how you decide to manage the connection objects. If you have a single thread / single core server that only even opens a single connection, then connection open overhead is never a problem even under infinite load. The two main issues w opening a connection are: 1. There is fixed cost O(database schema) time spent building the connection stuff. Ideally SQLite could use a “zygote” connection that can refresh itself and then get cloned to create a new one, instead of doing this work from scratch every time. 2. There is O(number of connections) time spent looking at a list of file descriptors in global state under a global lock. This one is REALLY BAD if you have >10,000 connections so it was a major motivator for us to do connection pooling at Notion. Ideally SQLite could use a hash table instead of a O(n) linear search for this, or disable it entirely. Both of these issues are reasons I’m excited about Turso’s SQLite rewrite in Rust - it’s so easy to fix both of these issues in Rust (like a good hash table is 2 LoC to adopt in Rust) whereas in the original C it’s much more involved to safely and correctly fix the issue in a fork. Furthermore, it would be great to share more of the cache between connections as a kind of “L2 cache”; again tractable and safe to build in Rust but complicated to build in a fork of the original C. Notion uses a SQLite-backed server for our “Database” product concept that I helped write, we ran in to a lot of these kinds of issues scaling reads. We implemented connection pooling over better-sqlite3 Node module to mitigate these issues. We also use Turso’s existing SQLite C fork “libsql” for some connections since it offers a true async option backed by thread pool under the hood in the node driver, which helps in cases where you can have a bottleneck serializing or deserializing data from “node” layout to “SQLite c” layout or many concurrent writes to different DBs from a single NodeJS process.
- manmal 1y agoDoesn’t SQLite have its own in-memory cache? Is this about having more control re cache size?
- dathinab 1y agoyes, per "open connection", hence why not closing+reopening connections all the time helps the cache ;)
- bootsmann 1y agoIs there a significant advantage of the sqlite in-memory page cache over the page cache that's already included with the operating system?
- jitl 1y agoYes: SQLite needs to inspect the schema when it opens a new connection object and does some O(number of conns) lookups in global state during this process. It’s best to avoid re-doing this work.
- gwbas1c 1y agoImportant word: > Python Your repo and the readme.md don't say "python." The title of this post doesn't say "python." It took me a while to realize that this is for python, as opposed to a general-purpose cache for, say, libsqlite.
- kstrauser 1y agoThe tag at the top of the readme, under the title, shows which Python versions it supports. If it never mentioned Python at all, that would be the tipster.
- sjsdaiuasgdia 1y agoLet's see... There's tags showing what Python versions are supported. The root dir of the repo contains a 'pyproject.toml' file. The readme contains installation instructions for pip, poetry, and uv, all of which are Python package managers. The readme contains example code, all of which is in Python. The readme references asyncio, a Python module that is included in the standard library for Python 3. The 'Languages' widget on the page shows 99.2% of the repo's code is in Python. Every file not in the root dir has a .py extension. Yeah, I can see why it was so hard to figure out...
- tracker1 1y agoI'm mostly with you.. it would still be nice if the title reflected the language limitation/feature.