Is it possible to scale SQLite databases in memory using concurrency?

Question

To prevent the SQLite database from being cleared in memory, you must use the same connection to access the database. However, using the same connection causes SQLite to synchronize access to the database. Thus, if I have many threads that read through the database in memory, on a multi-core machine it is slower than the same code that works with the file database.

Is there a way to get the best of both worlds? That is, a database in memory that allows multiple simultaneous calls to the database?

Answer 1

The answer is no. I asked the SQLite user group and received a response from Pavel Ivanov:

No, SQLite does not support full concurrent access to any database. only concurrency can you earn by having a database on disk without a shared cache (so there are actually several copies of the database in memory). Of course, I am not considering the option of concurrency from different processes.

Answer 2

If the page size and cache size are large enough to contain the entire database, read operations will be performed from the cache, and performance will be almost equal to the database in memory.

Answer 3

from here I realized that the answer is yes http://www.sqlite.org/faq.html#q6