upvote
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?

reply
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.
reply
> the actual data in a key-val object store

So, on a filesystem?

reply
Filesystems typically support mutable values, while it's beneficial in many ways to support only immutable values in distributed systems. A filesystem is a fine stand-in for object storage, though, at low scale/volume.
reply
deleted
reply
> 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.

reply
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.
reply
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!
reply
The usual answer here is a WAL + batching multiple transactions.
reply