24

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.

  1. What advantages does ':memory:' mode have? (When do I need this?)

  2. In-memory database's performance is faster?

  3. Do I still need to use transactions on an In-memory database?

Jenix
  • 2,996
  • 2
  • 29
  • 58
  • 2
    Main advantage, performance. When the connection to the database is closed, it disappears. See http://stackoverflow.com/questions/849679/temporary-in-memory-database-in-sqlite – Dijkgraaf Sep 28 '15 at 23:34
  • Thanks, but I already read the thread and it wasn't clear about the performance. I also read some threads on other DBMS such as MySQL, and those said temporary databases were not faster. That's why I asked here.. – Jenix Sep 28 '15 at 23:40
  • 2
    There is a difference between a "temporary database/table" and a "memory database" A temporary one can still be on disk. – Dijkgraaf Sep 28 '15 at 23:58
  • I didn't know that, thanks!! Then what you said is right? Faster performance?? – Jenix Sep 29 '15 at 00:04
  • Not necessarily that much faster: http://stackoverflow.com/questions/764710/sqlite-performance-benchmark-why-is-memory-so-slow-only-1-5x-as-fast-as-d YMMV – CAD bloke May 12 '17 at 10:24

1 Answers1

48

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.

Schwern
  • 153,029
  • 25
  • 195
  • 336
  • Wow thanks!!! Could you answer to No.3 too? I always use transactions due to performance, this would help when using in-memory databases too? – Jenix Sep 29 '15 at 00:27
  • 3
    @Jenix Regardless of the performance, you should always be using transactions for data integrity purposes. – Schwern Sep 29 '15 at 00:39