Distributed, high-performance blob storage on SQLite

(zerodha.tech)

141 points | by ghostfoxgod 6 hours ago ago

29 comments

  • anacrolix 3 hours ago ago

    I spent years trying to squeeze this use case out of sqlite. All roads lead to tears, it is not made for this use case.

    I wrote https://github.com/anacrolix/possum for this exact use case. It has a few rough edges but it absolutely hauls ass for caching on disk. Incidentally it uses sqlite for transaction/TTL metadata which is more suited to sqlite but it's still the bottleneck.

    sqlite transaction commit overhead is too high and uncompetitive without batching but it's a design flaw specific to being multiprocess.

    • mightyham 2 hours ago ago

      At the end of the day durable transaction commit in any OLTP database will always be slow because fsync is an expensive operation (~1000 IOPS on a decent SSD), so as you mentioned batching is the only way to make significant increases to throughput. That said, based on assorted benchmarks I've seen online, sqlite doesn't even get close to max fsync IOPS because it has a ton of overhead. Other embedded systems like rocksdb or lmdb are going to give better performance.

      • luke5441 29 minutes ago ago

        Or you can do like GP with that possum and not fsync at all. It even sets synchronous=OFF, which will corrupt SQLite databases. Then it is fast because it only writes to memory. YOLO!

        • stingraycharles 17 minutes ago ago

          The usual answer here is a WAL + batching multiple transactions.

  • lukax an hour ago ago

    OpenStack Swift uses SQLite for storing metadata. Each account has one SQLite db. Each container also initially had one SQLite db but in 2018 they added support for sharding so if a container has more than 1 million objects, the container is split into multiple SQLite databases. The performance is not ideal. Ceph scales much better.

  • gkamal an hour ago ago

    This looks like a classic case of nih/reinventing wheel.

    • cuttothechase 3 minutes ago ago

      explain?

      sqlite offers this and this is being redone or what is being reinvented here

  • bob1029 4 hours ago ago

    > JSON blobs

    An alternative to faster blob storage is to represent the blob's schema in SQL directly.

    If most blob updates are incremental you can eliminate a significant amount of storage IO and garbage collection this way.

    One SQLite database per blob type with a metadata SQLite db that tracks each. We could leverage powerful AI reasoning models to produce SQLite schemas and virtual table adapters for each encountered JSON schema type.

    • testtestmetest 3 hours ago ago

      You want an LLM to be modifying your db schemas on the fly?? Seems slow and error prone.

  • matja 3 hours ago ago

    What is the consistency model? What happens if a node is down unexpectedly?

  • CyrusGrissom 4 hours ago ago

    I couldn't find the word "replication" in the post or on the GitHub page. So, what about fault tolerance?

    • tomasol 4 hours ago ago

      I'm curious too. The only backup I found on GitHub uses cp; they may use filesystem snapshots or Litestream in production. If it’s Litestream, litestream sync -wait can force a sync after a write and wait for remote replication to complete.

  • Blobtits 2 hours ago ago

    Reads like this is be a perfect fit for ClickHouse. You should do async inserts, remove the 20 or so _log options ClickHouse enables for some insane defaults and batch them in Redis if needed (probablynot). Then you have automatic compression, free ttl, no write concurrency.

  • feverzsj 5 hours ago ago

    Sounds like you should use a database-per-user design.

    • dolmen 4 minutes ago ago

      As the post mentions "trading" and "50% writes", I guess that most writes are derived from live market data, and probably not user-specific but shared.

    • mkesper 5 hours ago ago

      Right, then even sqlite should be enough?

      • lmz 16 minutes ago ago

        But now you have a file per user, which you then need to distribute somehow...

  • Kalanos 3 hours ago ago

    A limitation that i ran into with sqlite was not being able to interact with it over the network e.g. NFS. Does this solve that problem?

  • martinjc 3 hours ago ago

    Why not rqlite?

    • eatonphil 3 minutes ago ago

      > Horizontally scalable, distributed.

      This doesn't describe rqlite, at least.

    • otoolep 3 hours ago ago

      rqlite[1] author here, happy to answer any questions.

      [1] https://rqlite.io

  • ratherlongname 2 hours ago ago

    So many comments about things already addressed in the article sigh

  • murkt 3 hours ago ago

    Sounds like it solves their particular problem, but hardly can be used by outsiders in the current state.

    Bad fault tolerance, no words on replication or backup. Offline rebalance... meh. I am not sure how I feel about their choice of Redis protocol.

  • robinduckett 3 hours ago ago

    What about using, ahem, files?

    • ncruces 3 hours ago ago

      Filesystems can be much slower than SQLite for many (think millions of) small files.

      They also mention considering this in the post.

      • robinduckett 2 hours ago ago

        I guess my squishy biological tokeniser and summariser missed it (i.e I read the TL;DR and skipped the post)

  • zodwick 4 hours ago ago

    all roads lead to sqlite

  • Kalanos 3 hours ago ago

    Is the db still a single, portable file?

  • _s_a_m_ 3 hours ago ago

    so it's just sharding?