🔗 Optimizing SQLite for servers

SQLite is often misconceived as a "toy database", only good for mobile applications and embedded systems because it's default configuration is optimized for embedded use cases, so most people trying it will encounter poor performances and the dreaded SQLITE_BUSY error.

But what if I told you that by tuning a few knobs, you can configure SQLite to reach ~8,300 writes / s and ~168,000 read / s concurrently, with 0 errors, on a ~40€ / m commodity virtual server with 4 vCPUs (details and code in the appendix).

Let's say that your server application is making in average 8 database read queries per request, you could, in theory, handle ~21,000 requests per seconds, or ~1,814,300,000 requests per day, for ~40€ per month, bandwidth included, not bad! (In practice you may not be able to do that: the server's bandwidth will be the limiting factor)

And this is before talking about tuning the garbage collector, caching and CDNs.

For more number crunching, take a look at Expensify's article: Scaling SQLite to 4M QPS on a single server in 2018.

TL;DR?

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 1000000000;
PRAGMA foreign_keys = true;
PRAGMA temp_store = memory;

Use BEGIN IMMEDIATE transactions.

writeDB.SetMaxOpenConns(1)
readDB.SetMaxOpenConns(max(4, runtime.NumCPU()))

Use STRICT tables.

continue reading on kerkour.com

⚠️ This post links to an external website. ⚠️