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
FYI, I've once had few long-lived connection with wal, and wal file just goes exploded. Turns out sqlite won't truncate the wal if there are open connections.
I've been thinking about trying pre-serialization of SQLite commands to enable single-writer against a singleton SQLiteConnection using something like Channel<T> or other high performance MPSC abstraction. Most SQLite providers have an internal mutex that handles serialization, but if we can avoid all contention on this mutex things might go faster. Opening and closing SQLite connections is expensive. If we can re-use the same instance things go a lot faster.
> The primary challenge with SQLite in a concurrent environment (like an asyncio web application) is not connection time, but write contention. SQLite uses a database-level lock for writes. When multiple asynchronous tasks try to write to the database simultaneously through their own separate connections, they will collide. This contention leads to a cascade of SQLITE_BUSY or SQLITE_LOCKED errors.
I really don't get it. How would this help?
The benchmarks dont mention which journal mode sqlite is configured as, which is very suspicious as that makes a huge difference under concurrent load.
When your program does heavy concurrent writing and opens/closes connections for each write, most of them will fail with SQLITE_BUSY or SQLITE_LOCKED errors.
This situation can be managed with a pool of small (5 connections or less) to prevent spawning too many connections. This will reduce racing between them and allow write operations to succeed.
Regarding shared caching: Use `PRAGMA mmap_size` to enable mmap for reading your database. This way SQLite won't add another layer of page caching on top saving RAM and making things faster. SQLite only uses mmap for reads and will continue to write to the database with pwrite().
You must set it to a value higher than the size of your DB. I use:
17 comments
[ 0.18 ms ] story [ 36.0 ms ] thread- 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!
> 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.
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?
It really depends on what your workload looks like, but I think synchronous will win most of the time.
I really don't get it. How would this help?
The benchmarks dont mention which journal mode sqlite is configured as, which is very suspicious as that makes a huge difference under concurrent load.
This situation can be managed with a pool of small (5 connections or less) to prevent spawning too many connections. This will reduce racing between them and allow write operations to succeed.
You must set it to a value higher than the size of your DB. I use:
(1TB)