Choosing the Right AWS Database

AWS gives you more than a dozen managed database services, and picking the wrong one is one of the most expensive mistakes you can make in a cloud project. The good news is that the choice is almost always driven by your workload (what your data looks like and how you read and write it), not by personal preference. This page gives you a simple decision framework so you can match the workload to the service, and it calls out the two classic mistakes beginners make.

The core idea: use the right tool for the job

For decades the default answer was “put everything in one relational database.” On AWS that habit gets expensive and slow. The modern approach is purpose-built databases: you pick a database engine that is designed for each access pattern. A relational database (data organized in tables with rows and columns, queried with SQL) is great for transactions but terrible for caching. A cache is fast but cannot run analytical reports. A data warehouse crunches huge reports but is too slow for live app traffic.

You do not have to use many databases on day one. But you should understand why one type fits and another does not, so you do not paint yourself into a corner.

The two most common and most painful mistakes: (1) bolting analytics and big reporting queries onto a transactional RDS database, which competes with live traffic and kills app performance, and (2) modeling relational, join-heavy data in DynamoDB, which has no joins and forces awkward, rigid access patterns. Avoid both.

Decision framework by workload

Start by describing your workload in one sentence, then match it to a category below.

Workload	Best service	Why
Transactional app data, SQL, joins, strong consistency	Amazon RDS (or Aurora)	Full SQL, ACID transactions, familiar engines (PostgreSQL, MySQL)
Same as above, but wanting more performance and auto-scaling storage	Amazon Aurora	MySQL/PostgreSQL-compatible, faster, storage scales automatically
Massive key-value or document data, single-digit-millisecond reads at any scale	Amazon DynamoDB	Serverless NoSQL, virtually unlimited scale, predictable latency
Speeding up reads / reducing database load	Amazon ElastiCache	In-memory cache (Redis or Memcached), microsecond responses
Analytics, dashboards, reporting over large datasets	Amazon Redshift	Columnar data warehouse built for big aggregate queries
Specialized needs (graph, time-series, ledger)	Purpose-built (Neptune, Timestream, QLDB)	Engine matched to a specific data shape

ACID means Atomicity, Consistency, Isolation, Durability — the guarantees that make a transaction (like a bank transfer) safe.

Relational / transactional: RDS and Aurora

When to use: your data fits in tables, you need joins, foreign keys, and transactions — for example an e-commerce order system, a user account system, or anything with structured relationships. RDS (Relational Database Service) runs standard engines like PostgreSQL, MySQL, MariaDB, Oracle, and SQL Server as a managed service, so AWS handles patching, backups, and failover.

When NOT to use: do not run heavy analytical reports against it, and do not try to store hundreds of millions of loosely structured records that you query by a single key — those are jobs for Redshift and DynamoDB respectively.

Aurora is AWS’s own engine that is wire-compatible with MySQL and PostgreSQL but performs better and grows storage automatically up to 128 TB. Choose Aurora when you have outgrown vanilla RDS or want serverless scaling.

Console steps to create an RDS database:

Open the AWS Management Console and go to RDS.
Click Create database.
Choose Standard create, then pick an engine (e.g. PostgreSQL).
Pick a template (Production, Dev/Test, or Free tier).
Set the DB instance identifier, master username, and password.
Choose an instance class (e.g. db.t3.micro) and storage.
Pick the VPC, subnet group, and security group, then click Create database.

Equivalent AWS CLI command:

aws rds create-db-instance \
  --db-instance-identifier my-app-db \
  --db-instance-class db.t3.micro \
  --engine postgres \
  --allocated-storage 20 \
  --master-username appadmin \
  --master-user-password 'ChangeMe123!' \
  --vpc-security-group-ids sg-0a1b2c3d \
  --db-subnet-group-name my-db-subnets

Output:

{
    "DBInstance": {
        "DBInstanceIdentifier": "my-app-db",
        "DBInstanceClass": "db.t3.micro",
        "Engine": "postgres",
        "DBInstanceStatus": "creating",
        "AllocatedStorage": 20
    }
}

Cost note: a db.t3.micro PostgreSQL instance runs roughly 12-15 USD per month in us-east-1. Aurora Serverless v2 instead bills per ACU (Aurora Capacity Unit) and can scale to near zero for spiky dev workloads.

Key-value at scale: DynamoDB

When to use: you have huge throughput, simple access by a key (like a user ID or session ID), and you want a serverless database with no servers to manage — for example a shopping cart, a gaming leaderboard, an IoT event store, or session storage. DynamoDB returns single-digit-millisecond responses no matter how big the table gets.

When NOT to use: when your queries need joins across many tables, ad-hoc filtering on arbitrary columns, or complex relationships. DynamoDB has no joins. You must design your table around your access patterns up front, which is painful if those patterns keep changing.

Create a table with the CLI:

aws dynamodb create-table \
  --table-name Sessions \
  --attribute-definitions AttributeName=SessionId,AttributeType=S \
  --key-schema AttributeName=SessionId,KeyType=HASH \
  --billing-mode PAY_PER_REQUEST

Output:

{
    "TableDescription": {
        "TableName": "Sessions",
        "TableStatus": "CREATING",
        "BillingModeSummary": { "BillingMode": "PAY_PER_REQUEST" }
    }
}

PAY_PER_REQUEST (on-demand) means you pay per read/write with no capacity to plan — ideal for unpredictable traffic.

Caching: ElastiCache

When to use: your database is doing the same expensive read over and over, or you need microsecond latency for sessions, leaderboards, or rate limiting. ElastiCache runs Redis or Memcached in memory in front of your database. When NOT to use: as your only source of truth — a cache is volatile and can lose data, so it sits in front of a durable database, never replaces one.

Analytics / warehouse: Redshift

When to use: you need to run big aggregate queries (sums, group-bys, trends) over millions or billions of rows for dashboards and business reporting. Redshift is a columnar data warehouse: it stores data by column so analytical scans are fast and cheap. When NOT to use: for live application traffic with many small reads and writes — that is what RDS and DynamoDB are for. The correct pattern is to keep transactions in RDS/Aurora and copy data into Redshift for analysis, keeping the two workloads apart.

Best practices

Describe the workload first, then pick the service — never the other way around.
Keep transactional and analytical workloads on separate databases so reporting never slows down your live app.
Reach for DynamoDB only when your access patterns are known and key-based; if you need joins, use RDS or Aurora.
Put ElastiCache in front of a durable database, not in place of one.
Start with the smallest instance and on-demand/serverless billing, then scale up once you measure real usage.
Use AWS Database Migration Service when moving an existing database into the right AWS service.
Revisit the choice as you grow — it is normal to use several purpose-built databases in one application.