Comment by stpn | Hacker News Reader

Comment by stpn | Hacker News Reader

We run sqlite at https://tender.run, both in our backend (as a primary data store, backed by litefs) and on the client (as a index and cache).

In general works really great, esp. in terms of speed. There are some limitations worth knowing though - for instance, figuring out making write throughput reasonable with the combination of wal mode, busy timeout, and begin immediate.

Another that I've had to work around recently is the lack of materialized views - you can make and query from a view, but it's just a select statement that gets shoved into queries that use it. To get the perf of a materialized view, you'd have to do something manual and custom. For the most part, this isn't an issue simply due to how fast sqlite is, but in the browser it's a bit more significant because of how much slower the wasm-compiled sqlite is.

Cool, I was thinking of making somehting like tender but for businesses. I always have an issue with expenses being everywhere and nowhere (to be found).

Have you written about your experiences? Especially the write-throughput?

There’s an overview for how our app is built here: https://tender.run/blog/tender-and-crdts

We haven’t written much about our server-side sqlite. I’d say our write workloads are easily within what sqlite can do for now. At one point we looked into doing per-customer databases for scaling write perf (since sharding by customer can be a lot cheaper in sqlite than other dbs), but it’s not necessary yet.

Considering the access patterns of the clients are well known, would it make sense to populate new tables as materialized views before Sending it to the client or you think is not worth it perf wise?

We use the client-side sqlite as part of making the app work locally and offline, so it wouldn’t make sense.

The materialized view isn’t a huge perf problem for now, and I expect we’d switch to using triggers or similar without too much difficulty.

Reply on news.ycombinator.com