There’s also the question of do you need transactions.

There is transactions for consistency, and there’s transactions to ensure correctness of the system state (like indexes)

Many Nostr use cases actually do not need strict consistency. There is only some level of correctness required.

CRDTs and conflict resolution fixes this. A notes app for example can be represented as a set of diffs on top of each other, and two updates to the same note can be merged.

This is also why Dynamo and eventually consistent databases exist. You could also have slightly smarter relays that can do slightly smarter queries if you want.