SQLite Forum

memory vs mmap
A memory-mapped file is just a file on backing store with a big cache.  Sometimes you have to access backing store.  For instance, you have to check the backing store to find out whether a file with that name already exists.  A journal file can be used, so checks need to be done about whether a file with that name exists, whether it's time to do a checkpoint, whether the journal file can be shrunk, etc..  And even if everything involved is in solid state, that's extra operations, and extra time needed for the operations.

A file in <code>:memory:</code> involves only access to memory.  It's all one big database, so you don't need to check whether a file already exists.  No journal can be maintained (because if you lose memory you will obviously lose all your data anyway).