edge-db-benchmarks

This[1] has improved the situation considerably! ``` [log] SQLite: 100000 embeddings inserted in 2635 ms [log] SQLite: 100000 embeddings retrieved in 561 ms ``` Isar is still ~2x faster, but there's room for optimization. Thanks a bunch for sharing this, I will update the post. [1]: https://github.com/ente-io/edge-db-benchmarks/commit/51ec496...

https://github.com/ente-io/edge-db-benchmarks/blob/43273607d... 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... )

Nice catch, thank you! I was playing around with the offsets incorrectly, while attempting to reduce the memory footprint on a low-end Android device. I've updated the code[1] to read all embeddings in one shot, and I'm now measuring the time to read and the time to deserialize separately. On an iPhone simulator running on an Apple M3 Pro, this is what it says: ``` [log] SqliteDB fetch all took: 1572 ms [log] SqliteDB deserialization took: 4268 ms ... [log] Isar: 100000 embeddings retrieved in 616 ms ``` [1]: https://github.com/ente-io/edge-db-benchmarks/commit/e02c3d3...

Hello, OP here! The code we used for running the benchmarks is available here[1]. If you have suggestions on how we could speed up the deserialization during reads, please do share. We'd be happy to go back to SQLite. [1]: https://github.com/ente-io/edge-db-benchmarks

Look here: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d... SELECT $columnEmbedding FROM $tableName LIMIT 10000 OFFSET $offset They're not using any indexes, because they're doing offset pagination [1] in a loop. And offset pagination is causing them to scan 550K total rows [2] instead of 100K. OP, change your code. You're probably wrong about SQLite being slower than Isar, or at least it must be very close. [1] https://use-the-index-luke.com/no-offset [2] 10K + 20K + 30K + 40K + 50K + 60K + 70K + 80K + 90K + 100K = 550K

The code was always calling the latter[1], I had pasted the incorrect link. [1]: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...

My bad, I linked to the wrong portion of the code. Here's the updated link[1]. We are executing the entire write in a single batch query. From what we observed, and have documented, it is the serialization that is causing the bottleneck, not the write itself. Also, more than writes, it is the latency associated with deserializing the read data into models that pushed us to look at options outside SQLite. [1]: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...

Hey, the writes were batched[1]. What is expensive here is the serialization. [1]: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d... Edit: Updated the permalink

Sorry, we should have linked to the DB Schema. Here you go: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...

You don't seem to be using the WAL or any other SQLite optimizations. https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/... Any reason for that?

Hello! OP here. Snippets are available @ https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/...