Database Architecture

TL;DR

• Database family choice should follow from actual query/access patterns — not team familiarity or hype (“scales infinitely”).
• DynamoDB scales predictably only when partition keys and secondary indexes are designed up front around known query shapes; it is weak at ad-hoc joins and flexible reporting.
• Aurora is a cost premium over standard RDS in exchange for faster failover, more read replicas, and auto-scaling storage; RDS wins on engine breadth (SQL Server, Oracle) and simpler cost profile.
• Caches (ElastiCache) are not a source of truth — every cache design needs explicit, tested invalidation logic.
• Neptune (graph), OpenSearch (search), and Redshift (OLAP) each solve a fundamentally different access pattern than relational/NoSQL — mismatched use produces exponentially degrading queries as the workload grows.
• Separate OLTP and OLAP workloads onto different engines; running analytics against a production transactional database degrades it for real users.

Core Concepts

Relational: RDS and Aurora

Both are managed relational databases (MySQL, PostgreSQL, MariaDB, and for RDS also SQL Server/Oracle). Aurora replaces the underlying storage engine with a distributed, auto-scaling, cloud-native layer decoupled from compute, which is what enables its faster failover and higher read-replica ceiling.

DynamoDB

A managed key-value/wide-column NoSQL database offering single-digit-millisecond latency at effectively unlimited scale — but only when access patterns are modeled up front. The partition key design and any Global Secondary Indexes (GSIs) must be chosen to match the actual queries the application will run, because DynamoDB has no query planner capable of arbitrary ad-hoc joins. Retrofitting a new access pattern onto an existing table design typically requires a new GSI (with its own cost/capacity implications) or restructuring the table entirely — far more expensive than getting the design right initially.

ElastiCache (Redis / Memcached)

An in-memory cache layer sitting in front of a primary datastore, used for sub-millisecond lookups on hot data: session state, computed recommendation results, rate-limit counters. Redis additionally supports data structures (sorted sets, lists, pub/sub) and optional persistence; Memcached is purely an in-memory key-value cache with simpler multi-threaded scaling. The architectural discipline required: the cache must never become the system of record. Every write path needs explicit invalidation or write-through logic, or the cache silently serves stale data.

Neptune (graph)

Purpose-built for graph-shaped data — social graphs, fraud-detection networks, recommendation engines driven by relationship traversal. Supports Gremlin and openCypher (property graph) and SPARQL (RDF). The reason relational modeling breaks down here: a multi-hop query (“friends of friends of friends”) requires a self-join per hop in a relational model, and the cost of each additional hop compounds. A graph engine stores and traverses adjacency natively, making multi-hop traversal efficient regardless of hop count.

DocumentDB

A MongoDB-API-compatible managed document database. Its primary use case is migrating an existing MongoDB-modeled application to a managed AWS service without a data-model rewrite — less compelling as a first choice on a greenfield design where the team has full freedom to pick the access pattern from scratch.

OpenSearch

A managed search and analytics engine built on an inverted-index model — purpose-built for full-text search, faceted filtering, and log/metrics analytics. Standard B-tree indexes used by relational engines cannot efficiently serve substring or full-text search (LIKE '%term%' forces a full scan), and that degrades badly as data volume grows. OpenSearch’s inverted index is designed specifically for this query shape.

Redshift (data warehouse)

Columnar storage engine built for large aggregate queries across large datasets — analytics, BI dashboards, reporting. Not designed for low-latency single-row transactional lookups. OLTP (transactional) and OLAP (analytical) workloads have fundamentally different access patterns and belong on different engines; forcing either job onto the wrong engine degrades it (running heavy aggregation against a transactional RDS instance competes for the same resources serving live application traffic).

Aurora vs RDS

Database Family Comparison

Command / Configuration Reference

# Create an Aurora cluster (MySQL-compatible)
aws rds create-db-cluster --db-cluster-identifier my-aurora-cluster --engine aurora-mysql --master-username admin --master-user-password ****

# Add an Aurora read replica
aws rds create-db-instance --db-instance-identifier my-replica --db-cluster-identifier my-aurora-cluster --engine aurora-mysql --db-instance-class db.r6g.large

# Create a DynamoDB table with a GSI (access pattern modeled up front)
aws dynamodb create-table --table-name Orders --attribute-definitions AttributeName=PK,AttributeType=S AttributeName=GSI1PK,AttributeType=S --key-schema AttributeName=PK,KeyType=HASH --global-secondary-indexes '[{"IndexName":"GSI1","KeySchema":[{"AttributeName":"GSI1PK","KeyType":"HASH"}],"Projection":{"ProjectionType":"ALL"}}]' --billing-mode PAY_PER_REQUEST

# Create a Redis (cluster-mode) ElastiCache replication group
aws elasticache create-replication-group --replication-group-id session-cache --replication-group-description "session cache" --cache-node-type cache.r6g.large --engine redis --num-node-groups 1 --replicas-per-node-group 1

# Create an OpenSearch domain
aws opensearch create-domain --domain-name search-logs --engine-version OpenSearch_2.11 --cluster-config InstanceType=r6g.large.search,InstanceCount=3

# Create a Redshift cluster
aws redshift create-cluster --cluster-identifier analytics-wh --node-type ra3.xlplus --number-of-nodes 2 --master-username admin --master-user-password ****

Common Pitfalls

• Pitfall: A team defaults every new service to DynamoDB regardless of query shape. Why: DynamoDB’s performance guarantees depend on access-pattern modeling done up front; it has no engine for arbitrary joins or ad-hoc reporting. Fix: Model partition keys and GSIs against actual known queries before committing, and pick a relational engine or warehouse for join-heavy/reporting workloads.
• Pitfall: ElastiCache is treated as a source of truth. Why: Without rigorous invalidation logic, the cache and the primary datastore diverge, producing stale-data bugs. Fix: Design explicit write-through or invalidation logic for every write path that touches cached data; treat cache loss as a normal, recoverable event.
• Pitfall: Full-text search implemented as LIKE '%term%' against a relational database. Why: B-tree indexes can’t serve substring search efficiently, forcing full scans that degrade as data grows. Fix: Use OpenSearch (or a dedicated search engine) for full-text/faceted search requirements.
• Pitfall: Analytical queries run directly against a production OLTP database. Why: OLTP and OLAP workloads compete for the same compute/IO resources but have opposing access patterns (many small transactions vs. large aggregations). Fix: Replicate or ETL data into Redshift (or a read replica dedicated to reporting) rather than querying the primary transactional instance.
• Pitfall: Modeling deeply relational “friends of friends” queries in a relational database. Why: Multi-hop traversal requires a self-join per hop, and cost compounds with each additional hop. Fix: Use Neptune for genuinely graph-shaped, relationship-heavy data models.

Interview Questions

• “A team wants to move an e-commerce product catalog with complex filtering and full-text search from RDS to ‘something faster.’ What’s recommended, and why?” — tests whether OpenSearch is reached for the right reason, not as a trendy swap.
• “Design the data layer for a social app needing friend recommendations based on mutual connections three hops deep.” — tests whether Neptune is understood as solving a fundamentally different problem than a relational join.
• “Walk through the access-pattern design process used before creating a DynamoDB table for a new service.” — tests whether DynamoDB modeling is treated as an upfront design discipline, not an afterthought.
• “When would Aurora be worth the cost premium over standard RDS?” — tests understanding of the actual technical trade-off, not just brand preference.
• “How should OLTP and OLAP workloads be separated in an AWS architecture?” — tests whether the candidate recognizes resource contention between transactional and analytical access patterns.