Database Types and How to Choose

By the end of this lesson you will be able to

• Ask the questions that decide a database: read or write heavy, throughput and spikes, size and growth, object size, access pattern, durability and source of truth, latency, concurrent users, data model, joins, schema, reporting, search and licence cost.
• Name the database families (relational OLTP, key-value, in-memory, document, wide-column, graph, time series, ledger, object store, warehouse OLAP, search, SQL over S3) and the AWS service for each.
• Tell OLTP from OLAP, and explain why a report on the checkout database hurts customers and where it should run instead (read replica, Redshift, Athena).
• Recall, in one card each, when to pick RDS, Aurora, ElastiCache, MemoryDB, DynamoDB and S3, with their exam keywords and anti-patterns.
• Use a decision map and a keyword map to answer “which database?” exam questions, and spot the trap answer.
• Recognise retired or changed services (Quantum Ledger Database, Timestream for new accounts) and new ones (Aurora DSQL, Valkey) and know what the exam still expects.

#Why the choice matters

AWS offers more than fifteen managed databases. That is not a menu where one item is the best; each one is built for a particular shape of data and a particular kind of question. Pick the wrong one and the app works on day one, then slows down, costs too much or loses data as it grows. Pick the right one and most of the hard problems (scaling, failover, backups) are already solved for you.

The notes put it this way: if you know when, why and in which architectural pattern to use each service, you can answer most database questions in the exam. They also say databases and storage make up 15 to 20% of the questions. That figure is unofficial: the SAA-C03 exam has four domains, Secure Architectures (30%), Resilient Architectures (26%), High-Performing Architectures (24%) and Cost-Optimized Architectures (20%), and database questions appear in all four. Either way, they are many.

This lesson is the map of the whole chapter. RDS, Aurora and ElastiCache were taught in chapter 7, DynamoDB in lessons 15.4 and 15.5, S3 and Glacier in chapter 10. Here each gets a short recap card and a link back. The four purpose-built engines, DocumentDB, Neptune, Keyspaces and Timestream, get one line each here and a full lesson next, in 16.2.

#The questions to ask

Before naming a service, answer these. Each one points towards some families and away from others.

Two of these deserve a sentence more.

• Source of truth. For every piece of data, decide which store holds the real copy. Everything else (a cache, a search index, a warehouse) is a copy that can lag behind or be rebuilt. Most data-loss stories begin with a copy that was treated as the original.
• Access pattern. A relational database lets you ask questions you have not thought of yet, because SQL can filter, join and group any column. A key-value store is fastest when you know every question in advance and design the key for it. “We do not know yet what we will ask” is a strong vote for SQL.

#Worked example: a ride-sharing app

A Pathao-style ride app in Dhaka answers the questions like this:

Using several stores, each for what it does best, is called polyglot persistence. It is normal on AWS, and you will see it again at the end of this lesson.

#Database types

The slides group AWS databases into families. Here they are with the service for each, the shape of data, and what each is good and bad at.

The slides call relational databases “great for joins” and NoSQL databases “no joins, no SQL”. That is the exam’s mental model; a few NoSQL services do have SQL-like languages (DynamoDB’s SQL-compatible query language, the Cassandra Query Language on Keyspaces), but none of them join tables the way a relational database does.

#OLTP vs OLAP

These two words come back in every “which database” question.

• OLTP (online transaction processing) is the day-to-day work of an application: place an order, transfer money, update a profile. Many users, many small transactions, each touching a few rows, each needing to be correct (ACID) and fast. Data is stored row by row, because a transaction reads or writes a whole row. On AWS: RDS and Aurora.
• OLAP (online analytical processing) is asking big questions about history: total sales by district and month for three years. Few users, few queries, each reading millions or billions of rows but only a few columns. Data is stored column by column, compressed, and the work is spread over many nodes. On AWS: Redshift (and Athena for files in S3).

The classic mistake is to run OLAP on the OLTP database. Watch what happens:

A read replica is the quick fix: it moves the load off the writer. But a replica stores rows like the writer does, so big reports stay slow, and it is sized for app traffic. For real analytics, copy the data to a warehouse. Aurora, RDS for MySQL and DynamoDB can feed Redshift through zero-ETL integrations with no pipeline to build (lesson 7.3). If the data is already files in S3 and the questions are occasional, Athena queries them in place and charges per data scanned. Redshift is lesson 17.2 and Athena lesson 17.1; the slides note that these belong to the data and analytics section.

#NoSQL is not one thing

“NoSQL” only says what a database is not. The five NoSQL families on AWS are very different from each other:

• Key-value (DynamoDB): you give a key, you get an item. Fastest and most scalable when every question is “get the thing with this key” or “get the items with this partition key, sorted by this sort key”.
• In-memory (ElastiCache, MemoryDB): key-value in RAM with rich data structures (sorted sets, hashes, lists, geospatial). Microseconds.
• Document (DocumentDB): each record is a JSON document that can nest and differ from the others; you can query and index fields inside documents. The answer for MongoDB applications.
• Wide-column (Keyspaces): rows grouped in partitions, built to take enormous write volumes across many nodes. The answer for Apache Cassandra applications.
• Graph (Neptune): the relationships are the data. “Friends of friends”, “accounts linked to a fraudster”, “customers who bought this also bought”.

The slides list Neptune twice, under NoSQL and again under graphs; it is one service.

#Search, analytics and the rest, in one line each

• OpenSearch (successor of Elasticsearch on AWS): a search and log analytics engine. Feed it a copy of your data for free-text search, partial and fuzzy matching, relevance ranking and log dashboards. Never the source of truth. Lesson 17.3.
• Redshift: the data warehouse for OLAP and BI. Lesson 17.2.
• Athena: serverless SQL directly on files in S3. Lesson 17.1.
• EMR: managed Hadoop and Spark clusters for big-data processing; the slides place it with warehouses. Lesson 17.4.

#Ledger: the Quantum Ledger Database is retired

#Newer services and names, 2024 to 2026

#Recap cards: the five services you already know

The notes give each service a short overview. Here they are as cards: what it is, when to pick it, the exam keywords, and when not to pick it. Follow the link for the full lesson.

#Amazon RDS

What: a managed relational database service. Traditional SQL (tables, rows, columns) in the cloud; AWS handles provisioning, OS patching and backups. You choose an instance size and an EBS storage type and size (storage auto scaling can grow it).

Engines: PostgreSQL, MySQL, MariaDB, Oracle, Microsoft SQL Server and IBM Db2. Aurora is a separate, AWS-built engine with its own card below. RDS Custom gives access to the underlying OS and database for Oracle and SQL Server.

Key features:

• Multi-AZ: a synchronous standby copy in another Availability Zone with automatic failover when the primary fails. It is for availability, not performance: the standby of a Multi-AZ instance serves no reads. It protects against an AZ failure, not a Region failure (lesson 7.1).
• Read replicas: asynchronous copies that serve read queries only, up to 15 per source on most engines (the notes say 5, an old limit; Db2 allows 3). A replica can be in another Region, which also helps disaster recovery.
• Backups: automated backups with point-in-time recovery for 1 to 35 days, plus manual snapshots kept as long as you like (lesson 7.2).
• Security: IAM database authentication, security groups, KMS encryption at rest, TLS in transit, Secrets Manager integration (lesson 7.4). Maintenance is managed and scheduled, and can mean brief downtime.

Pick it when: relational data, SQL and transactions, especially on Oracle, SQL Server or Db2, or a small steady MySQL/PostgreSQL workload where cost matters most.

Exam keywords: “high availability / disaster recovery” → Multi-AZ; “read performance scaling” → read replica; “root access to the OS or database internals” → RDS Custom (Oracle, SQL Server).

Not for: unlimited write scale, key lookups at millions per second, big files, free-text search, heavy analytics on the primary.

#Amazon Aurora

What: AWS’s own enterprise-class relational database, compatible with MySQL and PostgreSQL. AWS quotes up to 5× the throughput of MySQL and 3× of PostgreSQL on the same hardware; treat that as a marketing figure that depends on the workload.

Key features (lesson 7.3):

• Storage separated from compute: data is kept as 6 copies across 3 AZs, self-healing, and grows automatically in 10 GiB steps up to 128 TiB (256 TiB on recent engine versions; the notes say 128 TB).
• Up to 15 Aurora Replicas with very low lag, auto scaling of replicas, writer and reader endpoints (and custom endpoints). Failover usually completes in under 30 seconds; the notes say “milliseconds”, which describes the replica lag, not the failover.
• Aurora Serverless v2: capacity scales in seconds; for unpredictable or intermittent workloads with no capacity planning.
• Global Database: one primary Region and up to 10 secondary Regions (each with up to 16 read instances), storage-level replication in under one second.
• Also: backtrack and restore options, cloning (a new cluster from an existing one, faster than restoring a snapshot), Aurora machine learning (SageMaker, Comprehend, Bedrock from SQL), Babelfish for SQL Server apps.

Pick it when: the same uses as RDS, with less maintenance and more performance, availability and features.

Exam keywords: “PostgreSQL/MySQL compatible” plus “max performance”, “auto-scaling storage”, “15 replicas” → Aurora. “Global low-latency reads and Regional DR” → Aurora Global Database.

Not for: Oracle, SQL Server or Db2 without Babelfish (use RDS), or a tiny database where cost matters most.

#Amazon ElastiCache, and MemoryDB

What: a managed in-memory cache: it takes load off the database and brings response times from milliseconds down to microseconds. Engines: Valkey, Redis OSS and Memcached (lesson 7.5). You choose a node type (for example cache.m6g.large) or use ElastiCache Serverless.

The notes compare the two classic engines:

Caching strategies: lazy loading (load into the cache only when a request misses) and write-through (update the cache whenever the database is written).

Pick it when: caching query results, frequent reads with fewer writes, session data for web servers, leaderboards and counters that can be rebuilt. It needs code changes in the app (the app talks to the cache), and it cannot run SQL.

Exam keywords: “session store”, “user profiles with persistence / Multi-AZ” → Redis; “pure caching, simple key-value, multi-threaded” → Memcached.

When the cache must be the database: MemoryDB. ElastiCache replicates asynchronously, so a failover can lose the last writes; it is a cache, a copy of data that lives elsewhere. MemoryDB is a Valkey- and Redis OSS-compatible primary database: every write is stored in a Multi-AZ transaction log before it is acknowledged, so data survives node and AZ failures. Reads are still microseconds; writes take single-digit milliseconds. Keywords: “durable in-memory database”, “Redis-compatible primary database”, “no data loss” → MemoryDB.

#Amazon DynamoDB

What: AWS’s fully managed, serverless NoSQL database for key-value and document data, with single-digit millisecond latency at any scale. Multi-AZ by default (lessons 15.4 and 15.5).

Key features:

• Capacity modes: on-demand (scales automatically, pay per request) and provisioned (you set RCU and WCU, with optional auto scaling; the notes say provisioned is fixed, but auto scaling has long been available).
• DAX: an in-memory cache with the DynamoDB API; read latency in microseconds.
• DynamoDB Streams: every insert, update and delete becomes an event, for example to run a Lambda function (or Kinesis Data Streams).
• Global Tables: multi-Region, active-active replication.
• Transactions, TTL to expire items, PITR backups up to 35 days, on-demand backups, export to S3 and import from S3 without using capacity, IAM for all access control.

Pick it when: serverless apps, key lookups at any scale, spiky traffic, items up to a few hundred KB, rapidly evolving schemas. The slides add that DynamoDB can replace ElastiCache as a key-value store, for example for sessions with TTL, when you want the data to be durable and serverless.

Exam keywords: “serverless key-value store with massive scale” → DynamoDB; “microsecond latency” → DAX.

Not for: joins, ad-hoc reporting, items larger than 400 KB, free-text search.

#Amazon S3 and Glacier

What: serverless object storage: files, images, video and backups stored as objects in buckets (not block storage, not a file system). Durability is 99.999999999% (11 nines), objects can be up to 50 TB, and there is no capacity to plan. The slides describe S3 as a key-value store for objects: great for big objects, not for many tiny ones (chapter 10, starting at lesson 10.1).

The notes’ storage-class table, with the current facts (lesson 10.5):

S3 Object Lock stores objects as WORM (write once, read many): for a set period nobody can delete or overwrite them, for regulatory compliance. In compliance mode not even the root user can (lesson 10.9).

Exam keywords: “unknown access patterns” → S3 Intelligent-Tiering; “cheapest storage for long-term compliance backup” → S3 Glacier Deep Archive; “prevent deletion by any user including root” → S3 Object Lock in compliance mode.

Not for: a shared file system for servers (that is EFS or FSx, lessons 5.4 and 12.2), or many small updates inside files.

#The four purpose-built engines, in one line each

All four, with real scenarios and their own animations, are in lesson 16.2.

#The decision map

Read the requirement, then walk these steps in order. The first step that matches usually gives the answer.

1. Is it files or big objects?

   Photos, video, backups, logs, anything measured in megabytes or more: S3, with a storage class for the access pattern and Glacier classes for archives. The database keeps only the object key.

2. Is it analytics over history rather than running the app?

   Reports, BI, dashboards over terabytes: Redshift. Occasional SQL over files already in S3: Athena. Never run it on the OLTP primary.

3. Is it search?

   Free text, partial or fuzzy matching, relevance, log search: OpenSearch, fed from the source of truth.

4. Does the data have a special shape, or an existing engine?

   Graph of relationships: Neptune. Time-stamped readings: Timestream. Existing MongoDB: DocumentDB. Existing Cassandra: Keyspaces. Oracle, SQL Server or Db2 kept as they are: RDS for that engine.

5. Does it need joins, transactions across entities, or ad-hoc SQL?

   Yes: relational. Aurora for performance, many replicas, auto-growing storage and global reach; Aurora Serverless v2 for unpredictable load; RDS for commercial engines or a small, cheap, steady database.

6. Is it looked up by key at large or spiky scale?

   DynamoDB (serverless, single-digit ms). Microsecond reads on it: DAX.

7. Is it a fast copy, or must memory-speed data be durable?

   A cache, sessions, data you can rebuild: ElastiCache. An in-memory store that is the source of truth: MemoryDB.

#The keyword map

The notes end with a quick revision cheat sheet. Here it is, corrected and extended with the families from the slides:

Now practise it. Each scenario below starts with the requirement, shows the tempting wrong answer failing, then the right one:

And when there is no exam question, only your own app, answer the questions one at a time:

#What goes wrong

Most database trouble on AWS is not a bug in the database; it is a database used for something it was not built for. Six common wrong picks:

#Databases in system design

• One store per job, one source of truth per fact. Polyglot persistence is normal, but every fact needs exactly one home. Caches, search indexes and warehouses are copies fed from it, and every copy lags a little.
• Let the data model and access pattern decide, not fashion. “It scales” is not a reason to choose DynamoDB for relational data; “we know SQL” is not a reason to keep graph or time-series data in RDS.
• Separate OLTP from OLAP from day one. Even a small shop should send reports to a replica, and grow into Redshift or Athena.
• Serverless where load is unpredictable. DynamoDB on-demand, Aurora Serverless v2, ElastiCache Serverless, Athena: capacity follows traffic.
• Encryption and access the same way everywhere: KMS keys for data at rest (chapter 21, lesson 21.2), TLS in transit, IAM and security groups for access.
• Moving off old engines: DMS copies the data with little downtime, SCT converts the schema when the engine changes (Oracle to Aurora PostgreSQL), Babelfish keeps SQL Server apps running on Aurora (lesson 23.3).

Here is a single shop using six stores, and two things that go wrong with copies:

#Try it yourself

#Exercise: pick the store

Answer each before reading the solution below the list.

1. A university stores exam results with students, courses and semesters, and the registrar runs new queries every term.
2. A mobile game needs each player’s inventory, read and written by player ID, 200,000 times a second at launch.
3. The same game’s global top-100 leaderboard must update in real time; it can be rebuilt from the inventories.
4. A bank must find accounts connected to a known mule account within four transfers.
5. A logistics company keeps 7 years of delivery records for audit, almost never read.
6. A marketing team wants to ask SQL questions about last month’s app logs, already in S3, twice a month.

Solutions: (1) Aurora or RDS, relational with ad-hoc queries; (2) DynamoDB on-demand, key access at scale; (3) ElastiCache sorted set (rebuildable), or MemoryDB if it must be durable; (4) Neptune; (5) S3 Glacier Deep Archive, with Object Lock if it must be tamper-proof; (6) Athena.

#See what is on offer in your Region

These read-only commands show the engines and versions each service offers. Region ap-southeast-1; outputs are shortened.

# RDS engines and versions (Db2 included)
aws rds describe-db-engine-versions --query 'DBEngineVersions[].Engine' --output text | tr '\t' '\n' | sort -u
#   aurora-mysql  aurora-postgresql  custom-oracle-ee  custom-sqlserver-ee  db2-ae  db2-se
#   mariadb  mysql  oracle-ee  oracle-se2  postgres  sqlserver-ee  sqlserver-ex  sqlserver-se  sqlserver-web

# Aurora PostgreSQL versions
aws rds describe-db-engine-versions --engine aurora-postgresql \
  --query 'DBEngineVersions[-3:].EngineVersion'
#   [ "16.6", "17.4", "17.5" ]

# ElastiCache and MemoryDB engines (Valkey is there)
aws elasticache describe-cache-engine-versions --query 'CacheEngineVersions[].Engine' --output text | tr '\t' '\n' | sort -u
#   memcached  redis  valkey
aws memorydb describe-engine-versions --query 'EngineVersions[].[Engine,EngineVersion]' --output text
#   redis   7.0
#   redis   7.1
#   valkey  7.2

# DocumentDB and Neptune use the RDS-style API (details in lesson 16.2)
aws docdb describe-db-engine-versions --query 'DBEngineVersions[].EngineVersion'
#   [ "4.0.0", "5.0.0", … ]   (lesson 16.2 checks which versions are current)
aws neptune describe-db-engine-versions --query 'DBEngineVersions[-1].EngineVersion'
#   "1.4.5.1"

# DynamoDB: one table, nothing to size in on-demand mode
aws dynamodb create-table --table-name carts-example \
  --attribute-definitions AttributeName=shopperId,AttributeType=S \
  --key-schema AttributeName=shopperId,KeyType=HASH \
  --billing-mode PAY_PER_REQUEST
#   "TableStatus": "CREATING", "BillingModeSummary": { "BillingMode": "PAY_PER_REQUEST" }
aws dynamodb delete-table --table-name carts-example

Version numbers change often; what matters is the shape. RDS, Aurora, DocumentDB and Neptune ask for engines, versions and instances. DynamoDB asked only for a key.

#Summary

Key takeaways

• Choose by asking: read/write mix, throughput and spikes, size and growth, object size, access pattern, durability and source of truth, latency, concurrency, data model, joins, schema, reporting, search, licence cost.
• OLTP (many small ACID transactions, row store) → RDS / Aurora. OLAP (big questions over history, columnar) → Redshift; SQL on files in S3 → Athena. Never run reports on the OLTP primary.
• NoSQL families: key-value DynamoDB, in-memory ElastiCache / MemoryDB, document DocumentDB, wide-column Keyspaces, graph Neptune, time series Timestream. Plus object S3/Glacier and search OpenSearch.
• RDS: six engines incl. Db2; Multi-AZ = synchronous, HA within a Region; read replicas = async, up to 15; PITR 1–35 days; RDS Custom for OS access.
• Aurora: MySQL/PostgreSQL compatible, 6 copies in 3 AZs, storage to 128 TiB (256 TiB new), 15 replicas, failover under 30 s, Serverless v2, Global Database.
• ElastiCache = cache (Redis/Valkey: persistence, Multi-AZ, data structures; Memcached: simple, multi-threaded). MemoryDB = durable in-memory primary database.
• DynamoDB: serverless key-value, single-digit ms, on-demand or provisioned (+ auto scaling), DAX for µs, Streams, Global Tables, 400 KB items.
• S3: 11 nines, objects to 50 TB; Intelligent-Tiering for unknown patterns (no minimum duration), Deep Archive cheapest, Object Lock compliance mode stops even root.
• QLDB ended support in July 2025 (Aurora PostgreSQL recommended). Aurora DSQL and Valkey are new; the exam answers remain Global Database and “Redis”.

Self-check: Database types and how to choose32 questions