Databases & Analytics

Storing data on disk (EFS, EBS, EC2 Instance Store, S3) has its limits. Sometimes you want to store data in a database instead: you can structure the data, build indexes to query and search it efficiently, and define relationships between datasets. Databases are optimised for a purpose and come with different features, shapes and constraints — this chapter walks the AWS catalogue.

1. Relational vs NoSQL

Relational databases look just like Excel spreadsheets, with links between them. The slide’s example uses three tables — Students (Student ID, Dept ID, Name, Email), Subjects (Student ID, Subject) and Departments (Dept ID, SPOC, Email, Phone) — joined through the shared Student ID and Dept ID columns. You use the SQL language to perform queries and lookups.

NoSQL = non-SQL = non-relational databases. They are purpose built for specific data models and have flexible schemas for building modern applications. The benefits:

• Flexibility — easy to evolve the data model
• Scalability — designed to scale out using distributed clusters
• High-performance — optimised for a specific data model
• Highly functional — types optimised for the data model

Examples of NoSQL families: key-value, document, graph, in-memory and search databases.

A common NoSQL shape is JSON (JavaScript Object Notation) — data can be nested, fields can change over time, and there is support for types such as arrays:

{
    "name": "John",
    "age": 30,
    "cars": ["Ford", "BMW", "Fiat"],
    "address": {
        "type": "house",
        "number": 23,
        "street": "Dream Road"
    }
}

2. Databases and shared responsibility

AWS offers managed versions of many databases. The benefits include:

• Quick provisioning, high availability, vertical and horizontal scaling
• Automated backup & restore, operations, upgrades
• Operating system patching is handled by AWS
• Monitoring, alerting

Exam tip

Many database technologies could be run on EC2 — but then you must handle resiliency, backup, patching, high availability, fault tolerance and scaling yourself. When a question contrasts “run it on EC2” with a managed service, the managed service is what removes that operational burden.

3. Amazon RDS

RDS stands for Relational Database Service. It is a managed DB service for databases that use SQL as a query language, letting you create databases in the cloud that are managed by AWS. The supported engines:

• Postgres
• MySQL
• MariaDB
• Oracle
• Microsoft SQL Server
• IBM DB2
• Aurora (AWS proprietary database)

Advantages over deploying a DB on EC2

RDS is a managed service:

• Automated provisioning, OS patching
• Continuous backups and restore to a specific timestamp (Point in Time Restore)
• Monitoring dashboards
• Read replicas for improved read performance
• Multi-AZ setup for DR (Disaster Recovery)
• Maintenance windows for upgrades
• Scaling capability (vertical and horizontal)
• Storage backed by EBS

BUT you can’t SSH into your instances.

A typical RDS architecture is an Elastic Load Balancer in front of EC2 instances (possibly in an Auto Scaling Group — see ELB & ASG), which read and write to the SQL database.

4. Amazon Aurora

Aurora is a proprietary technology from AWS (not open sourced).

• PostgreSQL and MySQL are both supported as Aurora DB engines
• Aurora is “AWS cloud optimized” and claims 5x performance improvement over MySQL on RDS, and over 3x the performance of Postgres on RDS
• Aurora storage automatically grows in increments of 10 GB, up to 128 TB
• Aurora costs more than RDS (20% more) — but is more efficient

Aurora Serverless

• Automated database instantiation and auto-scaling based on actual usage
• PostgreSQL and MySQL are both supported as Aurora Serverless DB
• No capacity planning needed
• Least management overhead
• Pay per second, can be more cost-effective
• Use cases: good for infrequent, intermittent or unpredictable workloads

The architecture is a shared storage volume behind a proxy fleet managed by Aurora, which clients connect to.

Real-world example

An internal reporting database that is hammered for two hours each morning and idle the rest of the day is the textbook Aurora Serverless workload — you pay per second for the burst instead of provisioning for the peak 24/7.

5. RDS deployments: Read Replicas, Multi-AZ, Multi-Region

This distinction is one of the most reliably examined items in the chapter.

Read Replicas

• Scale the read workload of your DB
• You can create up to 15 Read Replicas
• Data is only written to the main DB; replicas serve reads

Multi-AZ

• Failover in case of an AZ outage — this is about high availability, not scaling
• Data is only read/written to the main database
• You can only have 1 other AZ as failover, with replication across AZ

Multi-Region (Read Replicas)

• Disaster recovery in case of a Region issue
• Local performance for global reads
• Comes with replication cost

Exam tip

Read Replicas = performance (more read capacity). Multi-AZ = availability (automatic failover). Multi-Region = disaster recovery + global read latency. If the question asks “how do I survive an AZ failure”, the answer is Multi-AZ, even though a Read Replica also holds a copy of the data.

6. Amazon ElastiCache

The same way RDS gives you managed relational databases, ElastiCache gives you managed Redis or Memcached.

• Caches are in-memory databases with high performance and low latency
• Helps reduce load off databases for read-intensive workloads
• AWS takes care of OS maintenance / patching, optimizations, setup, configuration, monitoring, failure recovery and backups

In the solution architecture, EC2 instances behind a load balancer read and write fast from the ElastiCache in-memory database, and fall back to the slower relational database only when the cache does not have the data.

7. DynamoDB

DynamoDB is a fully managed, highly available NoSQL database with replication across 3 AZ.

• NoSQL — not a relational database
• Scales to massive workloads, a distributed “serverless” database
• Millions of requests per second, trillions of rows, 100s of TB of storage
• Fast and consistent in performance — single-digit millisecond latency
• Integrated with IAM for security, authorization and administration
• Low cost and auto scaling capabilities
• Standard & Infrequent Access (IA) Table Class

DynamoDB is a key/value database.

DynamoDB Accelerator (DAX)

• Fully managed in-memory cache for DynamoDB
• 10x performance improvement — from single-digit millisecond latency to microsecond latency when accessing your DynamoDB tables
• Secure, highly scalable & highly available

Exam tip

The difference between DAX and ElastiCache at the Cloud Practitioner level: DAX is only used for, and integrated with, DynamoDB, while ElastiCache can be used for other databases.

DynamoDB Global Tables

Global Tables make a DynamoDB table accessible with low latency in multiple Regions, using active-active replication — you can read and write to any AWS Region and the replication is two-way.

8. Amazon Redshift

Redshift is based on PostgreSQL, but it is not used for OLTP — it is OLAP, online analytical processing (analytics and data warehousing).

• You load data once every hour, not every second
• 10x better performance than other data warehouses, scales to PBs of data
• Columnar storage of data (instead of row based)
• Massively Parallel Query execution (MPP), highly available
• Pay as you go based on the instances provisioned
• Has a SQL interface for performing the queries
• BI tools such as AWS QuickSight or Tableau integrate with it

Redshift Serverless

• Automatically provisions and scales the data warehouse’s underlying capacity
• Run analytics workloads without managing data warehouse infrastructure
• Pay only for what you use
• Use cases: reporting, dashboarding applications, real-time analytics

You enable Redshift Serverless for your AWS account, connect using the Amazon Redshift Query Editor or any other tool, and pay only for the compute and storage used during analysis.

9. Amazon EMR

EMR stands for “Elastic MapReduce”.

• Helps create Hadoop clusters (Big Data) to analyze and process vast amounts of data
• The clusters can be made of hundreds of EC2 instances
• Also supports Apache Spark, HBase, Presto, Flink
• EMR takes care of all the provisioning and configuration
• Auto-scaling and integrated with Spot Instances
• Use cases: data processing, machine learning, web indexing, big data

10. Amazon Athena

Athena is a serverless query service to analyze data stored in Amazon S3.

• Uses standard SQL to query the files
• Supports CSV, JSON, ORC, Avro, and Parquet (built on Presto)
• Pricing: $5.00 per TB of data scanned
• Use compressed or columnar data for cost savings (less data scanned)
• Use cases: business intelligence / analytics / reporting, analyzing & querying VPC Flow Logs, ELB Logs, CloudTrail trails

Exam tip

Straight from the slide: analyze data in S3 using serverless SQL → use Athena. Pair it with QuickSight when the question adds “and build a dashboard”.

11. Amazon QuickSight

QuickSight is a serverless, machine learning-powered business intelligence service to create interactive dashboards.

• Fast, automatically scalable, embeddable, with per-session pricing
• Use cases: business analytics, building visualizations, ad-hoc analysis, getting business insights from data
• Integrated with RDS, Aurora, Athena, Redshift, S3

Real-world example

A common serverless analytics chain from the deck: raw logs land in an S3 bucket, Athena queries and analyzes them with SQL, and QuickSight turns the results into reporting dashboards — no servers anywhere in that pipeline.

12. Purpose-built databases

DocumentDB

Aurora is an “AWS implementation” of PostgreSQL / MySQL; DocumentDB is the same for MongoDB (a NoSQL database). MongoDB is used to store, query, and index JSON data.

• Similar “deployment concepts” as Aurora
• Fully managed, highly available with replication across 3 AZ
• Storage automatically grows in increments of 10 GB
• Automatically scales to workloads with millions of requests per second

Amazon Neptune

Neptune is a fully managed graph database. A popular graph dataset would be a social network: users have friends, posts have comments, comments have likes from users, users share and like posts.

• Highly available across 3 AZ, with up to 15 read replicas
• Build and run applications working with highly connected datasets — optimized for these complex and hard queries
• Can store billions of relations and query the graph with milliseconds latency
• Great for knowledge graphs (Wikipedia), fraud detection, recommendation engines, social networking

Amazon Timestream

Timestream is a fully managed, fast, scalable, serverless time series database.

• Automatically scales up/down to adjust capacity
• Store and analyze trillions of events per day
• 1000s of times faster & 1/10th the cost of relational databases
• Built-in time series analytics functions that help you identify patterns in near real-time

Amazon Managed Blockchain

Blockchain makes it possible to build applications where multiple parties can execute transactions without the need for a trusted, central authority. Amazon Managed Blockchain is a managed service to:

• Join public blockchain networks
• Or create your own scalable private network
• Compatible with the frameworks Hyperledger Fabric & Ethereum

13. AWS Glue

Glue is a managed extract, transform, and load (ETL) service.

• Useful to prepare and transform data for analytics
• Fully serverless
• Glue Data Catalog: a catalog of datasets, which can be used by Athena, Redshift and EMR

A typical flow: extract from an S3 bucket and Amazon RDS, transform in Glue ETL, load into Redshift.

14. AWS DMS – Database Migration Service

DMS lets you quickly and securely migrate databases to AWS. It is resilient and self healing, and the source database remains available during the migration.

It supports:

• Homogeneous migrations — for example Oracle to Oracle
• Heterogeneous migrations — for example Microsoft SQL Server to Aurora

DMS runs on an EC2 instance that reads from the source DB and writes to the target DB.

Real-world example

Moving a legacy Microsoft SQL Server estate to Aurora is a heterogeneous migration: DMS keeps the old database serving traffic while the data is copied and kept in sync, so the cutover is a short switch rather than a long outage.

Quick recap

Concept	What to remember for the exam
RDS	Managed relational (OLTP) SQL databases; automated patching, backups, Point in Time Restore; no SSH access
RDS engines	Postgres, MySQL, MariaDB, Oracle, SQL Server, IBM DB2, Aurora
Aurora	AWS proprietary, PostgreSQL/MySQL compatible, 5x MySQL / 3x Postgres, storage grows in 10 GB steps up to 128 TB, ~20% more expensive
Aurora Serverless	Auto instantiation and scaling, no capacity planning, pay per second — for intermittent/unpredictable workloads
Read Replicas	Scale reads, up to 15, writes only go to the main DB
Multi-AZ	High availability via failover to 1 other AZ
Multi-Region	Disaster recovery + low-latency global reads, at a replication cost
ElastiCache	Managed Redis / Memcached, in-memory cache, reduces load on the database
DynamoDB	Serverless NoSQL key/value, 3 AZ replication, single-digit ms latency, IAM integrated
DAX	In-memory cache for DynamoDB only, ms → microseconds; ElastiCache works with other databases
Global Tables	DynamoDB active-active multi-Region replication
Redshift	OLAP data warehouse based on PostgreSQL, columnar, MPP, SQL interface; Serverless variant available
EMR	Managed Hadoop/Spark/HBase/Presto/Flink clusters on EC2, works with Spot
Athena	Serverless SQL over data in S3, $5 per TB scanned
QuickSight	Serverless BI dashboards, per-session pricing
DocumentDB	“Aurora for MongoDB” — JSON NoSQL, 3 AZ, 10 GB storage increments
Neptune	Graph database — social networks, fraud detection, knowledge graphs
Timestream	Serverless time-series database
Managed Blockchain	Managed Hyperledger Fabric & Ethereum networks
Glue	Serverless managed ETL + Data Catalog for Athena, Redshift, EMR
DMS	Database migration, source stays available, homogeneous and heterogeneous