SQLite Forum

SQLite Binary Log support!
Sorry for me bad expression ability, I will try again.

Sqlite has nice performance without lock,  on my test it handle 100K write transaction per second with memory db. This kind performance it can be used on a lot new area.

With EXCLUSIVE lock the performance reduce a lot, but you get the concurrent read/write transaction with multi thread.  in this mode the write thread will blocked to wait checkpoint.  On my test around 10K write transaction per second .

That is why I want binary log, for each write transaction generate a log entity, replay it in order you will get the same result on a diff db handle.  In this case you don't have to wait on LOCK for checkpoint. and without LOCK you get much higher performance.  And if you don't loss log then you also get persistence storage.

Use it with Raft will allow you have a system able to provide service even one of server crashed. 

The log entity should throw error if the SQL include RANDOM(), DATE() function. (so then don't get diff result when replay). It also should exclude some SQL like PRAGMA journal_mode=DELETE.  

The session extension is not able to handle scheme change, so it is not suit for the task. The binary log should generate for each transaction, session extension   is design for log transaction in batch, and it only work with primary key.

https://github.com/benbjohnson/litestream do what I need, it replicate SQLite change into other server. But it use cross process lock do do the job. which result in poor performance compare to in memory DB or LOCK-FREE mode(At least 1000 times fast). 

I hope I explain myself better.