It's the first time I've heard of Isar[1] though. I'm always surprised at how many solid-but-underused Apache projects are out there, chugging along.
[0] https://phiresky.github.io/blog/2020/sqlite-performance-tuni...
Also, even if it is done right, inserting 100,000 of 512 byte objects will be around 50MiB, which will be the point to trigger checkpointing WAL file into the main db, which can further slow down SQLite.
https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf
“To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.”
“It is not possible to change the page size after entering WAL mode.”
“In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.”
https://www.sqlite.org/lang_attach.html
“SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.”
Still dont think this is sqlite's fault. Serialization is something outside sqlite and a cost you have to pay no matter what you are using.
Within reads the most expensive part was the re-construction of models from the rows returned by SQLite.
While deserialization is not the responsibility of the database, the overall throughput with SQLite was too low for it to serve our usecase.
If this is how you are doing reads, this is your problem. Limit/offset reads are slow.
You'll get much better results if you do a proper pagination implementation.
This article describes the problem and solution that'd give you much better read results.
https://use-the-index-luke.com/sql/partial-results/fetch-nex...
(don't forget page 2 https://use-the-index-luke.com/sql/partial-results/window-fu... )
Removing the offsets has unfortunately not sped things up, since it's the step post reading the entries (deserialization) that is the bottleneck[1].
Since SQLite does not support Lists, Protobufs came across as the best way to serialize the data at hand.
If there's more native way to solve this problem within SQLite, please let me know!
Snippets are available @ https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/...
https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/...
Any reason for that?
https://flutterawesome.com/high-performance-asynchronous-int...