6 Database Selection Case Studies

Match the database to the workload

Database selection starts with the workload, not the name of a service. Identify the data model, access patterns, transaction needs, latency and throughput targets, availability and recovery objectives, and operational constraints. A solution can combine databases when different workloads need different strengths; forcing every use case into one database may not fit those requirements.

For each workload, describe the stored data and the reads and writes the application performs. Then specify consistency, transaction, latency, traffic-peak, availability, recovery, and query requirements. Relational data and flexible SQL queries point toward RDS or Aurora; predictable key-based access at high scale can suit DynamoDB; and large-scale analytical queries point toward Redshift.

After selecting a likely data model, compare compatible services and deployment options for scaling, resilience, operational effort, and cost. Verify that the required engine features and deployment options are available in the intended Region. Test the design with representative data and query patterns, measure latency, throughput, failover and recovery behavior, and cost, and revise the design if it misses requirements.

Use a managed relational engine

For an established application using PostgreSQL, SQL joins, constraints, and transactions, Amazon RDS for PostgreSQL is a reasonable starting point when the team wants managed backups and a familiar engine but has no demonstrated need for unusually high database throughput. RDS manages common database operations while retaining a familiar relational engine. Configure high availability to meet the application's failover requirements.

If read traffic grows, evaluate read replicas for read-only queries. RDS read replicas replicate asynchronously, so check whether replica lag is acceptable for the queries routed to them. A Multi-AZ standby intended for failover is not automatically a read-scaling replica; check the selected deployment type and engine capabilities.

Test the application's SQL and extensions against the chosen engine and version. Measure transaction latency under expected load, verify backup restoration and failover procedures, and confirm whether replica-routed queries require read-after-write behavior. RDS can be a sensible starting point when engine compatibility and managed operations matter more than adopting Aurora's cluster architecture.

Evaluate relational read scaling

An order service with relational data and transactional rules may need to serve many reads while maintaining a writable primary database and accommodating variable load. In this case, evaluate Amazon Aurora with a compatible MySQL or PostgreSQL engine. Aurora separates shared cluster storage from database instances; reader instances can serve read traffic, and Aurora can fail over to a reader if the primary becomes unavailable.

Aurora may suit workloads that benefit from its cluster design and read scaling, but it is not automatically the best or least costly choice for every relational workload. Compare it with RDS using the actual engine, instance sizes, I/O profile, availability needs, and migration effort. Keep transactional writes on the writer, and route reads to readers only when the application can tolerate the relevant replication behavior.

Benchmark representative transactions and concurrent reads. Test failover from the application's perspective, check client reconnection behavior, and model costs using expected compute, storage, and I/O usage.

Design for key-based access

A shopping cart or session workload can suit Amazon DynamoDB when requests usually retrieve or update data by a known identifier, traffic is bursty, and arbitrary SQL joins are not needed for those operations. DynamoDB is a managed key-value and document database designed for high-scale access.

Design keys and indexes around the application's known queries. For example, a customer ID as a partition key and an item identifier as a sort key can support retrieving and updating a customer's cart items. DynamoDB schema design begins with access patterns: queries spanning unrelated keys or requiring flexible joins may need additional indexes, denormalized data, or application-level composition. Poorly distributed partition-key values can create hot partitions, so account for traffic distribution when choosing keys.

List every required query before finalizing keys and indexes. Test peak and skewed traffic, item sizes, consistency needs, and capacity mode. Confirm that the design supports required updates and that the application handles any denormalized data consistently.

Separate analytics from operations

When analysts need dashboards that scan and aggregate sales from many months or years, combine data from operational systems, and support business-intelligence queries, Amazon Redshift is suited to the analytical warehouse role. Load or ingest operational data through an appropriate data pipeline, then run reporting and analytical queries in Redshift. Redshift is optimized for high-performance batch analysis and reporting over datasets, rather than serving as the default operational database for application transactions.

Data must be made available to the warehouse, and reporting freshness depends on the ingestion design. Do not assume the warehouse automatically contains current operational data: define acceptable latency and put the pipeline into operation.

Test representative dashboard queries using realistic data volumes and concurrency. Measure ingestion freshness, query response times, and warehouse costs, and confirm that analytical workloads remain isolated from latency-sensitive application transactions.

Combine databases by responsibility

An e-commerce system may need atomic checkout operations that create orders and update inventory, fast cart lookups, and reports across order history and customer activity. These distinct responsibilities can be assigned to different services:

• Aurora or RDS: orders and inventory, where relational constraints, joins, and transactions matter.

• DynamoDB: carts or sessions, where access is primarily key-based and traffic can be highly variable.

• Redshift: historical reporting and analytical queries, populated by a data pipeline from operational systems.

Keep each service authoritative for its own data. Define how events or changes flow between systems, how duplicate or delayed updates are handled, and which system is the source of truth for each business entity. Data copied into analytics or other read models can be delayed or temporarily inconsistent with its source.

Multiple databases add integration, monitoring, security, and operational complexity. Use this design only when the benefits for distinct workloads justify that complexity. Exercise a complete order journey, including failure and retry cases; check that inventory and order rules remain correct, cart operations meet latency targets, and warehouse data arrives within the reporting freshness objective. Monitor each data flow for failures and lag.

Validate the complete design

Before committing to a design, record the workload's transactions, key lookups, or analytical scans; its read/write mix; and its average and peak load. Document data relationships, joins, query patterns, item sizes, and required indexes. Specify transaction boundaries, consistency expectations, and the authoritative source for each record.

Also record availability targets, backup and restore tests, failover behavior, and recovery objectives. Review engine compatibility, team skills, scaling approach, monitoring, migration, and data movement. Estimate compute, storage, I/O, capacity, replica, and pipeline costs.

Base the decision on benchmark results and tested recovery procedures, not assumptions based only on service descriptions. Use RDS when a managed relational engine and compatibility meet the workload; evaluate Aurora when its cluster architecture and read scaling fit. Choose DynamoDB for well-defined, key-oriented access patterns at scale, and Redshift for large-scale analytics and reporting. Combine services when operational transactions, high-volume key lookups, and analytics have genuinely different needs, then validate the added integration complexity with end-to-end tests.

• read replica