Clojurians Log v2

Thanks! always glad when people like my posts 🙂 and yeah, pretty much just performance. There might be some other benefits to sqlite, e.g. probably less memory consumption. But any other non-performance benefits of sqlite are definitely dwarfed by the downsides (not a distributed database, not immutable, super limited data types). There are probably plenty of situations that biff targets which would be fine with xtdb, but in general xtdb does feel more like a "specialized" db IMO where you'll want to make a conscious decision to use it and make sure its performance characteristics match what you need. Whereas sqlite feels like a safer default choice e.g. for situations where you're like "idk I'm just making a boring web app, tell me what db to use"

Excited for all the new growth! If performance is prime concern, why not use Datalevin? It is pitched as a SQLite alternative (embeddable, schema optional, operationally simple, fast), but more versatile (rule engine, vector db, document store, kv-store, blob storage, better api?: graph query, datalog..) from readme.. > Datalevin is about 2.4X faster than PostgreSQL and 4X faster than SQLite on average in running these complex queries that involves many joins. The gain is mainly due to shorter query execution time as Datalevin’s query optimizer generates better plans. Details of the analysis can be found in https://yyhh.org/blog/2024/09/competing-for-the-job-with-a-triplestore/ > > When transacting one entity (equivalently, one row in SQLite) at a time, Datalevin’s default transaction function is over 5X faster than SQLite’s default; while Datlevin’s asynchronous transaction mode is over 20X faster than SQLite’s WAL mode.

also +1 for Datastar. I’ve drank the koolaid!

Interesting--maybe I'll have to think more about datalevin before the official release 😉 an immediate thought is that litestream+sqlite is a pretty big deal. with datalevin my understanding is you wouldn't be able to get granular backups/point-in-time restore like that; you'd be limited to doing more of a standard copy-the-whole-database-periodically thing. I'd also be curious to benchmark datalevin on Yakread's data + queries. The fact that the author's benchmark has sqlite running almost twice as slow as postgres makes me think that benchmark probably isn't similar to the kinds of queries I do. My benchmarks had sqlite being 5x faster than postgres.

Super exciting stuff. So de prioritizing xtdb is mostly for performance reasons?

Apart from that: You are such a great writer, I just had a blast reading this. And it's truly amazing how you manage to maintain that kind of output. I don't even have kids, and most of my blog posts and software projects are already collecting dust while waiting to be published:sweat_smile:

@crimsonhawk47 all good points. My prioritization framework is basically (1) what do I want for my own projects, (2) what do I think would be useful for hobbyists and solo entrepreneurs, (3) what do I think would be useful for larger projects. I am going for practicality in all cases. sqlite in this sense fits pretty well since it's good for (1) and (2). The design choice that's felt the most difficult is whether to include pathom or something like it (i.e. biff.graph) since that hits (1) and (3) for sure but (2) is a lot more iffy. I'm very curious to see what people's experience with biff.graph is. Basically I think it's worth trying to make it beginner friendly, and hopefully I don't have to back track on its inclusion 😉 . If needed I could always take it out of the default template and then have an article "Recommendations for larger projects" or something. I so for have never used XTDB's bitemporality features. "mono temporality" is more broadly useful IMO, e.g. I've used it at least once to recover from a borked transaction, and I've used it a number of times to look at the history of a particular entity (e.g. "what was this user doing" kind of thing). Doesn't come up super often but it's nice to know the capability is there. Even without the ability to do ad-hoc historical queries, litestream is also pretty nifty just as a backup solution. In the biff.sqlite library, you just have to provide a few configuration values (s3 credentials) and then it'll run litestream on startup. @huahaiy that's super interesting. I'm outing myself as not having read the README closely since I didn't realize datalevin had a HA mode! I'll try datalevin out sometime before doing the official biff 2.0 release. Having HA + streaming backups would be a pretty big improvement over sqlite even without taking performance into account. I've got a https://github.com/jacobobryant/benchmark with all the queries from my main application that I used last year to compare XTDB v1/v2, sqlite and postgres query performance (the input data isn't published though since I'd need to more rigorously de-anonymize it). I'll try adding datalevin to that too.

I think sqlite is the right move. I can only say for my personal experience, getting into XTDB was really hard to learn on top of learning Clojure and HTMX, and the payoff is questionable if you're making an app that has no use for bitemporality. Though I did find the model validation stuff very helpful. But it really depends what your goals for Biff are. Is it a beginner friendly framework, or is it a demonstration of a purely functional ethos? I think it's really hard to do both of those things, and I imagine you go back and forward on which one to do. A functional language is one thing, but when we get into Pathom and NoSQL on top of learning Clojure, we get away from beginner friendly, even if it might technically help someone like you write more correct/safe code. But I also understand being drawn to that tech, and trying to put it behind a simpler API. For instance, you want to target smaller projects. I'm not sure most people working on small projects need to arbitrarily query the history of their database at any point in time. That sounds like the type of audit capability a bank would need, not a small side project. I imagine sqlite + litestream would be enough. I'm, personally, not sold on the value add of litestream for my small projects. But I will be able to use sqlite fine. I also have heard Clojure members reference performance issues with XTDB in the past, but I barely understand how regular SQL engines work, I just write the queries. So I have no concept of whether XTDB critics were spreading FUD or not.

If litestream+sqlite is what you need, Datalevin does not have litestream right now. However, it should be easy to build one by streaming the WAL out, which we have API for. That's what our high availability cluster mode uses. As to Sqlite, I have a blog post about this exact topic: https://yyhh.org/blog/2026/01/sqlite-in-production-not-so-fast-for-complex-queries/ The conclusion: "SQLite in production" advocates often benchmark simple cases: key-value lookups, single-table scans, basic CRUD operations. On those workloads, SQLite does extremely well. But production systems grow. Schemas become more normalized as data integrity requirements increase. Questions become more compositional as business logic matures. And at that point, the query optimizer becomes the bottleneck.... Before choosing SQLite for a production system, ask: will our queries stay simple forever? If the answer is no, and it usually is, the savings in deployment simplicity may not be worth the cost in query performance as the system grows."

As for biff integration with Datalevin, I have a biff-datalevin library: https://github.com/datalevin/biff-datalevin I may announce it sometime when I got the time 🙂