For small amounts of data that would work, but for a larger scale there are a few possibilities and tradeoffs. Ideally the same KV interleaving trick used to implement with-tx could (hypothetically) be made to work more generically as multiple query db sources, assuming all the KV index-stores are on the same machine. However querying over multiple nodes running on multiple machines would require a fairly different approach, assuming you need the queries to be fast. What's the use case? Multi-tenancy? Multiple tx-logs (~sharding) for write scaling?

The indexes are on the same machine. The idea is not about scale in that sense. I'm not even considering scale, it's just an idea I've been turning around in my head. I'm thinking to have a master node and a working node, the working node would store a ton of garbage, think very small, frequent updates from a GUI and I would only transact to master via a specific command. Not sure if that is the right way to do it, but I'm thinking of the difference between the working directory and commits in a git repository as an analogy. You want durability and possibly ad-hoc queries, but you keep the history in a state that makes sense for this kind of application. Alternatively I could just try it out without doing any of this - but it seems wasteful and kind of messy. I'm not considering something fundamental here?

Cool, well with-tx might be a good bet for ease and simplicity then, although it might not be the most efficient option. I would be interested to hear how you get on 🙂

I had a bit of time to experiment and I'm thinking a good alternative is to just populate the working node with current documents from the main node, exclusively query and transact on the working node and then flush to main on commit.

I have to sleep on it and experiment some more another time. But so far I think this might fit my use case better on a glance. It will get clearer once I do temporal (domain time) queries.

Either way xtdb its very fun to work with!