Topic lessonTopic: AWS databases: design, comparison and connectivity
RDS vs DynamoDB: choose by SQL, data relationships and access patterns
Compare RDS and DynamoDB using relationships, required reads, update boundaries and the existing application. SQL versus NoSQL, or large versus small, is not enough to decide. Use order-management and cart examples to distinguish selection reasons from remaining validation.
Use business reads, writes and migration requirements to explain candidate choices and unresolved checks for RDS and DynamoDB.
For readers familiar with RDS engines/responsibilities and DynamoDB keys/Query. Cases are fictional; no databases are created, migrated or benchmarked.
Align requirements before comparing product names
- Business reads and writes → Relations, SQL, updatesInspect existing SQL and relationships
- Business reads and writes → Keys and access patternsDesign keys from required reads
- Business reads and writes
- Results, frequency, freshness and update boundaries.
- Relations, SQL, updates
- Evaluate an RDS engine and data structure.
- Keys and access patterns
- Design DynamoDB keys, indexes and writes.
The diagram identifies design questions, not a verdict that one side is always correct or faster. Both can implement some workloads; compare redesign and operational work as well.
| Dimension | For an RDS candidate | For a DynamoDB candidate |
|---|---|---|
| Relationships | Tables, PK/FK constraints, joins and selected-engine features | Items/keys, colocated data and updates to duplicated attributes |
| Reads | Required SQL, indexes, plans, workload and results | GetItem/Query, indexes and any required Scan cost |
| Updates | Transactions, constraints and locks under engine-specific behavior | Single-item changes, conditional operations and supported multi-item transactions |
| Freshness | Database/replica destination, isolation and read behavior | Table/LSI/GSI destination and supported consistency options |
| Existing application | SQL, features, drivers and RDS management restrictions | Access-layer, key, item and index changes, with migration verification |
| Cost and operations | Deployment/capacity, storage, I/O, backups, transfer and operations | Capacity mode, items/indexes, skew, storage and read/write workload |
Example B1: order management with existing SQL and relationships
Fictional ShopOps uses SQL Server with related Customers, Orders and OrderLines tables. Placing an order updates related data, while staff change reporting conditions over time. A requirement is to retain existing SQL and database behavior where practical.
| Requirement | Reason to evaluate RDS for SQL Server | Still verify |
|---|---|---|
| Retain T-SQL and related tables | Evaluate migration within the supported scope of the same SQL Server engine | Version/edition/features, OS dependencies and ordinary RDS restrictions |
| Update related data together | Check whether engine constraints and transaction design can be retained | Actual SQL, locking, load and failure outcomes |
| Changing reporting requirements | Evaluate SQL/index changes for new questions | Plans, indexes, data volume, cost and operations |
For the same result, compare how data is associated
| customer_id | region |
|---|---|
| 101 | North |
| 202 | South |
| order_id | customer_id | status | points |
|---|---|---|---|
| 41 | 101 | PAID | 1200 |
| 42 | 101 | PENDING | 800 |
| 43 | 101 | PAID | 600 |
| 44 | 202 | PAID | 400 |
With these supplied rows, associate customer regions through customer_id and sum only PAID orders: North=1800 and South=400. A join/aggregate design differs from duplicating region on orders or maintaining aggregate items; the latter must define what stays correct when data changes.
Example B2: a keyed cart and predictable access
Fictional CartView retrieves the current cart by customer_id, lists that customer’s items and updates quantities conditionally. Reads and update boundaries are explicit, and the application access layer can be designed for them.
| Requirement | Candidate rationale | Required checks |
|---|---|---|
| Known customer-specific reads | Access can map clearly to PK/SK and Query | Other lists/search/analytics, indexes and pagination |
| Concurrent quantity changes | Evaluate supported conditional updates | Multi-item update boundaries, failures and retries |
| Immediate confirmation display | Align authoritative keys with freshness needs | Do not treat eventual GSI reads as guaranteed current |
| Increasing access | Evaluate key design and capacity mode | Hot keys, item size, measured workload and cost |
RDS can also implement a cart. DynamoDB is a candidate when its design fits the access requirements, not because avoiding SQL is always faster or cheaper. New cross-customer searches can require new indexes, aggregates or responsibilities for another service.
Do not infer atomicity or freshness from a product label
| Requirement | Meaning | Check |
|---|---|---|
| Atomicity | Whether grouped updates succeed or fail as a unit | Engine/transaction scope in RDS; supported transactional operations and limits in DynamoDB |
| Freshness/consistency | Which completed changes a read must reflect | Actual destinations: RDS replicas/isolation or DynamoDB tables/LSIs/GSIs |
Record selection reasons and assumptions to validate
- Write concrete reads, writes, relationships and consistency needs.
- Identify existing SQL/features to retain and application changes allowed.
- Design keys/tables/indexes/updates/read paths for each candidate.
- Align data volume, workload, availability/recovery and cost assumptions.
- Validate representative operations, failure outcomes, performance and cost; record the decision.
The small examples do not finalize a real deployment decision. RDS still requires engine/configuration choices; DynamoDB still requires access/update design and operation. If these two do not cover the workload, consult the AWS database decision guide for other candidates.
Match requirements to selection reasons
Choose an option to see the result and explanation.
Check, then explain in your own words
- Explain B1/B2 candidate choices beyond scale.
- Verify North=1800/South=400.
- Separate atomicity from read-path freshness.
- Retain migration/performance/cost checks as acceptance conditions.
Check your understanding: Does choosing RDS remove B1 index design, or choosing DynamoDB remove B2 operational design?
Neither. Separate provider-managed tasks from business-specific SQL/keys/indexes, access, consistency, cost and recovery decisions.
Product selection does not replace the design and validation needed to meet requirements.
Sources and conditions checked
The official references used in this lesson are collected here with their scope, conditions and corresponding sections. Independent expert review and live AWS comparison have not been performed.
- AWS: Choosing a database serviceChecked:
Scope and conditionsCompare data models and access patterns; RDS is not synonymous with SQL Server, and NoSQL does not imply no transactions.
Used in this lessonAlign requirements before comparing product names Example B1: order management with existing SQL and relationships Example B2: a keyed cart and predictable access Record selection reasons and assumptions to validate
Scope and conditionsDifferences in access and authorization; no promise of unchanged SQL/join migration.
Used in this lessonAlign requirements before comparing product names
Scope and conditionsDynamoDB supports transactions; distinguish atomicity from the freshness of a separate read.
Used in this lessonAlign requirements before comparing product names Example B1: order management with existing SQL and relationships Do not infer atomicity or freshness from a product label
- AWS: DynamoDB read consistencyChecked:
Scope and conditionsStrong reads on base tables/LSIs versus eventual GSI reads; do not generalize to all global-table modes.
Used in this lessonAlign requirements before comparing product names Example B2: a keyed cart and predictable access Do not infer atomicity or freshness from a product label
Scope and conditionsAlternative key access and index propagation; account for projection, cost and concentrated access.
Used in this lessonAlign requirements before comparing product names
Scope and conditionsOrdinary RDS and OS-dependent features; distinguish xp_cmdshell restrictions from the SQL Server product generally.
Used in this lessonExample B1: order management with existing SQL and relationships
Scope and conditionsRelated data can be designed around access patterns; tables, indexes and duplicated updates still need design.
Used in this lessonExample B1: order management with existing SQL and relationships Example B2: a keyed cart and predictable access
Scope and conditionsItems, attributes and primary keys; the example uses a simple base-table PK/SK, not every index key schema.
Used in this lessonExample B2: a keyed cart and predictable access
Scope and conditionsDistinct scopes of GetItem, Query and Scan, and supported write operations.
Used in this lessonExample B2: a keyed cart and predictable access
Scope and conditionsInspect access distribution, item size and capacity for tables/indexes; avoid unconditional performance guarantees.
Used in this lessonExample B2: a keyed cart and predictable access
Scope and conditionsShared volume versus instances; account for reader lag and interrupted connections during failover.
Used in this lessonDo not infer atomicity or freshness from a product label