4 ms·

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

by stpn 3y ago

We run sqlite at https://tender.run 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.

martinbaun 3y ago
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?
- stpn 3y ago
  There’s an overview for how our app is built here: https://tender.run/blog/tender-and-crdts 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.
kandros 3y ago
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?
- stpn 3y ago
  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.

by stpn 3y ago

martinbaun 3y ago

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?

stpn 3y ago
There’s an overview for how our app is built here: https://tender.run/blog/tender-and-crdts 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.

kandros 3y ago

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?

stpn 3y ago
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.