The Core Database Services

Lesson purpose

Most applications depend on a database, and the database choice shapes performance, availability, and cost. Organizations used to install and maintain their own database servers; AWS offers managed database services instead, where AWS installs, patches, backs up, and replicates the database while you focus on the data. This lesson compares relational and nonrelational models, then covers Amazon RDS (including Aurora), DynamoDB, Redshift, in-memory caching with ElastiCache and MemoryDB, and AWS’s streaming and analytics services. It supports CLF-C02 objectives 3.1, 3.3–3.6, and 3.8.

Learning objectives

Compare relational and nonrelational databases and choose one for a workload.
Recognize basic SQL statements: SELECT, JOIN, and INSERT.
Explain caching and compare ElastiCache (Memcached and Redis) with MemoryDB.
Describe RDS engines, licensing, instance classes, and storage types.
Distinguish vertical scaling, read replicas, and Multi-AZ, and explain Aurora’s advantages.
Explain RDS backups, point-in-time recovery, and recovery point objective (RPO).
Identify DMS and SCT for database migration.
Describe DynamoDB items, attributes, data types, primary keys, capacity, queries, and scans.
Explain what a data warehouse is and when to use Redshift and Redshift Spectrum.
Match Kinesis services, MSK, EMR, OpenSearch Service, and Data Exchange to use cases.

1. Managed databases

With a managed database service, you create the database from the console or CLI, and AWS handles:

installing and configuring the database software
patches and upgrades during a maintenance window you choose
backups and snapshots
replication across Availability Zones

You can still install a database yourself on EC2, but then all of that work — and the risk — is yours. Connect this to shared responsibility (Lesson 4): the more managed the service, the more AWS takes on.

2. Relational vs. nonrelational databases

The application’s needs decide the model, not the other way around.

Relational databases

A relational database works like a set of spreadsheets with strict rules:

A table contains columns (attributes) and rows (records); a database can contain many tables.
Each column has a predefined data type, so data must be structured.
Every row must be unique, usually guaranteed by a primary key (one or more columns that are unique and always present).

Customer ID (primary key)	Last name	First name	Last purchase
1670	Isaacson	Callan	2023-08-29
1680	Ashland	Chessa	2021-12-15
1690	Colson	Charlie	2017-02-20

Relational databases excel at flexible, complex queries across many tables — ideal for reporting and analytics. The trade-off: large databases and complex queries take longer, and database administrators must tune them.

SQL basics

Relational databases use Structured Query Language (SQL), so they are often called SQL databases. Students don’t need to write SQL for this course, but should recognize these statements:

-- Read data (SELECT)
SELECT FirstName, LastName
FROM Customers
WHERE LastName = 'Smith';

-- Combine data from two tables (JOIN)
SELECT Customers.LastName, Orders.OrderDate
FROM Customers
JOIN Orders ON Orders.CustomerID = Customers.CustomerID;

-- Write data (INSERT)
INSERT INTO Customers (CustomerID, LastName, FirstName)
VALUES (1710, 'Rivera', 'Sam');

SELECT reads data and chooses which columns come back; WHERE filters rows.
JOIN ... ON matches rows from two tables using a shared value, here the customer ID.
INSERT INTO ... VALUES adds a new row. Every time an application saves data to a relational database, it runs an INSERT (or UPDATE).

Nonrelational (NoSQL) databases

Nonrelational databases were built for applications that need tens of thousands of reads or writes per second and data that doesn’t fit a predictable structure (unstructured data). They are called schemaless.

Tables are sometimes called collections, and rows are items.
Only the primary key must be defined in advance; each item can have different attributes.
Queries by primary key are extremely fast. Searching on any other attribute requires reading the whole table, which slows down as the table grows.

Relational	Nonrelational
Complex or arbitrary queries	A few well-defined queries
Structured data required	Structured or unstructured data
Ideal for reporting and analysis	Ideal for high-transaction applications
Scale reads with replicas; scale writes vertically	Scale horizontally across partitions

3. In-memory caching: ElastiCache and MemoryDB

A cache keeps copies of frequently requested data (a product catalog, session data) in memory, where it can be read far faster than from disk or a database. A cached copy is used until its time to live (TTL) expires, and then it is refreshed from the source.

Amazon ElastiCache

An ElastiCache cluster has one or more nodes — compute instances whose type you choose based on expected demand.
Memcached engine — simple, easy to scale, multithreaded and fast, but stores only simple key-value objects and doesn’t persist data.
Redis engine — supports richer data types (strings, lists, sets, sorted sets), can persist data to disk for snapshots and recovery, and supports sorting and ranking (for example, a game leaderboard) and session caching.

Modern note: ElastiCache now also offers Valkey, an open-source, Redis-compatible engine.

Amazon MemoryDB

MemoryDB is a durable, Redis-compatible in-memory database — not just a cache. It stores data persistently across AZs, so it can be the primary database for applications that need microsecond reads, with no separate caching layer.

Distinction to stress: ElastiCache speeds up another database; MemoryDB is the database.

4. Amazon RDS

Amazon Relational Database Service (RDS) is AWS’s managed relational database. RDS creates the database instances, installs the engine, connects them to your VPC, and uses EBS for storage. You cannot SSH into RDS instances; you connect to the database endpoint.

Database engines

Each instance runs one engine:

MySQL
MariaDB
PostgreSQL
Oracle
Microsoft SQL Server
Amazon Aurora — AWS’s own engine, compatible with MySQL and PostgreSQL

(RDS has since added IBM Db2 as well.) Using a familiar engine makes migrating from on-premises easier.

Licensing

License included — license cost is part of the hourly price. Used by MySQL, MariaDB, and PostgreSQL (open source) and offered for SQL Server and Oracle.
Bring your own license (BYOL) — you supply the license; the book lists Oracle.

Instance classes

Class	Use	Notes
Standard (general purpose)	Most production databases	Balanced vCPU and memory
Memory optimized	Most demanding databases	Most memory, disk throughput, and network bandwidth
Burstable performance	Dev, test, and light workloads	Lowest cost; performance bursts

Standard and memory-optimized classes are EBS-optimized, with dedicated bandwidth to storage.

Storage types

Performance depends on both the instance class and the storage’s IOPS (input/output operations per second — how fast data can be read and written).

General Purpose SSD — right for most databases; IOPS scale with size, and volumes can burst during spikes.
Provisioned IOPS SSD — you specify the exact IOPS for consistent, high performance; no bursting needed.
Magnetic — legacy only; not for new deployments.

Storage can be increased without downtime but cannot be shrunk, so don’t overallocate. Changing storage type can cause a brief outage.

5. Scaling and availability in RDS

Vertical scaling (scale up or down)

Change to a larger instance class for more CPU, memory, or throughput, or to a smaller one to save money. Choosing Provisioned IOPS storage is another form of scaling up.

Horizontal read scaling: read replicas

Only the primary instance can write. Read replicas are extra instances that handle read-only queries, offloading the primary. They help most with read-heavy applications and heavy reports (monthly or quarterly). Read replicas do not increase write capacity.

High availability: Multi-AZ

With Multi-AZ, RDS keeps a standby instance in another AZ and replicates every write to it synchronously. If the primary fails — including during patching or maintenance — RDS fails over automatically in about one to two minutes with no data loss. The standby does not serve reads; its job is availability. Expect about 99.95 percent monthly availability.

Feature	Purpose	Serves traffic?
Read replica	Performance (more reads)	Yes, reads
Multi-AZ standby	Availability (survive failure)	No

Amazon Aurora

Aurora instances form a cluster that shares one storage volume replicated across three AZs, and storage grows automatically (the book cites 64 TB; current limits are higher). Aurora is designed for higher performance and availability than standard MySQL and PostgreSQL. Modern note: Aurora Serverless scales capacity automatically with demand.

6. Backup, recovery, and RPO

Snapshots — manual (kept until you delete them) or automatic (daily, in a 30-minute backup window, kept 1–35 days, default 7). Snapshots are stored across multiple AZs, and restoring always creates a new instance — handy for creating test copies.
Point-in-time recovery — turned on with automatic backups; transaction logs are saved every 5 minutes, so a database can be restored to within about 5 minutes of a failure.

The recovery point objective (RPO) is how much data loss is acceptable, measured in time.

RPO of 1 hour → automatic snapshots with point-in-time recovery are enough.
RPO under 5 minutes → add Multi-AZ for synchronous replication.

Snapshots and Multi-AZ solve different problems: snapshots recover from corruption or mistakes (even days old), while Multi-AZ keeps the database running through an instance or AZ failure. Multi-AZ faithfully copies a mistaken DELETE to the standby — only a backup can undo it.

Migrating databases to RDS

AWS Database Migration Service (DMS) — moves data from a source database to RDS or Aurora, including between different engines (for example, SQL Server or Oracle to Aurora PostgreSQL), while the source stays in use.
AWS Schema Conversion Tool (SCT) — converts a database’s schema and code to a different engine (for example, Oracle or IBM Db2 to Aurora or PostgreSQL, or Apache Cassandra to DynamoDB), warns about obstacles, and can identify cheaper licensing options. The book describes SCT as a download for Windows or Linux; AWS now also offers DMS Schema Conversion in the console.

7. Amazon DynamoDB

DynamoDB is AWS’s managed nonrelational database for applications that need tens of thousands of reads and writes per second with consistently low latency.

Tables, items, and attributes

A table holds items (like rows); each item holds attributes (key-value pairs) up to 400 KB per item.
Tables are stored in partitions on SSDs replicated across multiple AZs, for 99.99 percent availability.
Every item needs a unique primary key. Other attributes are optional and can differ from item to item.

Username (primary key)	LastName	FirstName	FavoriteColor
hburger	Burger	Hamilton
dstreet	Street	Della	Fuchsia
pdrake	Drake	Paul	Silver
perry		Perry

Good primary keys are naturally unique: generated IDs, user names, email addresses.

Attribute data types

Scalar — one value: string, number, binary, or Boolean.
Set — multiple unique scalar values.
Document — list (ordered) or map (unordered), able to hold nested values such as JSON; DynamoDB can pull out just the nested value you need.

Scaling

DynamoDB spreads items across partitions using the primary key. Capacity is measured in read capacity units (RCU) and write capacity units (WCU); more capacity means more partitions and more throughput. You can:

set provisioned RCU/WCU and change them as needed,
use DynamoDB Auto Scaling to adjust them automatically, or
choose on-demand mode and pay per request with no capacity planning.

Queries vs. scans

A query finds items by primary key — fast, even with billions of items.
A scan reads every item in every partition to find matches on other attributes — slow and read-intensive. For example, finding all user names starting with “p” requires a scan.

Exercise 9.1: create a DynamoDB table

Open the DynamoDB console and choose Create table.
Name the table Foods, with partition (primary) key FoodName of type String.
Choose customized settings and select On-demand capacity.
Create the table, then use Explore items → Create item to add Asparagus.
Delete the table when finished.

8. Amazon Redshift

Redshift is a data warehouse — a managed relational database built to store very large amounts of structured data from many sources and run complex analytical queries. Example: combine financial, sales, and inventory databases into one warehouse for company-wide reporting.

A Redshift cluster has 1 to 128 compute nodes and can hold petabytes (the book cites up to 2 PB).
Redshift Spectrum queries structured data directly in S3 without loading it first; you define the data’s structure so Redshift can read it.

Modern note: current Redshift uses RA3 nodes with managed storage, and Redshift Serverless runs queries without managing a cluster at all. The book’s dense compute and dense storage node types are older generations.

Database selection summary:

Need	Service
Existing SQL application, transactions	RDS or Aurora
Massive scale, simple key lookups, flexible data	DynamoDB
Analytics across huge historical data sets	Redshift
Microsecond reads from memory (cache)	ElastiCache
Durable in-memory primary database	MemoryDB

9. Analytics services

Amazon Kinesis (streaming data)

Kinesis collects, processes, and delivers streaming data in real time from thousands of sources. Streams use a producer–consumer model: producers send data in; consumers read it out.

Kinesis Video Streams — ingests and indexes (by timestamp) streaming video from cameras and phones, plus related data like audio, subtitles, or GPS. Uses: image recognition, streaming video, teleconferencing. Consumers include video players, custom apps, and services such as SageMaker and Rekognition Video.
Kinesis Data Streams — a pipeline that buffers and stores data records (logs, social feeds, financial transactions, location data) until consumers are ready. Records are ordered by partition key and sequence number, so data plays back in order. Multiple consumers can read the same stream (fan-out), usually within a second of the data arriving.
Kinesis Data Firehose — ingests streaming data, optionally transforms it with Lambda, and delivers it to destinations such as S3; it can also keep a raw backup copy. (Now called Amazon Data Firehose.)
Kinesis Data Analytics — runs your Java, Scala, Python, or SQL code against streaming data, powered by Apache Flink. (Now called Amazon Managed Service for Apache Flink.)

Amazon MSK

Amazon Managed Streaming for Apache Kafka (MSK) provides managed Apache Kafka, the open-source event-streaming platform. It is similar to Kinesis, but Kinesis is AWS proprietary while MSK runs open-source Kafka — making it easy to move existing Kafka applications to AWS. Kafka can also act as a message broker between application components.

Amazon EMR

Amazon EMR (formerly Elastic MapReduce) runs big-data framework clusters such as Apache Hadoop, Spark, Flink, Presto, and TensorFlow. The old name came from Hadoop’s MapReduce programming model; EMR now supports many frameworks.

Amazon OpenSearch Service

Runs OpenSearch clusters — an open-source fork of Elasticsearch — for full-text search and analysis of web pages, data lakes, and logs, with dashboards and integration with S3, CloudWatch, Kinesis, DynamoDB, and QuickSight.

AWS Data Exchange

A catalog of third-party data sets — weather, healthcare, financial, retail, government — delivered to S3 buckets, often by subscription. Part of AWS Marketplace; usually no refunds.

Classroom application

Model choice: For each scenario, choose relational or nonrelational and a service: a student registration system with reports (RDS), a mobile game storing player profiles and scores for millions of players (DynamoDB, with ElastiCache/Redis for a leaderboard), a five-year sales trend analysis across departments (Redshift).
Availability vs. performance: A reporting job slows down the order database. Read replica or Multi-AZ? (Read replica.) The database must survive an AZ outage. (Multi-AZ.) Someone deleted a table yesterday. (Restore from a snapshot / point-in-time recovery.)
RPO exercise: Give RPOs of 24 hours, 1 hour, and under 1 minute; students choose the protection needed.
Lab: Complete Exercise 9.1. Add a second item with extra attributes to show that items in the same table can differ.
SQL reading: Show the three SQL examples and ask students to predict each result.
Streaming match: Security cameras (Video Streams), clickstream data read by several apps (Data Streams), logs delivered to S3 every few minutes (Firehose), an existing Kafka app (MSK).

Common misconceptions

“Read replicas provide high availability.” Replicas are for read performance; Multi-AZ provides automatic failover.
“Multi-AZ protects against mistakes.” It replicates mistakes instantly; backups and snapshots are what undo them.
“Restoring a snapshot overwrites my instance.” Restores always create a new instance.
“NoSQL means no structure at all.” Every DynamoDB item still needs a primary key, and each attribute has a data type.
“DynamoDB can search any attribute quickly.” Only primary-key queries are fast; other searches require scans (or secondary indexes).
“Redshift is for everyday transactions.” It is an analytics warehouse, not an application’s transactional database.
“ElastiCache replaces my database.” ElastiCache is a cache in front of a database; MemoryDB is the durable in-memory database.
“RDS storage can be shrunk later.” Storage can grow but not shrink.

Lesson summary

Managed database services let AWS handle installation, patching, backups, and replication. Relational databases store structured data in tables of records and attributes and use SQL (SELECT, JOIN, INSERT) for flexible queries and reporting; nonrelational databases store flexible items identified by a primary key and scale to very high transaction rates with limited query options. ElastiCache caches data in memory with Memcached or Redis, while MemoryDB is a durable Redis-compatible database. RDS runs MySQL, MariaDB, PostgreSQL, Oracle, SQL Server, and Aurora with license-included or BYOL options, Standard, Memory Optimized, or Burstable instance classes, and General Purpose or Provisioned IOPS SSD storage. Scale RDS vertically with larger classes, scale reads with read replicas, and achieve high availability with Multi-AZ; Aurora shares storage across three AZs. Automatic snapshots (kept 1–35 days) plus point-in-time recovery restore data to within about 5 minutes, and the RPO determines whether Multi-AZ is also needed. DMS and SCT help migrate databases. DynamoDB stores items up to 400 KB across partitions, scales with RCU/WCU, auto scaling, or on-demand mode, and favors queries over scans. Redshift is a petabyte-scale data warehouse, with Spectrum querying S3 directly. Kinesis, MSK, EMR, OpenSearch Service, and Data Exchange ingest, process, search, and supply data for analytics.