Relational Databases

Relational databases have been around a long time, and even if you’ve never created or maintained one, you’re already familiar with its fundamental concepts. A relational database is analogous to a spreadsheet that contains columns and rows. In a relational database, columns are called attributes, and rows are called records. Both are stored in a table, and a database can contain multiple tables. Table 9.1 shows what a simple relational database table might look like.

Like a spreadsheet, a relational database table has a defined number of columns. Where spreadsheets and relational databases differ is that each row in a relational database table must be unique. One way of ensuring uniqueness of rows is by defining a primary key—a column that must be unique and present in each record. In Table 9.1, the primary key would be the Customer ID column.

Another way that spreadsheets and relational databases differ is that you must predefine the type of data that can be stored in each column. For example, a column for storing a person’s birthdate would be restricted to storing only numbers. Because of these requirements, relational databases are ideal for storing structured data that follows a predictable, well-defined format.

An advantage of storing structured data in a relational database is that you can quickly search an entire database for specific values. You can also retrieve data from different tables and combine that data into virtually any format you can imagine. For example, you can search for all customers with the last name Smith who have made a purchase in the last 90 days. This sort of query power is why many application developers choose relational databases. Relational databases are ideal for performing complex analytics and generating reports against large data sets. Such uses require multiple complex queries, and this is where relational databases excel.

As a rule, the larger the database and the more complex the query, the longer it takes to retrieve the data you’re looking for. Database administrators frequently tune or optimize databases to ensure the best possible performance. Longer query times and increased maintenance are the price you pay for flexible queries.

Structured Query Language

Relational databases use the Structured Query Language (SQL). You can use SQL statements to create databases and tables, as well as to read and write data. You can also perform tuning and maintenance tasks using SQL. Not surprisingly, relational databases are often called SQL databases for short.

Although you don’t need to know how to use SQL, you should be familiar with the following two SQL statements, which come up frequently in conversations around relational databases. If you’re interested in learning more about SQL, the SQL tutorial at https://www.w3schools.com/sql is a great place to start.

Nonrelational (No-SQL) Databases

Relational databases are wildly popular, but because of their restrictions, they’re also overly complex for applications that don’t need to store structured data. Also, relational databases often perform poorly when put under the strain of handling thousands of reads or writes per second.

Nonrelational databases were developed to provide a fast alternative for applications that need to perform tens of thousands of reads or writes per second. In addition to being able to handle these high transaction rates, nonrelational databases let you store data that doesn’t have a well-defined, predictable structure. Such data is often called unstructured data, in contrast to the structure imposed on data by a relational database. Because of their unstructured nature, nonrelational or no-SQL databases are said to be schemaless.

Nonrelational databases also store information in tables; tables are sometimes called collections, and each row or record is called an item. Nonrelational databases don’t require you to specify in advance all the types of data you’ll store. The only thing you have to define in advance is a primary key to uniquely identify each item. For example, to store customer data in a table, you might use a unique customer ID number as the primary key.

In exchange for the flexibility of storing unstructured data, the types of queries you can perform against that data are more limited. Nonrelational databases are designed to let you query items based on the primary key. Because the rest of the data doesn’t follow a predictable structure, a particular piece of data could be anywhere in the database. Hence, trying to query against any other data requires searching through every item in the entire table—a process that gets slower as the table grows. Nonrelational databases are best suited for applications that need to perform just a few well-defined queries.

Database Engines

When you create an RDS instance, you must choose a database engine, which is the specific RDBMS that will be installed on your instance. You can have only one database engine per instance, but you can provision multiple instances if need be. Amazon RDS supports the following six database engines:

• MySQL
• MariaDB
• Oracle
• PostgreSQL
• Microsoft SQL Server
• Amazon Aurora

With the exception of Amazon Aurora, these database engines are either open source or commercially available products found in many data center environments. Amazon Aurora is a proprietary database designed for RDS, but it’s compatible with existing MySQL and PostgreSQL databases. Being able to use RDS to deploy an RDBMS that you’re already familiar with makes migrating such databases from on-premises to RDS much easier.

Licensing

Depending on the database engine you choose, you must choose one of two licensing options: license included or bring your own license (BYOL):

License included The license is included in the pricing for each RDS instance. The Microsoft SQL Server and Oracle database engine options offer this license model. The free database engines—MariaDB, MySQL, and PostgreSQL—exclusively use the license included model.

Bring your own license In this model, you must provide your own license to operate the database engine you choose. Unlike the license-included option, licensing costs are not built into RDS pricing. This model is currently available only for Oracle databases.

Instance Classes

Implementing a relational database—even one backed by RDS—requires some capacity planning to ensure the database gives you the level of availability and performance your application needs.

When you deploy an RDS instance, you must choose a database instance class that defines the number of virtual CPUs (vCPU), the amount of memory, and the maximum network and storage throughput the instance can support. There are three instances classes you can choose from: Standard, Memory Optimized, and Burstable Performance.

Scaling Vertically

Scaling vertically refers to changing the way resources are allocated to a specific instance. After creating an instance, you can scale up to a more powerful instance class to add more memory or improve computing or networking performance. Or you can scale down to a less powerful class to save on costs.

Storage

The level of performance an RDS instance can achieve depends not only on the instance class you choose but also on the type of storage. New RDS instances use EBS volumes, and the maximum throughput a volume can achieve is a function of both the instance class and the number of input/output operations per second (IOPS) the EBS volume supports. IOPS measure how fast you can read from and write to a volume. Higher IOPS generally means faster reads and writes. RDS offers three types of storage: general-purpose SSD, provisioned IOPS SSD, and magnetic.

Scaling Horizontally with Read Replicas

In addition to scaling up by choosing a more powerful instance type or selecting high-IOPS storage, you can improve the performance of a database-backed application by adding additional RDS instances that perform only reads from the database. These instances are called read replicas.

In a relational database, only the master database instance can write to the database. A read replica helps with performance by removing the burden of read-only queries from the master instance, freeing it up to focus on writes. Hence, read replicas provide the biggest benefit for applications that need to perform a high number of reads. Read replicas are also useful for running computationally intensive queries, such as monthly or quarterly reports that require reading and processing large amounts of data from the database.

High Availability with Multi-AZ

Even if you use read replicas, only the master database instance can perform writes against your database. If that instance goes down, your database-backed application won’t be able to write data until it comes back online. To ensure that you always have a master database instance up and running, you can configure high availability by enabling the multi-AZ feature on your RDS instance.

With multi-AZ enabled, RDS creates an additional instance called a standby database instance that runs in a different Availability Zone than your primary database instance. The primary instance instantly or synchronously replicates data to the secondary instance, ensuring that every time your application writes to the database, that data exists in multiple Availability Zones.

If the primary fails, RDS will automatically fail over to the secondary. The failover can result in an outage of up to two minutes, so your application will experience some interruption, but you won’t lose any data.

With multi-AZ enabled, you can expect your database to achieve a monthly availability of 99.95 percent. It’s important to understand that an instance outage may occur for reasons other than an Availability Zone outage. Routine maintenance tasks such as patching or upgrading the instance can result in a short outage and trigger a failover.

If you use the Amazon Aurora database engine—Amazon’s proprietary database engine designed for and available exclusively with RDS—you can take advantage of additional benefits when using multi-AZ. When you use Aurora, your RDS instances are part of an Aurora cluster. All instances in the cluster use a shared storage volume that’s synchronously replicated across three different Availability Zones. Also, if your storage needs increase, the cluster volume will automatically expand up to 64 TB.

Backup and Recovery

Whether or not you use multi-AZ, RDS can take manual or automatic EBS snapshots of your instances. Snapshots are stored across multiple Availability Zones. If you ever need to restore from a snapshot, RDS will restore it to a new instance. This makes snapshots useful not only for backups but also for creating copies of a database for testing or development purposes.

You can take a manual snapshot at any time. You can configure automatic snapshots to occur daily during a 30-minute backup window. RDS will retain automatic snapshots between 1 day and 35 days, with a default of 7 days. Manual snapshots are retained until you delete them.

Enabling automatic snapshots also enables point-in-time recovery, a feature that saves your database change logs every 5 minutes. Combined with automated snapshots, this gives you the ability to restore a failed instance to within 5 minutes before the failure—losing no more than 5 minutes of data.

Determining Your Recovery Point Objective

Do you need snapshots and multi-AZ? It’s important to understand that although both snapshots and multi-AZ protect your databases, they serve slightly different purposes. Snapshots are good for letting you restore an entire database instance. If your database encounters corruption, such as malicious deletion of records, snapshots let you recover that data, even if the corruption occurred days ago (provided you’re retaining the snapshots). Multi-AZ is designed to keep your database up and running in the event of an instance failure. To achieve this, data is synchronously replicated to a secondary instance.

How much data loss you can sustain in the event of a failure is called the recovery point objective (RPO). If you can tolerate losing an hour’s worth of data, then your RPO would be 1 hour. To achieve such an RPO, simply using automatic snapshots with point-in-time recovery is sufficient. For an RPO of less than 5 minutes, you would also want to use multi-AZ to synchronously replicate your data to a secondary instance.

Items and Tables

The basic unit of organization in DynamoDB is an item, which is analogous to a row or record in a relational database. DynamoDB stores items in tables. Each DynamoDB table is stored across one or more partitions. Each partition is backed by solid-state drives, and partitions are replicated across multiple Availability Zones in a region, giving you a monthly availability of 99.99 percent.

Each item must have a unique value for the primary key. An item can also consist of other key-value pairs called attributes. Each item can store up to 400 KB of data, more than enough to fill a book! To understand this better, consider the sample shown in Table 9.2.

Username, LastName, FirstName, and FavoriteColor are all attributes. In this table, the Username attribute is the primary key. Each item must have a value for the primary key, and it must be unique within the table. Good candidates for primary keys are things that tend to be unique, such as randomly generated identifiers, usernames, and email addresses.

Other than the primary key, an item doesn’t have to have any particular attributes. Hence, some items may contain several attributes, while others may contain only one or two. This flexibility makes DynamoDB the database of choice for applications that need to store a wide variety of data without having to know the nature of that data in advance. However, every attribute must have a defined data type, which can be one of the following:

Scalar A scalar data type has only one value and can be a string, a number, binary data, or a Boolean value.

Set A set data type can have multiple scalar values, but each value must be unique within the set.

Document The document data type is subdivided into two subtypes: list and map. Document data types can store values of any type. List documents are ordered, whereas map documents are not. Document data types are useful for storing structured data, such as an IAM policy document stored in JavaScript Object Notation (JSON) format. DynamoDB can recognize and extract specific values nested within a document, allowing you to retrieve only the data you’re interested in without having to retrieve the entire document.

Scaling Horizontally

DynamoDB uses the primary key to distribute items across multiple partitions. Distributing the data horizontally in this fashion makes it possible for DynamoDB to consistently achieve low-latency reads and writes regardless of how many items are in a table. The number of partitions DynamoDB allocates to your table depends on the number of write capacity units (WCU) and read capacity units (RCU) you allocate to your table. The higher the transaction volume and the more data you’re reading or writing, the higher your RCU or WCU values should be. Higher values cause DynamoDB to distribute your data across more partitions, increasing performance and decreasing latency. As demand on your DynamoDB tables changes, you can change the number of RCU and WCU accordingly. Alternatively, you can configure DynamoDB Auto Scaling to dynamically adjust the number of WCU and RCU based on demand. This automatic horizontal scaling ensures consistent performance, even during times of peak load.

Queries and Scans

Recall that nonrelational databases let you quickly retrieve items from a table based on the value of the primary key. For example, if the primary key of a table is Username, you can perform a query for the user named pdrake. If an item exists with that primary key value, DynamoDB will return the item instantly.

Searching for a value in an attribute other than the primary key is possible, but slower. To locate all items with a Username that starts with the letter p, you’d have to perform a scan operation to list all items in the table. This is a read-intensive task that requires scanning every item in every partition your table is stored in. Even if you know all the attributes of an item except for the primary key, you’d still have to perform a scan operation to retrieve the item.

Complete Exercise 9.1 to get an idea of how DynamoDB tables work.