AWS RDS vs Aurora vs DynamoDB: Choosing Your Database

What and Why

AWS gives you three first-class database options most teams consider for new services: RDS (managed Postgres or MySQL on EC2), Aurora (a Postgres or MySQL compatible engine with cloud-native storage), and DynamoDB (a fully managed key-value and document store). They cover overlapping ground but optimize for different things.

Choosing wrong is expensive. RDS can buckle under unpredictable load. Aurora costs more per hour but scales storage and replicas elegantly. DynamoDB needs you to design for access patterns up front but rewards you with single-digit-millisecond latency at any scale.

Mental Model

RDS (single AZ):    [ Primary EC2 ] -- EBS volume
RDS (multi AZ):     [ Primary ] --sync--> [ Standby ]   (separate EBS each)

Aurora:             [ Writer ] [ Reader 1..15 ]
                        \       /
                     [ Distributed storage layer, 6 copies / 3 AZs ]

DynamoDB:           [ Request router ] -> partitions -> SSDs
                  (no instances to manage)

RDS is “Postgres on a server, but AWS babysits it.” Aurora rewrites the storage layer so writes go to a shared, replicated log; readers and the writer all read from that log. DynamoDB has no instances at all - you pay for read and write units and storage.

Hands-on Example

Provision RDS Postgres with Terraform:

resource "aws_db_instance" "app" {
  identifier          = "app-db"
  engine              = "postgres"
  instance_class      = "db.t4g.medium"
  allocated_storage   = 100
  storage_type        = "gp3"
  multi_az            = true
  backup_retention_period = 14
}

Aurora is a cluster of compute plus a shared storage:

resource "aws_rds_cluster" "app" {
  cluster_identifier = "app-aurora"
  engine             = "aurora-postgresql"
  master_username    = "admin"
  master_password    = var.password
  backup_retention_period = 14
}
resource "aws_rds_cluster_instance" "writer" {
  cluster_identifier = aws_rds_cluster.app.id
  instance_class     = "db.r6g.large"
  engine             = "aurora-postgresql"
}

DynamoDB needs a partition key and any access patterns:

resource "aws_dynamodb_table" "orders" {
  name         = "orders"
  billing_mode = "PAY_PER_REQUEST"
  hash_key     = "pk"
  range_key    = "sk"
  attribute { name = "pk"; type = "S" }
  attribute { name = "sk"; type = "S" }
}

A query against DynamoDB looks nothing like SQL:

aws dynamodb query --table-name orders \
  --key-condition-expression "pk = :u AND begins_with(sk, :o)" \
  --expression-attribute-values '{":u":{"S":"USER#42"},":o":{"S":"ORDER#"}}'

Common Pitfalls

• Treating Aurora as a drop-in for RDS without re-thinking IO. Aurora’s storage layer optimizes for many small writes; very large transactions can be slower than expected.
• DynamoDB hot partitions. A bad partition key (like status) concentrates load on one partition and you get throttling even though total throughput is low. Choose high-cardinality keys.
• Running RDS in single-AZ for production. Failover takes minutes and you may lose recent writes. Multi-AZ should be the default.
• Underestimating connection cost. Postgres opens a process per connection. Always front RDS and Aurora with RDS Proxy or PgBouncer.
• Querying DynamoDB without an index. A Scan reads every item and bills you for it. Add a GSI for each access pattern instead.

Production Tips

• For most CRUD apps with under a few TB of data and rich queries, Aurora Postgres is the sweet spot - cheap-ish, fast failover, painless read scaling.
• For tiny workloads or strict cost control, RDS on a graviton instance is fine. You will outgrow it before you outgrow Aurora.
• For ultra-high-throughput, predictable access patterns (carts, sessions, IoT telemetry, leaderboards), DynamoDB wins on latency and on operational simplicity.
• Use provisioned capacity with auto scaling in DynamoDB once your traffic shape is known; on-demand is great for spikes but more expensive at steady state.
• Always enable point-in-time recovery and snapshot exports to S3. Cross-region copies belong in your runbook before you need them.

Wrap-up

There is no universally best database here. RDS gives you a familiar engine on a single server. Aurora gives you a cloud-native architecture that scales reads and storage independently. DynamoDB gives you a serverless data plane that rewards careful modeling. Match the database to the access patterns and growth shape of your workload, and revisit the choice as the system matures.