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 = none_and_delete_wal_if_corrupted”? Thanks for suggestions, kind regards, Pavel _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
