6 ms·

SQLite vs. ObjectBox vs. Isar

• jzebedee 3y ago
  I would have appreciated some more details on the benchmark. SQLite has notoriously slow writes in the default journal mode, but proper configuration and WAL/WAL2 mode[0] should be a starting point for any comparison. 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-tuning/ https://phiresky.github.io/blog/2020/sqlite-performance-tuni... [1] https://isar.dev/ https://isar.dev/
  • tehlike 3y ago
    Yeah code snippets for the benchmark would also be useful.
    • vishnumohandas 3y ago
      Hello! OP here. Snippets are available @ https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/main.dart https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/...
      • yellow_lead 3y ago
        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/databases/sqlite_db.dart https://github.com/ente-io/edge-db-benchmarks/blob/main/lib/... Any reason for that?
        • slingnow 3y ago
          My guess is because they are an SQLite beginner, but for engagement purposes on their blog they had to write something that sounds very authoritative, with a title like "SQLite Isn't Enough".
          • tehlike 3y ago
            That's a bit cynical. They may or may not be a newbie, but there is always something for everyone to learn, and saying they did this for engagement is something you don't know.
        • matharmin 3y ago
          The sqlite_async lib uses WAL mode by default.
          • yellow_lead 3y ago
            My bad, that's great. https://flutterawesome.com/high-performance-asynchronous-interface-for-sqlite-on-dart/ https://flutterawesome.com/high-performance-asynchronous-int...
  • liuliu 3y ago
    Agreed. From the look at it, a few things: WAL is not enabled, insertion is not wrapped in transactions. I did a benchmark a while ago and if everything done right it is much faster: https://dflat.io/benchmark/ https://dflat.io/benchmark/ 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.
  • vishnumohandas 3y ago
    More than writes, reads is what we struggled with. 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.
    • cogman10 3y ago
      https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L84 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-next-page 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-functions https://use-the-index-luke.com/sql/partial-results/window-fu... )
      • vishnumohandas 3y ago
        Hey yes, thank you for pointing it out! 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! [1]: https://news.ycombinator.com/item?id=39290784 https://news.ycombinator.com/item?id=39290784
  • chasil 3y ago
    WAL mode does introduce a number of important usage limitations. https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf 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 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.”
  • bawolff 3y ago
    Isn't WAL more about concurrent performance? I'd be surprised if its all that much faster when there is only a single thread doing anything. 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.
• deleted 3y ago
  [deleted]
• mholt 3y ago
  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.
  • vishnumohandas 3y ago
    Sorry, we should have linked to the DB Schema. Here you go: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L46 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...
    • maskros 3y ago
      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 https://www.sqlite.org/autoinc.html
    • mholt 3y ago
      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.
  • SPBS 3y ago
    Look here: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L84 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 https://use-the-index-luke.com/no-offset [2] 10K + 20K + 30K + 40K + 50K + 60K + 70K + 80K + 90K + 100K = 550K
• pkoird 3y ago
  I'd be curious to know the results you get if you forgo serialization entirely and save the embeddings on a table with 512 columns, one for each embeddings. Some rough calculation, assuming you store doubles, seem to show that the total db size should be less than 500 MB.
• cpursley 3y ago
  Ha, I guess this title is a play on my submission yesterday “Postgres Is Enough”: https://news.ycombinator.com/item?id=39273954 https://news.ycombinator.com/item?id=39273954
• evmar 3y ago
  I'm sure the database is nice for other reasons, but also without really understanding the problem it sounds like you could represent two separate things here: (1) metadata about photos and their metadata sync state, (2) embeddings. You could store a dense mapping to integers in (1) and then represent (2) as a separate file that is literally just packed floats. Photo number n has its embeddings start at file offset n * 512 * sizeof(float). Updating any given photo's embeddings is an easy seek+write. Given that the use case of similarity search is to stream through the embeddings and not any complex seeking/rewriting/indexing/etc, feels like a relatively simple bit of code. The post talks about serializing 100k photos. With 32-bit floats each embedding vector is 4*512=2048 bytes, so that's ~205mb of data, much less than the >500mb quoted in the post.
• electroly 3y ago
  Doesn't pass the sniff test. 19 seconds sounds like the performance of 100,000 separate transactions. I bet a crisp $10 bill they've forgotten to wrap all of this in a transaction. In SQLite it makes a huge difference. I struggle to think of a way that you could make SQLite take 19 seconds to write and read 100,000 rows otherwise.
  • vishnumohandas 3y ago
    Hey, the writes were batched[1]. What is expensive here is the serialization. [1]: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L65-L66 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d... Edit: Updated the permalink
    • electroly 3y ago
      This is the smoking gun, unfortunately; I was right. This code is not correct. You're doing a single insert per transaction (which is the same thing you'd get if you hadn't used an explicit transaction at all). What you want is a single transaction with ALL of the inserts in it. Do it again with a single transaction and you'll find it massively improves the performance. This is a common pitfall for beginners. Edit: You've changed the link after I wrote this reply. Did you fix the code? Is the blog post using the original code or this updated code?
      • vishnumohandas 3y ago
        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/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L65-L66 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...
• CharlesW 3y ago
  It seems like the author would've had better luck with https://pub.dev/packages/sqlite_async https://pub.dev/packages/sqlite_async, which appears to be made for Flutter users who don't necessarily know much about configuring SQLite.
  • vishnumohandas 3y ago
    Hey, we're already using sqlite_async, and yes it does a nice job of abstracting away the complexity introduced by isolates.
• thatgurjot 3y ago
  Thanks for sharing this! This comparison is quite helpful as I was facing write speed issues for my desktop app that uses SQLite (just text, not embeddings). For working with embeddings on desktop, would you recommend using a vector DB? Or can we get away with SQLite (or Isar)?
• scblock 3y ago
  ... in our one case. It probably works fine for you.
• munro 3y ago
  > 500ms is an acceptable latency for a search that runs offline without network calls. Definitely disagree, having fast search makes a huge difference. Apple Photo's search is fast. Especially since CLIP embeddings aren't perfect, you may need to try a few different keywords. > For 100,000 embeddings, writing took 6.2 seconds, reading 12.6 seconds and the disk space consumed dropped to 440 MB. Something sounds so wrong here, I read/write a 100-500 GiBs of data from Python, and it's much faster than this. In fact I'm seeing ~1.3s to write, and ~700ms to read from trivial Python: import pickle import sqlite3 import numpy as np IMAGES = 100_000 EMBEDDING_DIMENSIONS = 1024 ### write db = sqlite3.connect("images.db") db.execute("CREATE TABLE IF NOT EXISTS image_embeddings (id INTEGER PRIMARY KEY, embedding BLOB)") # generate synthetic embeddings embeddings = np.random.normal(size=(IMAGES, EMBEDDING_DIMENSIONS)) # 1.61 s # serialize embeddings for storage serialized_embeddings = [(pickle.dumps(embedding),) for embedding in embeddings] # 626 ms # insert into db db.executemany("INSERT INTO image_embeddings (embedding) VALUES (?)", serialized_embeddings) # 679 ms db.commit() # 95.3 ms db.close() #### read db = sqlite3.connect("images.db") serialized_embeddings = db.execute("SELECT embedding FROM image_embeddings").fetchall() # 370 ms embeddings = [pickle.loads(x[0]) for x in serialized_embeddings] # 322 ms
  • cogman10 3y ago
    For reads from the original code, part of the problem is they are doing limit/offset (rather than a fetch all). Even if they didn't want to read all, it'd be a lot faster to remove the offset and instead query based on the id. Limit/offset often result in really expensive query plans.
• your_challenger 3y ago
  So the problem is with dart library sqlite_async.dart [1] and not with sqlite itself. Kind of a misleading title, TBH. [1] https://github.com/powersync-ja/sqlite_async.dart https://github.com/powersync-ja/sqlite_async.dart Reference to an HN comment that clarifies this - https://news.ycombinator.com/item?id=39290411 https://news.ycombinator.com/item?id=39290411
• gwbas1c 3y ago
  > On the brighter side of things, the number of embeddings we've to query over is small. An average user has < 100,000 photos, and running a brute force search over 100,000 items on midrange mobile devices takes < 500ms. So your embeddings are an index? That's kind of the point of an index.
• gwbas1c 3y ago
  > What I thought would be a short fairytale, did not have a happy ending. Writing and reading 100,000 serialized embeddings took ~19 seconds. WTF? Are you running a separate transaction per embedding? Using a heavy weight ORM incorrectly? SQLite can handle inserts very, very fast; but if you put lots of layers on top of it, (like an ORM,) they usually slow things down. > It was clear that serialization was the culprit, so we switched to Protobuf instead of stringifying the embeddings. This resulted in considerable gains. Why are you serializing and then inserting into a SQL database? You should have columns for your fields, and directly query on those columns. Anyway, I think I understand why this article is (unfairly) flagged: You are criticizing SQLite, and clearly do not know how to program with a SQL database.