Advantages of an in-memory Database in SQLite
I read about the keyword ":memory:" from a book on SQLite today but it only says what it is, how to use and the explanations were too short. So I searched for more information here, but couldn't get SQLite specific info.
What advantages does ':memory:' mode have? (When do I need this?)
In-memory database's performance is faster?
Do I still need to use transactions on an In-memory database?
Answer
A SQLite in-memory database's primary advantage is performance: rather than reading and writing to disk, it will keep the whole database in memory. Memory is much faster than disk. You'll see the biggest performance improvement with a spinning disk or a heavily IO loaded server, and less with an SSD.
However, this isn't a panacea for badly written queries and tables. Before you reach for an in-memory database to improve performance, be sure to optimize your table design, queries and indexes.
The main disadvantages are once the process closes the database is gone. And the database cannot be bigger than available memory.
Commits may be faster since there's no need to write to disk, so autocommit mode might be faster, but transactions should still be used for data integrity purposes.
Note that a temporary SQLite database that doesn't get too big will probably be stored in memory.
Because of its drawbacks, and because you have much less memory than storage, before committing to an in-memory database try a temporary database instead. This is done by using '' for the database filename. This will write to a temp file, but buffer the work in a memory cache. It is the best of both worlds, you get improved performance without using too much memory.
Even though a disk file is allocated for each temporary database, in practice the temporary database usually resides in the in-memory pager cache and hence is very little difference between a pure in-memory database created by ":memory:" and a temporary database created by an empty filename. The sole difference is that a ":memory:" database must remain in memory at all times whereas parts of a temporary database might be flushed to disk if database becomes large or if SQLite comes under memory pressure.
Profile and benchmark your application to be sure it will result in a performance improvement, consider whether it would be better to optimize your queries and add indexes instead, and be sure it's ok if your data disappears.