1 Choosing an AWS Database

Start with the workload

Start database selection with the workload rather than a preferred service. Identify the data shape, how the application reads and writes data, consistency needs, expected scale, availability targets, and cost constraints. A system may use more than one database when different parts of the workload have different needs.

Relational data with RDS and Aurora

Relational databases organize structured data into tables with defined schemas and relationships. SQL supports joins and flexible queries, while constraints and transactions help keep related records correct. Choose a relational database when the application needs relationships, transactions spanning multiple records, or queries that are not all known in advance.

Amazon RDS supports familiar relational engines, including PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, and Db2. It is often a good fit when an application needs a particular supported engine or is moving an existing relational application to a managed service.

Amazon Aurora is compatible with PostgreSQL or MySQL. Consider it when those compatible engines fit and the workload needs Aurora's cluster architecture, scaling, or availability features. Both RDS and Aurora support SQL and relational transactions.

Neither service is an analytical warehouse by default: heavy reports can compete with an application's transactional work. Read replicas can help serve read traffic, but they are not a substitute for choosing an appropriate high-availability configuration.

Example: An order-processing application that joins orders, customers, and payments while enforcing transaction rules is a natural starting point for RDS or Aurora.

Key-value and document access

NoSQL databases are often chosen for predictable access patterns and high request rates. Amazon DynamoDB is a managed key-value and document database. Items are identified by primary keys, and an item can include attributes that vary in structure. Design tables and keys around the queries the application must perform; do not assume arbitrary relational joins will be available or efficient.

DynamoDB supports eventually consistent reads by default in many cases, and strongly consistent reads for supported table and local-secondary-index reads. Global secondary indexes and streams do not support strongly consistent reads. Transactions are available when coordinated, all-or-nothing changes across items are needed, though they can add work and cost.

Example: A shopping-cart service typically retrieves or updates a cart by customer or cart identifier. A well-designed DynamoDB key can support that direct access pattern at high request volume.

For a document workload that needs richer queries across JSON-like document fields, consider a document-oriented database such as Amazon DocumentDB. DynamoDB is not a drop-in replacement for every document database.

Analytics with Redshift

Amazon Redshift is a data warehouse for analytical processing, or OLAP, such as aggregations, reporting, and queries over large datasets. Its columnar storage and massively parallel processing architecture is designed to scan and analyze data efficiently. Redshift is designed for analysis, not as the primary store for an application's frequent, small transactional reads and writes.

Example: A retailer can keep live orders in Aurora or DynamoDB, then load or share data with Redshift to analyze sales trends across years of transactions. Combining an operational database with an analytical warehouse can be a sensible design.

Compare access, consistency, and availability

Use the query and data shape to narrow the choices:

• Related entities, joins, constraints, or complex transactions point toward RDS or Aurora because relational schemas and SQL suit relationships and flexible queries.

• Mostly known lookups or updates by key, especially at very high throughput, point toward DynamoDB because key-based access and distributed scaling suit predictable request patterns.

• Document-shaped data that must be queried across document fields may fit a document database such as DocumentDB better than key-first access.

• Queries that scan and aggregate large historical datasets point toward Redshift, which is optimized for analytical scans and aggregation.

Then check requirements beyond the data model. If a read must immediately reflect a recent write, verify the consistency options for the specific service and operation. For example, DynamoDB offers strong reads only for supported operations and indexes.

Consider what happens if an instance or Availability Zone fails. RDS Multi-AZ and Aurora cluster features provide options for regional high availability, while DynamoDB is managed across multiple Availability Zones. Cross-Region recovery requires separate planning.

Estimate scale and total cost

Estimate and test each viable option at the expected workload rather than selecting on theoretical peak scale alone. Estimate request rates and data growth, identify peak-to-average variation, and test representative queries.

Compute or capacity, storage, requests, data transfer, replicas, backups, and idle capacity can all affect total cost. Relational services may be cost-effective for small or moderate workloads, while managed, on-demand scaling can help when demand varies; actual cost depends on request volume, data design, and service configuration. Redshift can suit recurring analysis, but using a warehouse for simple application lookups is usually a poor workload match.

Apply the choices to common workloads

For a booking system that must check availability and create a reservation while preventing conflicting updates, start with RDS or Aurora because relational transactions and data relationships are central. Compare engines, operational needs, availability configuration, and measured cost.

For a game that stores player sessions accessed by player ID, with sharp traffic spikes and simple reads and writes, consider DynamoDB. Define keys and access patterns first, then decide whether eventual or strong reads are needed for each operation.

For executives querying years of sales to compare regions, products, and monthly totals, consider Redshift for the analytical workload. Keep the live transaction system separate where appropriate.

For a small application that needs a familiar PostgreSQL engine and has moderate, predictable demand, consider RDS for PostgreSQL. Compare Aurora PostgreSQL-Compatible if its architecture or features justify the added cost and configuration choices.

For each scenario, check that the proposed service supports the required queries and consistency, how it will scale, how it recovers from failure, and which cost drivers dominate. A short proof of concept using realistic data and access patterns is often more useful than choosing from service names alone.