29 comments

  • sgarland34 minutes ago
    &gt; What we learned along the way… Deleting rows doesn’t shrink SQLite files<p>This is true of every RDBMS I am aware of. It would be horribly inefficient to constantly run page merges in order to be able to free some of them.<p>Whenever I read things on posts that are a. discussed in documentation b. should be apparent from first principles, I immediately lose faith in the product.
  • anacrolix3 hours ago
    I spent years trying to squeeze this use case out of sqlite. All roads lead to tears, it is <i>not</i> made for this use case.<p>I wrote <a href="https:&#x2F;&#x2F;github.com&#x2F;anacrolix&#x2F;possum" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;anacrolix&#x2F;possum</a> 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&#x2F;TTL metadata which is more suited to sqlite but it&#x27;s <i>still</i> the bottleneck.<p>sqlite transaction commit overhead is too high and uncompetitive without batching but it&#x27;s a design flaw specific to being multiprocess.
    • adev_39 minutes ago
      &gt; Incidentally it uses sqlite for transaction&#x2F;TTL metadata which is more suited to sqlite but it&#x27;s still the bottleneck.<p>Why sqlite instead of RocksDB ?<p>What you need at the end of the day for a blob storage is a transactional key value store.<p>Which RocksDB will give you with much better throughput (and latency) and solves your transaction problem.
    • mightyham2 hours 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&#x27;ve seen online, sqlite doesn&#x27;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.
      • luke54411 hour 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!
        • stingraycharles1 hour ago
          The usual answer here is a WAL + batching multiple transactions.
  • articulatepang42 minutes ago
    I wonder if they tried storing diffs instead, and compacting them once in a while.<p>These are user preferences for UI setup, and they have a lot of write traffic. But probably each user changes their preferences only by a little bit each time. Their 1MB writes would go down to just the small diffs.<p>They could store the diffs and transparently apply them before sending to the client. After some timeout or maybe after a certain number of diffs have accumulated, squash them all and write back.<p>They’d still need a server and custom code to handle this but it would probably reduce the database load and SSD writes by a lot. And let them just use something off-the-shelf for storage.
  • lukax1 hour 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.
  • gkamal2 hours ago
    This looks like a classic case of nih&#x2F;reinventing wheel.
    • cuttothechase1 hour ago
      explain?<p>sqlite offers this and this is being redone or what is being reinvented here
  • bob10295 hours ago
    &gt; JSON blobs<p>An alternative to faster blob storage is to represent the blob&#x27;s schema in SQL directly.<p>If most blob updates are incremental you can eliminate a significant amount of storage IO and garbage collection this way.<p>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.
    • testtestmetest4 hours ago
      You want an LLM to be modifying your db schemas on the fly?? Seems slow and error prone.
  • matja4 hours ago
    What is the consistency model? What happens if a node is down unexpectedly?
  • CyrusGrissom5 hours ago
    I couldn&#x27;t find the word &quot;replication&quot; in the post or on the GitHub page. So, what about fault tolerance?
    • tomasol5 hours ago
      I&#x27;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.
  • nodesocket15 minutes ago
    If only rethinkdb hadn’t nose dived. It supported sharding, health checks, clustering with failover. Write performance was quite good as I recall.
    • nodesocket10 minutes ago
      Update: I guess it’s still up, though last release was 3 years ago.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;rethinkdb&#x2F;rethinkdb" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;rethinkdb&#x2F;rethinkdb</a>
  • feverzsj6 hours ago
    Sounds like you should use a database-per-user design.
    • dolmen1 hour ago
      As the post mentions &quot;trading&quot; and &quot;50% writes&quot;, I guess that most writes are derived from live market data, and probably not user-specific but shared.
    • mkesper5 hours ago
      Right, then even sqlite should be enough?
      • lmz1 hour ago
        But now you have a file per user, which you then need to distribute somehow...
  • Blobtits2 hours 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.
  • Kalanos3 hours 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?
    • dolmen1 hour ago
      What about reading the article?<p>The network interface is the Redis protocol.
      • withinboredom37 minutes ago
        Then why use this at all? Why not something like SwytchDB?
  • martinjc3 hours ago
    Why not rqlite?
    • eatonphil1 hour ago
      &gt; Horizontally scalable, distributed.<p>This doesn&#x27;t describe rqlite, at least.
      • otoolep36 minutes ago
        (Hi Phil!)<p>Correct, I read the post. Agreed, rqlite is not a great fit for high-throughput durable writes since all writes go through the Raft Leader. This is not specific to rqlite -- all systems which put Raft in the data plane[1] have the same challenge.<p>rqlite does offer a Queued Writes mode[2], which allows you to trade some durability for much greater write throughput. The post mentions something similar -- async_write -- but their post claims they also make the data queryable before it is durable,unlike rqlite.<p>[1] <a href="https:&#x2F;&#x2F;youtu.be&#x2F;8XbxQ1Epi5w?t=2306" rel="nofollow">https:&#x2F;&#x2F;youtu.be&#x2F;8XbxQ1Epi5w?t=2306</a><p>[2] <a href="https:&#x2F;&#x2F;rqlite.io&#x2F;docs&#x2F;api&#x2F;queued-writes&#x2F;" rel="nofollow">https:&#x2F;&#x2F;rqlite.io&#x2F;docs&#x2F;api&#x2F;queued-writes&#x2F;</a>
    • otoolep3 hours ago
      rqlite[1] author here, happy to answer any questions.<p>[1] <a href="https:&#x2F;&#x2F;rqlite.io" rel="nofollow">https:&#x2F;&#x2F;rqlite.io</a>
  • redwood45 minutes ago
    To confirm this is being stored on devices rather than on a server side environment?
  • ratherlongname2 hours ago
    So many comments about things already addressed in the article <i>sigh</i>
  • robinduckett4 hours ago
    What about using, ahem, files?
    • ncruces3 hours ago
      Filesystems can be much slower than SQLite for many (think millions of) small files.<p>They also mention considering this in the post.
      • robinduckett3 hours ago
        I guess my squishy biological tokeniser and summariser missed it (i.e I read the TL;DR and skipped the post)
  • zodwick5 hours ago
    all roads lead to sqlite
  • murkt3 hours ago
    Sounds like it solves their particular problem, but hardly can be used by outsiders in the current state.<p>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.
  • Kalanos3 hours ago
    Is the db still a single, portable file?
  • _s_a_m_4 hours ago
    so it&#x27;s just sharding?
  • arbor-group47 minutes ago
    [flagged]
  • agline_in1 hour ago
    [flagged]
  • patryk_builds2 hours ago
    [flagged]
  • wangguode1 hour ago
    [flagged]
  • aman1791023 hours ago
    [flagged]
  • foolgry4 hours ago
    [flagged]
  • vrox6 hours ago
    [flagged]
  • mesm79035 hours ago
    [flagged]
  • MalgorzataKowal4 hours ago
    [flagged]