Forgive my technical ignorance here: Could SQLite be used as a "lightweight" ActivityPub datastore, for a hypothetical ~single-user instance? Leaving current implementations (Mastodon, etc.) aside, I'm curious if/how this could work? /cc @simon
@case @simon I don’t see why not. SQLite should have the performance to run a single user instance if not a few more. Not entirely sure what the concurrent connections on SQLite are? something like https://fly.io/docs/litefs/getting-started
@kylev @case @anildash SQLite happily handles hundreds (even thousands) of writes a second, so I imagine for almost all Mastodon use cases it would be just fine - maybe not for a single instance with thousands of active users, at least not without scaling up the server to one with a lot more RAM and CPU
@simon @kylev @case @anildash my 30-person instance processes about 200k messages/day via the redis queue. That's less than three/second. Watching sidekiq, it sometimes bursts to high double digits.
I have no doubt that sqlite would be able to handle several hundred users on modest hardware (I have it on a 4GB VM that's backed by NVMe storage; it's 20 bucks/month). I suspect it might be even more performant than postgres at that scale, since it's so much faster at chaining queries.
@jesse @case the general rule with SQLite running in WAL mode is concurrent reads but only one process writing at a time
Since SQLite writes are mostly measured in fractions of a millisecond it turns out queuing up writes to apply once at a time will happily handle hundreds (or even thousands) of writes a second on modest hardware
@simon @case @takahe Doesn’t SQLite depend on only having one process accessing a database file at a time? I would be a bit concerned about how well background workers (of whatever flavor you choose :)) would work in that scenario.
But I also haven’t really gotten to use SQLite “in anger” and am wanting to learn more about it. :)
@takahe @case @simon @edmistond sqlite supports multiple concurrent readers, and works fine with multiple writers (one holds the write lock and the rest just wait). It works fine for single person instances. I added the sqlite tests, but keep in mind it's not a focus of the project at the moment.