Hacker news

  • Top
  • New
  • Past
  • Ask
  • Show
  • Jobs

Distributed, high-performance blob storage on SQLite (https://zerodha.tech)

279 points by ghostfoxgod 1 day ago | 72 comments | View on ycombinator

anacrolix about 21 hours ago |

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.

sgarland about 18 hours ago |

> 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.

sneilan1 about 16 hours ago |

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?

lukax about 19 hours ago |

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.

bob1029 about 22 hours ago |

> 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.

gkamal about 19 hours ago |

This looks like a classic case of nih/reinventing wheel.

munch-o-man about 10 hours ago |

I wrote something similar with s3/blob storage but probably with slightly different use cases. Been working on it for like a year now:

https://github.com/afreidah/s3-orchestrator https://s3-orchestrator.munchbox.cc

self hosted service that acts as an orchestration layer between x-number of s3/blob storage backends, handles replication, failover, encryption, compression, integrity checking, and backend pressure analysis to optimize reads/writes to the most efficient available backend that meets the requirements of the request.

Threw a star on yours so I can remember to look at it deeper later

CyrusGrissom about 22 hours ago |

I couldn't find the word "replication" in the post or on the GitHub page. So, what about fault tolerance?

matja about 21 hours ago |

What is the consistency model? What happens if a node is down unexpectedly?

articulatepang about 18 hours ago |

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.

vmikhailov about 17 hours ago |

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

grep_it about 12 hours ago |

I find that partitioning by key range tends to be more flexible and malleable to rebalancing (merge, splits, etc). Doing things like CRC16(key) % 16384 tends to bite eventually.

feverzsj about 23 hours ago |

Sounds like you should use a database-per-user design.

Blobtits about 20 hours ago |

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.

martinjc about 21 hours ago |

Why not rqlite?

Kalanos about 21 hours ago |

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?

zodwick about 22 hours ago |

all roads lead to sqlite

adsharma about 9 hours ago |

Why not rivet?

Kalanos about 21 hours ago |

Is the db still a single, portable file?

murkt about 21 hours ago |

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.

robinduckett about 21 hours ago |

What about using, ahem, files?

ltbarcly3 about 11 hours ago |

I remember something in operating systems class about an efficient way to store binary objects. Let me go check the textbook..

Filesystem! I knew there was some obscure way to do this.

ratherlongname about 20 hours ago |

So many comments about things already addressed in the article sigh

nodesocket about 17 hours ago |

If only rethinkdb hadn’t nose dived. It supported sharding, health checks, clustering with failover. Write performance was quite good as I recall.

redwood about 18 hours ago |

To confirm this is being stored on devices rather than on a server side environment?

_s_a_m_ about 21 hours ago |

so it's just sharding?

EGreg about 17 hours ago |

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.

arbor-group about 18 hours ago |

[flagged]

patryk_builds about 20 hours ago |

[flagged]

foolgry about 22 hours ago |

[flagged]

aman179102 about 21 hours ago |

[flagged]

wangguode about 19 hours ago |

[flagged]

agline_in about 18 hours ago |

[flagged]

vrox about 23 hours ago |

[flagged]

dklabsdev about 16 hours ago |

[flagged]

zorvelaieffects about 14 hours ago |

[flagged]

routebraid about 16 hours ago |

[flagged]

mesm7903 about 23 hours ago |

[flagged]

MalgorzataKowal about 22 hours ago |

[flagged]