SQLite Forum

Tips for getting SQLite under Python to perform better with more RAM?
I have the similar situation in my python web api app: many read, few write. 

Here are my observations.

Page cache is private by default, so it lives only while connection is alive.
So if your application opens a new connection for each new request, page cache won't help much, because each connection will start "cold" with an empty cache and read data from disk.

I've tested 3 approaches to speed up in such case:

* use connection pool and private cache. Slightly worse then next variant in my tests.

* use shared cache for all connections.
At app start you create "master connection" to ensure the shared cache alive all app run time. So next db connections will not start "cold", but "warm" using previous cache data.

But keep in mind that shared cache is [not recommended to use](https://sqlite.org/forum/forumpost/9441200af2e359c9?t=h) and in general it is intended for other cases. But it really works for me, sorry for the "harmful advice" :-)

In the extreme case (page cache can contain entire db and all db data loaded into cache) it becomes in-memory db with disk-sync.

So, the third approach:

* use in-memory db if you have enough RAM, few writes and can reduce the requirements for persistence when app fails.

At app start create "master connection" to `file::memory:?cache=shared`, load data from disk using backup api.
Next your app uses this in-memory db when read. On write you have problems - you have to write into in-memory db and disk db. I haven't done this part yet, but you can use triggers + attached disk-db (so you have single transaction, but only if your disk-db not in WAL-mode - else you have separate transaction for each db), or just write python code and hope that both db will be successfully updated (if not - you can just reload in-memory db from disk - and let the whole world wait...).

I have tested my app in _read-only_ scenario with these modifications under heavy load - shared cache is faster, in memory-db is several times faster.

Since [3.36.0](https://www.sqlite.org/releaselog/3_36_0.html) you can create shared in-memory db in another way - `file:/memdbname?vfs=memdb`. [Source code commit](https://www.sqlite.org/src/info/533fffc4a39b01c3) says this mode doesn't use shared cache. But i have not tested it vs `:memory:` yet.
@drh [advices](https://www.sqlite.org/forum/forumpost/0359b21d172bd965?t=h) use `memdb`, but the question is not explained in the docs yet.

By the way, there are big problems with updating the sqlite version of the python sqlite3 module. I can advice you [`pysqlite3`](https://github.com/coleifer/pysqlite3) module - it allows you to compile a standalone python module using sqlite3 amalgamation of any required version. The interface is similar to the standard python sqlite3 module.
It is hardly googled because shadowed by `pysqlite` repo, which is deprecated after including it's code in the python standard library.

P.S. Excuse me for my english