Lets assume a simple setup with one master db and two read replicas.
Now when someone writes into db, those changes are copied down to replicas. So that if master goes down, we can restore master from one of the replicas.
And we can also route read requests to replicas, that way reducing the load on master.
So question is after you have written onto the master, should you first wait to copy those changes to replicas and then respond or should you reply back and copy into replicas in the background. In first approach, all 3 machines will have same data (consistency) at all the time but latency will high as data first needed to be copied on all replicas. In second approach, latency will be low as you write only to few nodes and reply back but data will not be consistent, data will be on all nodes “eventually” (as copying is happening in the background).
First approach is followed by sql databases (Strong Consistency)and second approach is followed by Cassandra (Eventual consistency).
In case of eventual consistency, you have the concept of quorum where you can set number of nodes first data need to be copied before replying. More the quorum better the consistency, lower the quorum better the latency.
That’s why Cassandra provides high write throughput with low latency. Hence best used for analytical data, log data, time series data.
MySQL, Postgres provides high consistency, hence best used for transactional data like payments, banking data.
Here’s a great article on eventual consistency by Amazon CTO
