I'm really excited about being able to do relational queries. I like the idea of ssb-query-relations or similar as a plugin.

Also relevant, I've been watching https://github.com/mafintosh/turbo-json-parse - @mafintosh recently added support for JSON-schema. I've not tried anything but been wondering if this thing could be super relevant.

Hah yeah @mix I have been watching that as well. We established that json parsing and stringifying is a big part of the performance problems we are seeing. I tested turbo-json and the 5x speedup is real. Also he optimizes for bytes that will fit perfectly with us. There are two problems right now with using it for messages: 1) We need anyOf otherwise our null or string for backlinks won't work and 2) we probably need something like an old school json.parse in the middle for the content part.

Yeah postgresql has similar functionality that we have used in some projects, its really great for when schemas are overkill.

I think I will try this as well for #go-ssb - the abstractions we already should enable this fairy easily anyways.

THIS IS EXCITING

• What social dynamics / psychology leads to the situation where none of us thought to benchmark against something like sql databases? Is it because it's new shiny tech and we tend to think new is inherently better? Is it because it's Not Invented Here, custom built?

I'll can give my two cents here. I have and still do work a lot with databases, so I have a fairly good idea about how fast databases are. That was also why I spent a lot of time on figuring out why scuttlebot and flume works the way it does performance wise. There was a certain element of, I want to understand how all of this works.

Just as there are things that is easier in sql, there are some things that are easier in flume, pulling on live data is one. The other one is getting the database to run in a browser, but maybe sqlite is catching up, now that wasm has become a thing.

There is a question of mindshare. A lot of people are familiar with sql and flume is still very much a work in progress. So the question becomes, should we be more careful about what battles we pick. Also if a part of the community started to work and base their stack on sql instead of flume, that would dilute the mind share.

Thanks for bringing this up.

as hinted in %8gOQchm... I recently toyed with this as well using #go-ssb

At first I got in over my head, trying to store keys as bytea (so removing the base64 overhead) but this turned into a doozy when querying so I punted on it. Might be worthwile to reconsider at some point. Right now I just use postgres since I'm more use to it but if sqlite has decent json facilities as well I'd be game for that too if some one feels more inclined to colaborate on schemas and queries.

My main aim is rebooting ssb-pubmon and also having it for verifying your feed is updating to the mainet or forked for instance. It could might as well turn into an alternative viewer.scuttlebot.io implementation at this point.

#ssb-implementations #sql

@skyebend if you "just" want to dump stuff into sqlite, have you considered using #ssb-patchql? You can let it ingest data in batches with the right mutation input, and the result is an sqlite file. If you don't care about accessing it via patchql, you can also just open it read-only, that should be totally possible.

Hey, but is database a solution? It adds a piece of code whose purpose is to write structured or "object" data in a way it is really quick to read from. This situation was involved because of slow mecanical "hard drives".

With SSD and PCIe port this slowliness problem is not a dead end.
I went back to file system directories and files trees, manupulated with GNU CORE BASH tools (cat, grep, awk, cut, ...) any kind of search is quick !! With that filesystem shared over IPFS in a SSB controled swarm, it is possible to exchange (clear, sign or crypt) this "DB" as a distributed data swarm. #astroport is based on that principle.

This approach gives with "ipfs p2p" port forwarding a kind of service teleportation over the swarm of my friends.
Then we could use extra services provided by https://chatons.org/en members