I mean, AcitivityPub is pretty much the ideal case for this architecture. ActivityPub/the underlying ActivityStream consists of activities that manipulates entities. The activities are in principle immutable, even if the entities they may create are not, and you can largely naively serialize them into a log, and partition them naively by actor or by server and actor or any number of schemes, to put them into the initial "depot".

From there, materializing indexes is trivial, and replicating that is also fairly trivial. You "just" need a bunch of workers processing the depot logs into these "P-stores"

If your requirements fits that, then recovery from failures "just" requires having replicas of the depots, which is "easy" as you just stream the logs elsewhere, and archive them to, say, a blob store, combined with the ability to reset the "cursors" of the workers populating the P-stores.

It's an architecture that works very well when you want to index and query large sets of data where the order either doesn't matter (much) or you can order "well enough" from the data itself, so you can stream to multiple "depots" without worrying about assigning a global order. Such as ActivityPub. E.g. it doesn't really matter for Mastodon if a reply appears a second before the post its a reply to, because the key linking them together is there and you need to be able to gracefully handle the case where you never get the original or isn't even allowed to fetch it.

So I don't doubt they can achieve this, and a platform that makes that easy would be nice.

Their problem is that 1) very few people have data problems that are large enough that it isn't easier to "just" buy/rent a larger database for the depots, 2) or even that have a dataset large enough that they'd need a secondary set of servers to handle the materialization of views (or need the views to actually be materialized in the first place), 3) and even if they do, running workers to populate a secondary set isn't hard. It's also trivial to use tools your devs are already familiar with for the depots and "P-stores". E.g. Postgres works just fine for both until you have an architecture far larger than most people ever need to deal with, but there are many other options too.

Once you start running into challenges with that, the obvious, well-known method is to stream into append-only logs, cut the logs regularly, run the indexers on the new log elements into indexed segments, and zipper merge the log elements and do streaming compression of the values (e.g. arithmetic coding is a common, trivial method) + a skip list. Since the point of these "P-stores" is to reduce the datasets to something where queries are low-complexity, you can generally assume you won't need to support much fancy re-ordering and grouping etc., mostly cheap filtering, coupled with streaming merges of query results from multiple P-store partitions. This is the "do it yourself" solution, which you can find "packaged up" in any number of search solutions, like Elasticsearch, Lucene, Sphinx etc., but it's also not all that hard to build from scratch for a specific use case.

This method has been used for e.g. search engines "forever". First time any system I worked on actually needed it was ca 2006, and we first used Sphinx, then built a custom one (single developer effort; needed only because Sphinx at the time had only a fraction of the features it has today)

Basically, they're building a distributed database that just has very opinionated ideas about the type of problems you should be solving and how. If your problem fits in that it's very possible they can provide something "turn key" that is more cohesive and better packaged up than picking and choosing your own components for the depot and indexes and writing your own indexing workers, but as you say, to most people the extreme scalability isn't the issue and a regular database is enough.

Addressing the Twitter and Mastodon cases is kind of a warning here, because both Twitter and Mastodon (the software) started out with a tremendously naive architecture in this respect, so it's a low hanging fruit if you want to show off impressive-looking improvements.

In terms of reporting, this architecture isn't a problem, because you'd not be working from events, but from the "P-stores" and nothing stops you from ensuring that data is trivially queryable with something reasonable. E.g. either using something like Postgres for the P-stores themselves, or just streaming the data into whatever you want to do reporting from.

But again, it doesn't need to replace the database.