The CAP theorem

The CAP theorem coined by computer scientist Eric Brewer, offers a way to formalize the trade-offs between consistency and availability in the presence of partitions, in distributed systems. The theorem states that we will need to choose 2 out of the following three guarantees while designing a distributed data store.
Consistency: Every read receives the most recent write or an error
Availability: Every request receives a (non-error) response, without the guarantee that it contains the most recent write
Partition tolerance: The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes

Wikipedia

Let’s look at the different system design options (databases particularly) in detail:

1. Consistency and Availability over Partition tolerance

Here we prefer having some data and same data for every request, than having isolated data in the system. Think about a set up where you have primary database to which you read/write and an isolated secondary database which is merely a replica of the primary.

Eg: Postgres, MySQL, SQL server, Oracle, IBM DB2 and most of the relational databases.

2. Consistency and Partition tolerance over Availability

In this mode, we prefer same data for requests, and compromise or restrict the availability of the stores when there is a partition. For instance, we may allow read from all the data stores in the system, but will restrict the write operation when there is a network partition or disconnect.

Eg: Apache HBase, MongoDB, CouchBase,Memcached, redis etc. Cassandra and Amazon DynamoDB may be configured to work this way.

3. Availability and Partition Tolerance over Consistency

Here we give up consistency in favour of availability and Partition tolerance. You can read/write even when there is a network partition or disconnect in the system. This will result in inconsistent data in the store.

Eg: Cassandra, CouchDB, Amazon DynamoDB

In most of the distributed systems we have today, there is some levels of varying ratios of C, A and P incorporated. I will soon expand this article or write another one to talk more about it.