Relational and Key-Value Data Stores

Goal

Storage shape answers one class of questions. Database selection answers another: how does the application create, update, query, and protect business state?

This section focuses on a common AWS decision: relational databases with RDS or Aurora versus key-value and document-style access with DynamoDB. The goal is not to make one sound modern and the other old. The goal is to match the database to the questions the application must answer.

The Short Version

Database choice follows the shape of questions the application must answer.

Use this first-pass map:

Flexible relational queries + transactions -> RDS or Aurora
Known high-scale key access patterns        -> DynamoDB
Derived read model, search, or analytics    -> Secondary data service

Relational databases are strong when the workload needs SQL, transactions, schemas, joins, constraints, and flexible query patterns. DynamoDB is strong when access patterns are known, high scale matters, and the model can be designed around keys, items, indexes, and predictable reads and writes.

RDS And Aurora: Managed Relational Choices

Amazon RDS is AWS's managed relational database service. It supports familiar database engines such as PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, and Db2. Amazon Aurora is AWS's cloud-optimized relational database engine compatible with MySQL and PostgreSQL.

Use relational databases when the workload benefits from:

• SQL queries.

• Transactions across related records.

• Schemas and constraints.

• Joins.

• Flexible reporting and operational queries.

• Mature relational tooling and team experience.

• Clear auditability for business records.

Orders, payments, invoices, customer account changes, and inventory adjustments often fit relational thinking because the business cares about correctness, constraints, and change history. A relational database does not make all of that automatic, but it gives the application a natural model for transactional state.

Managed does not mean zero design. You still need to decide engine, instance or serverless model, Multi-AZ posture, read scaling, backup retention, encryption, maintenance windows, schema migration approach, monitoring, and failover expectations.

DynamoDB: Known Access Patterns At High Scale

Amazon DynamoDB is a fully managed NoSQL database service for key-value and document-style data. The important design habit is that DynamoDB modeling starts with access patterns.

That means you ask:

• Which items are read by primary key?

• Which queries need a sort key or secondary index?

• What are the high-volume reads and writes?

• Which attributes are needed together?

• What consistency does each access path need?

• What partition keys avoid hot spots?

DynamoDB can be a strong fit for high-scale access patterns such as shopping cart items, session state, user preferences, device state, event metadata, game state, or catalog read models. It can also support transactional operations and secondary indexes, but it is not a drop-in replacement for relational modeling. If a team needs arbitrary joins and exploratory SQL, DynamoDB will feel painful.

The architecture strength of DynamoDB appears when the application can describe its access paths clearly and design the table around them.

Query Flexibility Versus Query Predictability

The trade-off is often not "SQL versus NoSQL." It is flexibility versus predictability.

Neither side is free. Relational systems can become overloaded if every read model, report, and search feature hits the same operational database. DynamoDB can become difficult if access patterns change constantly or the team has not modeled keys carefully.

Example: Commerce Data

Consider a commerce platform:

• Orders and payments may use Aurora or RDS because they need transactional consistency, auditability, and carefully controlled updates.

• Product catalog management may use a relational model if editors need flexible relationships, approval workflows, and integrity checks.

• Product catalog reads may use DynamoDB if the customer-facing application needs predictable, high-scale lookups by product ID, category, or tenant.

• Customer sessions or carts may use DynamoDB or ElastiCache depending on durability and access needs.

• Product search should probably use OpenSearch as a derived index, not the transactional database.

• Sales analytics should use Redshift or data lake patterns, not the checkout database.

The key design move is to separate source-of-truth state from optimized read models. An order database, a catalog read table, a search index, and an analytics table can all contain product or order information, but they should not all have the same authority.

Common Mistakes

• Choosing NoSQL because scale is desired without modeling access patterns.

• Choosing relational storage for every read model and then fighting performance.

• Using DynamoDB like a relational database with hidden joins in application code.

• Treating a cache, index, or analytics table as the source of truth.

• Ignoring schema migration, backup, restore, encryption, and monitoring because the database is managed.

If Someone Asks You

How do you choose between RDS/Aurora and DynamoDB?

A good answer is:

I choose RDS or Aurora when the workload needs relational modeling, SQL, transactions, joins, constraints, and flexible queries. I choose DynamoDB when the access patterns are known, the workload needs high-scale key-value or document-style access, and the team can model keys and indexes intentionally. I also separate source-of-truth data from derived read models, search indexes, analytics stores, and caches.

What You Should Remember

A database is chosen around access patterns, consistency needs, and ownership.

Questions to Check Your Understanding

1. What kinds of workloads benefit from relational transactions?

2. Why does DynamoDB design start with access patterns?

3. What is the risk of treating a cache or index as the source of truth?