I don't think you can have all of the above. The "should never get corrupted" 
part of SQLite comes from having the data in 2 non-volatile storage files 
during a commit/checkpoint. Problems while writing data to the main file are 
covered by having the rollback journal or WAL on disk to recover from in the 
event of death.

The memory journal mode says "I care about speed to the exclusion of recovery" 
and means a problem at the wrong time will leave you with a corrupted database. 
Without that second non-volatile store of the data then there will always be a 
chance of corruption while writing to the one and only permanent file that 
you're using.

WAL gets you around the writer blocking readers problem, but still uses the 
stored-in-2-places technique to prevent disaster.

(Sketchy on implementation details from here out)

With all your access being from 1 connection on the local computer, I would 
think that the best-case for you would be to write your own VFS that keeps a 
WAL in-memory so that you can have the concurrent access and speed, but then 
for corruption avoidance come checkpoint time writes the pages to be updated to 
a non-volatile rollback journal before updating the main db file. That way you 
could still have multiple in-memory non-recoverable transactions and commits 
between recoverable checkpoints. You'd still have to write to both the journal 
and to the main file, but would only have to do so once per page updated 
between checkpoints rather than potentially multiple times.

At least I think that's an option anyway. Those more familiar with what's 
actually possible and actually useful will now rip apart my suggestion.

-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Pavel Cernohorsky
Sent: Wednesday, April 04, 2018 10:01 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] In memory only WAL file

Hello, does anybody know if there is some possibility to not have WAL 
file as a normal file on the disk, but only in memory? I understand that 
all the modifications to the database would get lost in case of the 
application / OS crash, but for my application, I only need the level of 
durability based on checkpointing. I just need to guarantee that all the 
data are properly written to the main database and synchronized to disk 
when manual (or even automatic) WAL checkpoint is called, but I do not 
care if I loose data in between the checkpoints. Of course database 
should never get corrupted.

My goal is to limit the number of IOps being performed to the disk. 
Currently I use "PRAGMA synchronous = 1" and there is only one process 
manipulating the database (multiple reader threads, only one writer 
thread at one moment in time). Or if it is not possible to have WAL in 
memory only, is there something like “PRAGMA wal_synchronous = 

Thanks for suggestions, kind regards,

sqlite-users mailing list
sqlite-users mailing list

Reply via email to