Primer on Database Types

Pretend you have a distributed database. It doesn’t matter which one, but it runs on more than one discrete server. You have clients that talk to this distributed database and ask it questions, such as "What is the name of the user with the ID of 1?"

Or, in SQL-ish:

SELECT first_name, last_name FROM customers WHERE id = 1;

Let’s say that two clients issue this same query. Query 1 hits Server 1, Query 2 hits Server 2. If a system is consistent, then both clients will receive "Perceval Grimwhisker" back as the answer.

Now let’s say Perceval decided to change his name and updated his profile. Two clients again issue the same query. If a system is not consistent, one client would "Perceval Grimwhisker" while the other client might receive his new name "Xochipilli Deathpaw".

You still have your pretend database, but it now consists of 5 servers. If a system is available, then if Node 5 dies, Nodes 1-4 are still able to respond to queries. If it is not available then one node being down means the other servers cannot respond to queries.

This one is important: CAP Theorem only applies to distributed data stores. This means that they exist on a network. If that network fails, in whole or in part, you have what is known as a network partition. You may also hear the term split-brain used to refer to similar situations.

Let’s say you still have that 5 node cluster. The network fails in such a way that Nodes 1 and 2 can talk to each other, and nodes 3, 4, and 5 can talk to each other, but the two groups cannot talk to each other.

All distributed datastores have to be able to deal with this.

You’ll often hear "CAP, pick two". This is because the P is required, and general wisdom says you can have a CP system or an AP system, but not a CAP system. This is both true and not true. When you are choosing C or A, what you are choosing is which one you get when the network partitions. When the network is working fine, you get all three. This is the most frequent misunderstanding I hear from people about CAP.

Let’s say that in this setup, you have one Postgres node that can accept writes (this Primary node), and then you have one Replica node that mirrors all data on the Primary node. Where does this fall in terms of CAP? Let’s look at failure scenarios:

In a MongoDB Replica Set, you have 1 Primary and N Replicas. By default, the client will send all reads and writes to the Primary. If the Primary fails, one of the Replicas is promoted to Primary, and life goes on.

There’s some nuances to this that are worth talking about, though. Some are specific to Mongo, but some aren’t.

Another term you may have heard is "quorum", which means: > the minimum number of members of an assembly or society that must be present at any of its meetings to make the proceedings of that meeting valid.

A data storage system with more than one member (i.e., a cluster) usually has a concept of quorum. If you have a 5 node cluster, and 2 are down, then the cluster still has quorum, because a majority of members are present. If 3 nodes are down, we don’t have quorum. In this situation, a DB could choose to disallow all writes, flip to read only, or other variations.

A database has varying degrees of ACID-compliance. Postgres is an example of a full ACID-compliant database. NoSQL databases tend to be less ACID-compliant, but this can vary.