Hacker News Reader

Comment by mathgladiator | Hacker News Reader

I believe using a large JSON file is not half bad, but you do run into the problem of how do you index and query it in meaningful. I actually ran into this problem when building a game because a document doesn't provide a great model..

The relational model is... JUST... SO... GOOD. And, it is a shame that most of the relational systems are so complicated.

A document within Adama (https://www.adama-platform.com/) is basically a giant JSON file held within memory with clients connected via a WebSocket. I'm basically building my own indexing since I want the indexing to be reactive, and I've got exceptionally fun optimization problems.

Litestream, in my opinion, is a great way to get started. Actually, it's beyond fantastic if you maintain the 1 server to 1 database because migrations become so easy. So, I applaud the team for taking this simple approach.

View on HN

If you have a "single source of truth", sqlite/Litestream seems like a pretty-near-optimal way of taking advantage of the relational model while keeping design simplicity. I don't know what the higher-level architecture of Tailscale is, but we have the same problems; we have a complicated "single source of truth" that takes the form of a Consul cluster, but sqlite makes an absolute ton of sense for us, because we can condense the Consul cluster down to a SQL schema and then ship it around our fleet.

(we don't use Litestream right now; we're pushing the complexity up a layer in our design instead)

A lot of people on this thread are looking down their noses at sqlite, but I think they're kind of beclowning themselves; the unreasonable effectiveness of sqlite has been a meme in infra dev for a couple years now. It's not a new idea. Lots of people are doing stuff like this.

The funniest bit on this thread is the person saying they should use RDS, as if their infrastructure was just a big Rails app.

It would be interesting to known why a standard boring RDS setup wouldn’t solve their problem completely. In fact I would be more interested to understand that than the actual details of sqlite tailing.

(I think the reason they gave was vendor lock-in, but apart from that, I didn’t understand why it wouldn’t be adequate)

One obvious reason not to use RDS is wanting disk-local caches of information replicated from a single leader, rather than having every single machine in your fleet calling out to an external service on every read. That's certainly why we're not considering Postgres in our infrastructure, even though managed Postgres is a product we in fact offer.

We use Postgres! It's the backing state for our API, and an important source of truth in our architecture. Postgres is a great way of serving a GraphQL API. It is not necessarily a good way to back an infrastructure service.

Makes sense once you think of Tailscale as an infrastructure-level service and not just an app. Thanks

Sounds like postgres listen/notify could be viable for your high-read-low-write use case? Or it is not scalable enough for the fleet size?

That's still all your infrastructure components calling out to an external service on every read --- and for what advantage?

This isn't an app server; it's an infrastructure component, running (I don't know about Tailscale here, but we use SQLite in similar uses cases) on potentially hundreds or thousands of machines.

No, you don't need to make any network call on every read with listen/notify. It will essentially be a local cache, working the same way as etcd watcher, using one postgres connection per machine.

Really curious…how do you meaningfully overlay any indexes on top of a big-ass JSON file? Technical details are appreciated, and no problem if it’s your secret sauce—just very curious how this is accomplished!

The normal way? You can implement whatever kind of index you like — b-tree index, bitmap index, hash index are all useful and conceptually simple if you're familiar with the backing data structures.

For example, if you want to index a "foreign key" id stored in each "record" in a JSON array of objects, you build a hash table from the FK id values to the JSON array indices of the objects that have that id. It can be as stupid simple as an `fk_index = defaultdict(set)` somewhere in your program, to use a Pythonism.

Now when someone wants JSON objects in that array matching a given FK id, they can just O(1) look in the index to know the position of records that match. Much better than an O(N) scan of every item in the array.

Of course you have to to maintain the index as writes to the JSON happen, but that's not bad once you understand how things work. No real secret sauce.

The secret sauce may be the need to take control of the write path.

So, a funny thought experiment is what happens when you parse a JSON file at the same time? You also index by primary key (the field name).

So, I mirror this thinking and having be just an object with the keys being the primary key. Then, I simply index all the children by their fields based on insights from the developer via the index keyword.

So, if you have

record R { public int id; client int owner; int age; index age; } table<R> rows;

then queries for age can be accelerated by the table.

like "iterate rows where age==42" will basically hone in on the bucket of age==42. I currently only index clients by hash and integers.

The critical aspect which makes this work is that I monitor all mutations. When a child object has a field mutated, then it is removed from all indices and placed into an unknown index. Any queries will also consider it as the purpose of queries to simply narrow the field. Once data changes are persisted, the index is updated and items are moved out of the unknown bucket. This works fairly well because the indices are primarily used during the privacy check phase.

Reply on news.ycombinator.com