[powersurge360]

I haven't done this, but I've been thinking about it lately. Fly.IO has had some very interesting ideas on this if you want to use a relational database. There was an article about litestream that would allow you to replicate your SQLite database to an arbitrary number of nodes, which means that every application server would have a SQLite file sitting on it for read queries, and then you can capture write queries and forward them to a write leader and let that user continue talking to that server until it replicates across your application servers.

You can do basically the same idea with any relational database, have a write leader... somewhere and a bunch of read replicas that live close to the edge.

There's also what you would call cloud native data stores that purport to solve the same issue, but I don't know much about how they work because I much prefer working w/ relational databases and most of those are NoSQL. And I haven't had to actually solve the problem yet for work so I also haven't made any compromises yet in how I explore it.

Another interesting way to go might be CockroachDB. It's wire compatible w/ PostgreSQL and supposedly automatically clusters and shares data in the cluster. I don't know very much about it but it seems to be becoming more and more popular and many ORMs seem to have an adapter to support it. May also be worth looking into because if it works as advertised you can get an RBDMS that you can deploy to an arbitrary number of places and then configure to talk to one another and not have to worry about replicating the data or routing correctly to write leaders and all that.

And again, I'm technical, but I haven't solved these problems so consider the above to be a jumping off point and take nothing as gospel.

[nojvek]

This goes into CAP theorem. Do you want async/inconsistent read replicas or consistent replicas with guaranteed “read new info after write everywhere” semantics.

To get consistent reads, every write needs to wait to ensure every node has acknowledged the write and then return.

So yes, you can have single digit ms sql queries, but you’d pay the price of writes being bottlenecked by speed of all nodes acknowledging the writes.