we are self hosting kratos. We build systems with 3 redundant servers for our customers. I am trying to chhose a database to use for kratos. postgresql appears to support replicating the data to backup servers but the selection of a leader is manual. cockroachDB requires at least 3 servers to run so I would have to run 2 instances on each server putting sqllite on top of liteFS light work but this depends on Fuse and fuse and containers doesn't seem to play well. dqlite might be an option... does anyone have any experience of dqlite - is it api compatible with sqlite? would the Ory team have any other reccomendations?

Have you considered Yugabyte?

I have not - however is Yugabyte supported by kratos? if it was easy to add a backend database I would add ectd, we typicially have some tens of users so a trivial key/value database would be enough, and we already use etcd so it would fit well; however the ory team have made it clear that this would be hard and not supported in future kratos releases so it would not be a good use of resources.