SQLite async connection pool for high-performance

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!

How does this help with the second issue, the write locks?

Around what amount of load would you say the overhead of opening/closing becomes a problem?

No 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.

Doesn’t SQLite have its own in-memory cache? Is this about having more control re cache size?

if 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)

yes, per "open connection", hence why not closing+reopening connections all the time helps the cache ;)

Writers 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.

I 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.

Interesting, were there any warning signs beyond general query slowdown?

No 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.

Is there a significant advantage of the sqlite in-memory page cache over the page cache that's already included with the operating system?

Yes: 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.

WAL doesn’t cure concurrency issues for SQLite. WAL plus single writer, multiple reader threaded is required. It’s blazing fast though.

It 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.

I think this is mentioned in the docs https://www.sqlite.org/wal.html

Important 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.

The 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.

Let'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...

The 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_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)

I did read it. For whatever reason, automatic checkpoints basically would stop from time to time, and the WAL file would start growing like crazy.

In the end I wrote an external process that forced a checkpoint a few times a day, which worked. I came across other exasperated people in various dark corners of the Internet with the same symptoms.

I'm mostly with you.. it would still be nice if the title reflected the language limitation/feature.

And 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!