Improve SQLite performance

[rolznz]

From https://x.com/meln1k/status/1813314113705062774?s=46&t=B4-aN9

How I squeeze 60K RPS out of SQLite on a $5 VPS:

There are tons of tutorials on how to setup SQLite, and sometimes they contradict each other. Here is what worked for me.

1. Configuring PRAGMAs. We need to send the following PRAGMA commands right after opening the connection:

PRAGMA journal_mode = WAL;

• enables write-ahead log so that your reads do not block writes and vice-versa.

PRAGMA busy_timeout = 5000;

• sqlite will wait 5 seconds to obtain a lock before returning SQLITE_BUSY errors, which will significantly reduce them.

PRAGMA synchronous = NORMAL;

• sqlite will sync less frequently and be more performant, still safe to use because of the enabled WAL mode.

PRAGMA cache_size = -20000;

• negative number means kilobytes, in this case 20MB of memory for cache.

PRAGMA foreign_keys = true;

• because of historical reasons foreign keys are disabled by default, we should manually enable them.

PRAGMA temp_store = memory;

• moves temporary tables from disk into RAM, speeds up performance a lot.

Do NOT use cache=shared! Some tutorials recommend configuring it, but this is how you get nasty SQLITE_BUSY errors. It is disabled by default, so you don't have to do anything extra.

2. Use immediate transactions
   If you know that transaction can possibly do a write, always use BEGIN IMMEDIATE or you can a get SQLITE_BUSY error. Check your framework, you should be able to set this at the connection level.

3. Open two connection pools
   Another trick is to open 2 connection pools, one for reads only and another for reads/writes. Set the connection limit of write pool to 1, and the connection limit of the read pool to some reasonably high number, e.g. number of your CPU cores.

4. Bonus: how I configure sqlite with Go
   Here is the code in go I use to configure the sqlite connections:

func SQLiteDbString(file string, readonly bool) string {

connectionParams := make(url.Values)
connectionParams.Add("_journal_mode", "WAL")
connectionParams.Add("_busy_timeout", "5000")
connectionParams.Add("_synchronous", "NORMAL")
connectionParams.Add("_cache_size", "-20000")
connectionParams.Add("_foreign_keys", "true")
if readonly {
connectionParams.Add("mode", "ro")
} else {
connectionParams.Add("_txlock", "IMMEDIATE")
connectionParams.Add("mode", "rwc")
}

return "file:" + file + "?" + connectionParams.Encode()
}

func OpenSqliteDatabase(file string, readonly bool) (*sql.DB, error) {

dbString := SQLiteDbString(file, readonly)
db, err := sql .Open("sqlite3", dbString)

pragmasToSet := []string{
"temp_store=memory",
}

for _, pragma := range pragmasToSet {
_, err = db.Exec("PRAGMA " + pragma + ";")
if err != nil {
return nil, err
}
}

if readonly {
db.SetMaxOpenConns(max(4, runtime.NumCPU()))
} else {
db.SetMaxOpenConns(1)
}

return db, nil
}