Hacker News Reader

Comment by mholt | Hacker News Reader

This article doesn't describe any of their methods.

> It was clear that serialization was the culprit, so we switched to Protobuf instead of stringifying the embeddings. This resulted in considerable gains. For 100,000 embeddings, writing took 6.2 seconds, reading 12.6 seconds and the disk space consumed dropped to 440 MB.

Doing what though? What was their schema like? What are their indices? Primary keys? Column types? Query structure?

It's lacking so little information I find this article not useful and not interesting to me. Which is a bit disappointing since I was hoping to learn something relevant.

View on HN

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

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...

Sorry, we should have linked to the DB Schema.

Here you go: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...

Autoincrement is usually not needed in SQLite, and adds a lot of overhead to every insertion since it needs to read and update the sqlite_sequence table on every insert.

https://www.sqlite.org/autoinc.html

One obvious explanation is the lack of indexing on the column you're searching over. It's currently an opaque blob, but a design that actually uses the database's features will serve you much better.

What your article is really saying is, "SQLite isn't a good fit for our data that we didn't want to serialize differently to be efficient with SQLite." And that's fine, it looks like you found the right tool for your data and requirements, but the title is click-baity and isn't really about SQLite.

Reply on news.ycombinator.com