I've been using SQLite in several production projects for six months, and I've encountered some surprising aspects.

How?

I use SQLite in three Django apps and a Node-based MMORPG that's still in the early stages of development."

The Django apps have 2 web workers (= processes) each and 2 of the 3 apps have additional background worker processes. One app is running 24 background worker processes.

The MMORPG snapshots the game state into a single SQLite database within a Node process.

Why?

Because fly.io and The Orange Website said it's a good idea. No seriously, SQLite received so much goodwill and attention in the last few years that I decided to have a closer look.

I'm a minimalist in my personal life and I'm convinced that you should start out simply, like stupidly simple.

In my opinion, Postgres is boring but not necessarily simple. There are many knobs that can be tweaked. I don't think you need to decide between one or the other, I think Postgres is a natural step from SQLite once you out grow SQLite.

The first thing I've come to appreciate about SQLite is its documentation.. I felt like I could wrap my head around the tool in a matter of hours. It's very honest about what SQLite can do under which circumstances. And more important, what it can't do.

1. In-memory SQLite is not too exciting

---

At least not in my JavaScript benchmarks where I compared SQLite in-memory and SQLite backed by a file. I tested inserting and updating 1k, 10k and 100k rows.

This one surprised me because in my mind

• memory is fast and
• I/O is slow

I guess this is what years of dealing with databases over networks by using ORMs does to you.

I had hoped that changing

const db = new Database("db.sqlite3");

to

const db = new Database(":memory:");

would speed up everything by x100 and all my problems would go away.

Similarly, when I switched my Django tests from file to in-memory, there wasn't much of a difference.

I suspect that the data in my benchmarks was too small, so that SQLite never flushed the page cache. I know that in-memory SQLite has its uses, but personally, I didn't notice a significant difference in my everyday work as a web developer.

2. SQLite is surprisingly fast

---

At least compared to native JavaScript data structures. I ran some benchmarks for inserting and updating entities stored as JSON blobs. I compared

const db: Map<string, Entity> = new Map();

with storing entities in a in-memory SQLite database using better-sqlite3.

What's your intuition on this?

The JavaScript Map was only 1-2 orders of magnitude faster! This blew my mind. I expected SQLite to be at least 1000x slower than a data structure built for inserting and retrieving things by key, with all the V8 goodness and what not.

3. SQLite feels surprisingly concurrent

---

Of course, SQLite is not truly concurrent. No matter what you do, there can only be a single writer process. But with the following setup

@receiver(connection_created)
def configure_sqlite(sender, connection, **kwargs):
    if connection.vendor == "sqlite":
        cursor = connection.cursor()
        cursor.execute("PRAGMA journal_mode = WAL;")
        cursor.execute("PRAGMA busy_timeout = 5000;")
        cursor.execute("PRAGMA synchronous = NORMAL;")

SQLite certainly feels quite concurrent.

Many consider Python a "slow blocking" language. In one of my Django apps I have 24 background worker processes writing to the database 4-5 times per minute and 2 web workers with 1-2 writes per second. In total there are 26 processes potentially writing at the same time.

The SQLite driver complains about a locked database maybe once every week. So far, none of the human writers have been affected. All the stack traces came from background workers which simply retry.

There is plenty of room for improvements. For starters, the workers probably shouldn't write directly to the application database but instead to some worker database.

Conclusion

I'll continue testing SQLite with various workloads to see how it responds. Let's see how long I can keep going without having to switch to Postgres.