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.
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.
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!
Yeah, I'm not sure how well this will work. I've always dealt with data blobs with data on the filesystem (or s3), metadata in the database. Anything else starts to get a bit too much like a filesystem in a database, which has lead many smart people to tears.
Metadata in a DB or index of some kind and the actual data in a key-val object store is the most versatile configuration I have used. This lets you use as many different metadata indexes as you need.
> What we learned along the way… Deleting rows doesn’t shrink SQLite files
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.
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.
I once interviewed with a NYC robotics startup and, reflecting on his past job, he said they migrated away from SQLite3 because they needed a WAL database for their smart grocery cart.
Especially if you started in an age of remote cloud databases. The further we get away from the infrastructure we use, the less we know about nuances like this. What used to be considered common knowledge gets lost when you don't have experience with running a DB server.
I mean, today, why would you? It used to be that starting a new CRUD project meant at least an hour of setting up web servers, database servers, proxies, etc. Today, that's all abstracted away for pennies an hour.
Is there really no way to use Cassandra for this use case? A distributed and relatively battle tested database? They didn't want to use SSDB because it lacked support. Okay.
I mean you're one-off vibe-coded distributed database isn't a database until aphyer puts it through it's paces, and until it encounters most CAP its edge cases over years of use.
Otherwise, this isn't a database. It's a black hole of uncertainty that will almost certainly drop writes and commits silently
The problem with that attitude is that you're actively encouraging people to not talk about things they've just learned that might be obvious, for fear of being judged negatively by people like you. That harms the discourse.
Earlier in the blog post, Sarat writes about how many databases can't store multi-megabyte blobs. That makes sense to me but later on, he writes
> While raw files start to beat SQLite above ~100 KiB, we compress blobs before writing them. And thankfully, 99% of our compressed values sit well below 100 KiB.
My question is, were the other databases not tried with compression on the application side?
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.
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.
My short research gives me that ScyllaDB could be tuned for this use case (e.g. disabling durable writes for the blobs). And even if not I'd look into basing the work on something like ScyllaDB instead of developing a completely new database, especially since they already run ScyllaDB and have experience running it.
90% of it. Sharding, multi node, write buffering, redis protocol. The whole distributed and high performance part of it.
Problem is that it is not really distributed (static sharding, no fault tolerance, no consistency model).. on top of that poor operational support - backup/restore, recovery. Stateful infra pieces need lot more rigour, you can't iterate your way through such fundamental capabilities once it is in production use.
I wonder if they tried storing diffs instead, and compacting them once in a while.
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.
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.
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.
A part of me wonders a similar question that they started with a blob store and assumed a blob store and might have got good results normalizing their JSON schema into an Sqlite table structure, if they were going down the road towards Sqlite anyway. Especially, because if these preferences are truly per-user you could even ship the entire Sqlite database to the user and read it with WASM and not worry about concurrent writes and use much simpler object/file storage for the backup copies of Sqlite databases at that point. (Or get fancy with it and WAL log, sure, but really, if it is user owned at that point, just stick with simple dumb backups.)
Since you store blobs (json), you might benefit from having a graph on top of it at some point. I had a similar task in the past so I created a cypher-to-sql transpiler. Feel free to use it - https://github.com/vmikhailov/cypher-sql-go
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.
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.
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.
An honor to see you here. I used rqlite for a project i want to be available globally, in a small package i can myself manage, without having to bother with clustering postgres. I appreciate the ease of implementation in code, and how easy it is to manage operationally.
Great to hear, ease of operations is one of the main design goals. I've too many scars from running complicated systems. Of course that means that the rqlite feature set has remained focused (a nice way of saying "limited"), but in exchange, if it matches your use case, it can work well.
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.
rqlite does offer a Queued Writes mode[2], which allows you to trade 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.
One detail: if you batch up your writes in the client, and send that batch through a Raft-based data plane system, you can get high throughput. But then you are paying with latency, as the clients have to wait until they have a batch worth of data before making the write request to the Raft-based system. rqlite calls this approach Bulk Requests[1].
If you liked that, you might like this… it’s distributed, high performance, but also encrypted and has micropayments for storage, and anyone can contribute storage by just opening a browser tab:
Oh, and it is also compatible with S3 bucket protocol, IPFS, and even MySQL protocol so it can replace your S3 bucket or database with a distributed database — just like that!
PS: if you’re curious, it uses a columnar store and SQLite’s query planner, to be as efficient as possible.
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.
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.
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!
The usual answer here is a WAL + batching multiple transactions.
> Incidentally it uses sqlite for transaction/TTL metadata which is more suited to sqlite but it's still the bottleneck.
Why sqlite instead of RocksDB ?
What you need at the end of the day for a blob storage is a transactional key value store.
Which RocksDB will give you with much better throughput (and latency) and solves your transaction problem.
Yeah, I'm not sure how well this will work. I've always dealt with data blobs with data on the filesystem (or s3), metadata in the database. Anything else starts to get a bit too much like a filesystem in a database, which has lead many smart people to tears.
Or is there a better way?
Metadata in a DB or index of some kind and the actual data in a key-val object store is the most versatile configuration I have used. This lets you use as many different metadata indexes as you need.
> the actual data in a key-val object store
So, on a filesystem?
You can use one if you don't need the scale of a true object storage system.
> What we learned along the way… Deleting rows doesn’t shrink SQLite files
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.
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.
I once interviewed with a NYC robotics startup and, reflecting on his past job, he said they migrated away from SQLite3 because they needed a WAL database for their smart grocery cart.
It was hard to take him seriously after that
Everybody learns. Coming from other databases it might not be obvious that you need to enable vacuum explicitly.
Especially if you started in an age of remote cloud databases. The further we get away from the infrastructure we use, the less we know about nuances like this. What used to be considered common knowledge gets lost when you don't have experience with running a DB server.
I mean, today, why would you? It used to be that starting a new CRUD project meant at least an hour of setting up web servers, database servers, proxies, etc. Today, that's all abstracted away for pennies an hour.
Is there really no way to use Cassandra for this use case? A distributed and relatively battle tested database? They didn't want to use SSDB because it lacked support. Okay.
I mean you're one-off vibe-coded distributed database isn't a database until aphyer puts it through it's paces, and until it encounters most CAP its edge cases over years of use.
Otherwise, this isn't a database. It's a black hole of uncertainty that will almost certainly drop writes and commits silently
The problem with that attitude is that you're actively encouraging people to not talk about things they've just learned that might be obvious, for fear of being judged negatively by people like you. That harms the discourse.
Earlier in the blog post, Sarat writes about how many databases can't store multi-megabyte blobs. That makes sense to me but later on, he writes
> While raw files start to beat SQLite above ~100 KiB, we compress blobs before writing them. And thankfully, 99% of our compressed values sit well below 100 KiB.
My question is, were the other databases not tried with compression on the application side?
> 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.
You want an LLM to be modifying your db schemas on the fly?? Seems slow and error prone.
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.
This looks like a classic case of nih/reinventing wheel.
explain?
sqlite offers this and this is being redone or what is being reinvented here
My short research gives me that ScyllaDB could be tuned for this use case (e.g. disabling durable writes for the blobs). And even if not I'd look into basing the work on something like ScyllaDB instead of developing a completely new database, especially since they already run ScyllaDB and have experience running it.
90% of it. Sharding, multi node, write buffering, redis protocol. The whole distributed and high performance part of it.
Problem is that it is not really distributed (static sharding, no fault tolerance, no consistency model).. on top of that poor operational support - backup/restore, recovery. Stateful infra pieces need lot more rigour, you can't iterate your way through such fundamental capabilities once it is in production use.
I wonder if they tried storing diffs instead, and compacting them once in a while.
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.
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.
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.
A part of me wonders a similar question that they started with a blob store and assumed a blob store and might have got good results normalizing their JSON schema into an Sqlite table structure, if they were going down the road towards Sqlite anyway. Especially, because if these preferences are truly per-user you could even ship the entire Sqlite database to the user and read it with WASM and not worry about concurrent writes and use much simpler object/file storage for the backup copies of Sqlite databases at that point. (Or get fancy with it and WAL log, sure, but really, if it is user owned at that point, just stick with simple dumb backups.)
Since you store blobs (json), you might benefit from having a graph on top of it at some point. I had a similar task in the past so I created a cypher-to-sql transpiler. Feel free to use it - https://github.com/vmikhailov/cypher-sql-go
What is the consistency model? What happens if a node is down unexpectedly?
I couldn't find the word "replication" in the post or on the GitHub page. So, what about fault tolerance?
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.
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.
Sounds like you should use a database-per-user design.
Right, then even sqlite should be enough?
But now you have a file per user, which you then need to distribute somehow...
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.
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?
What about reading the article?
The network interface is the Redis protocol.
Then why use this at all? Why not something like SwytchDB?
Why not rqlite?
rqlite[1] author here, happy to answer any questions.
[1] https://rqlite.io
An honor to see you here. I used rqlite for a project i want to be available globally, in a small package i can myself manage, without having to bother with clustering postgres. I appreciate the ease of implementation in code, and how easy it is to manage operationally.
Great to hear, ease of operations is one of the main design goals. I've too many scars from running complicated systems. Of course that means that the rqlite feature set has remained focused (a nice way of saying "limited"), but in exchange, if it matches your use case, it can work well.
> Horizontally scalable, distributed.
This doesn't describe rqlite, at least.
(Hi Phil!)
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.
rqlite does offer a Queued Writes mode[2], which allows you to trade 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.
[1] https://youtu.be/8XbxQ1Epi5w?t=2306
[2] https://rqlite.io/docs/api/queued-writes/
One detail: if you batch up your writes in the client, and send that batch through a Raft-based data plane system, you can get high throughput. But then you are paying with latency, as the clients have to wait until they have a batch worth of data before making the write request to the Raft-based system. rqlite calls this approach Bulk Requests[1].
[1] https://rqlite.io/docs/api/bulk-api/
all roads lead to sqlite
If you liked that, you might like this… it’s distributed, high performance, but also encrypted and has micropayments for storage, and anyone can contribute storage by just opening a browser tab:
https://github.com/Safebots/Safecloud
It can also stream videos to any domain, like a decentralized YouTube. Here is a demo:
https://youtu.be/glWQM0BIcDQ?si=VV7YFiElGKBC82Bv
This is what we encourage to store in it:
https://safebots.ai/datasets.html
Oh, and it is also compatible with S3 bucket protocol, IPFS, and even MySQL protocol so it can replace your S3 bucket or database with a distributed database — just like that!
PS: if you’re curious, it uses a columnar store and SQLite’s query planner, to be as efficient as possible.
Is the db still a single, portable file?
What about using, ahem, files?
Filesystems can be much slower than SQLite for many (think millions of) small files.
They also mention considering this in the post.
I guess my squishy biological tokeniser and summariser missed it (i.e I read the TL;DR and skipped the post)
If only rethinkdb hadn’t nose dived. It supported sharding, health checks, clustering with failover. Write performance was quite good as I recall.
Update: I guess it’s still up, though last release was 3 years ago.
https://github.com/rethinkdb/rethinkdb
To confirm this is being stored on devices rather than on a server side environment?
No, this is on the server.
Then why not use S3 or an S3 equivalent open source? Or even gridfs
So many comments about things already addressed in the article sigh
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.
so it's just sharding?