Database Architecture

A database is a high-level storage system an application can communicate with to store data. Data is accessed not on a file-level but rather on a record level in most cases. Databases also make it easy to search the data since they usually contain an index that makes them searchable quickly.

Customers can operate databases on Exoscale instances, and some databases offer built-in redundancy options where the data is automatically replicated to a secondary instance, also running a database server.

Commonly used database servers include MySQL (variants: MariaDB, Percona), PostgreSQL, Oracle, MongoDB, Microsoft SQL Server, Apache CouchDB, and many more.

Database Redundancy/Failover

Fortunately, most database systems, SQL, or NoSQL, offer some form of replication. Replication means that the database software establishes network connectivity with its redundant counterparts and continuously updates all servers’ data.

However, not all web applications handle multiple database servers, so sometimes, a load balancer has to be used.

This load balancer enables non-cloud-friendly software to use multiple database servers. It will also regularly check if the databases are online and direct traffic away from offline database servers.

Deploying a database cluster confronts you with the consideration of one of the multiple synchronization methods available. The following sections cover how to choose the right one for your use case.

Data Consistency

The CAP Theorem: When building a redundant system, data consistency is of the utmost importance. Imagine you have an accounting system that keeps track of a customer’s balance on their account. This system consists of several database servers. The customer now creates two transactions that withdraw from their account, both of 10 EUR. If the customer’s account has 15 EUR on it, both transactions could be individually complete, but together they would land the account at -5 EUR.

If these two transactions hit two different servers, one of two things can happen.

In scenario one, the server that receives the request locks the record across multiple servers before processing it. In this case, the other request has to wait for the first to be complete. In this scenario, the second transaction is rejected for the lack of funds. We call this setup strongly consistent, and it has a couple of drawbacks we discuss in a minute.

In scenario two, the server does not lock the record. Depending on how the system is written, again, one of two things can happen. In a well-written system, the account shows a balance of -5 EUR. In a poorly written system, the two changes might overwrite each other when the database synchronizes, and the customer’s account shows 5 EUR.

As you can see, the choice of database synchronization method is significant when building a redundant system. Having the customer’s balance below zero may be acceptable in some situations, but losing a balance change is not good.

To understand the impact, let’s look at how data is stored. As a first example, let’s assume the information is stored as a list of transactions:

The total is not stored anywhere, and it is always recomputed on the fly. In this case, the total is 15 EUR, but that number is never stored. Let’s see what happens when the two transactions happen on different servers at the very same time:

As you can see, the two versions can be cleanly merged, and the resulting balance is -5 EUR. First, however, let’s look at the storage method, where the total balance is stored:

As you can see, if the entire field Is synchronized, both servers have 5 EUR, and the calculation is wrong. Therefore, if the application works as shown, we need a strongly consistent database to avoid losing changes. The same is true if the customer’s balance going below zero is not acceptable.

However, consistent databases have a significant drawback: synchronizing locks across all servers need to form a quorum. A Quorum is a majority vote among the servers. If a server or group of servers loses the connection with most servers, they will no longer be able to serve traffic.

In contrast, if a negative balance is acceptable and an application acts that way, a minority of servers can still service customers and resynchronize with the cluster once the connection is established again without data loss. This method of organizing data is called a conflict-free replicated data type or CRDT.

You may have experienced such a system at work if you, for example, use an online editor like Google Docs. Even offline, you can edit your documents, and the changes are synced with the cloud once you go online. If, in the meantime, someone else has changed something, the changes are merged automatically.

The CAP theorem is a very much simplified version of this. It states that out of the following three, you can pick two:

• Consistency
• Availability, and
• Partition Tolerance.

Consistency means that you either get up-to-date data or you get no data at all. However, you never get out-of-date data. Availability means that you always be able to get data, but it may not be up-to-date. Partition Tolerance means that the system does not crash if the servers are split into more than one group.

As you may have guessed, the P part is not optional, so you can only really pick between CP and AP. Furthermore, AP systems are often referred to as eventually consistent, showing that all nodes will eventually become consistent if the system is left alone long enough.

If you want to learn more about distributed systems, we recommend watching Tim Berglund’s Distributed Systems in One Lesson.

Database Solutions

Generally, databases come in two flavors: SQL and NoSQL. Or, more accurately, relational database systems and non-relational database systems.

The difference is that relational database systems store relations of records to each other. For example, a relational database system may store a customer record and purchase orders. Each purchase order would then have the customer ID in them. Thus, a relational database system would not let you a purchase order with an incorrect customer ID because it knows about the relation between the records.

Relational database systems often also support transactions, although some non-relational database systems also support transactions. Transactions group modifications to a database so that either all of the changes go through or none of them. This is important because if a modification fails halfway, the database is not left in an inconsistent state.

Database Vendors