
Design the Access Pattern First, Choose the Database Second
Choosing a database should not begin with SQL versus NoSQL. Start with how your application reads, writes, relates, and scales its data, then choose the AWS database that fits those patterns.
When I started building applications, database selection usually happened very early.
The conversation looked something like this:
Should we use SQL or NoSQL?
Then we picked PostgreSQL, MySQL, or sometimes a NoSQL database and started designing around it.
The problem is that this reverses the decision.
A database is not just a place where an application stores data. It directly affects how the application queries information, handles relationships, scales, maintains consistency, recovers from failures, and eventually costs money to operate.
A better question is:
How will my application actually use its data?
Only after answering that question should we start choosing the database.
Start With the Application, Not the Service
Imagine we are building a deployment platform where developers can create projects and deploy their applications.
Before creating a database table, we could write down a few things the application needs to do:
- Find a user using an email address
- List all projects owned by a user
- Retrieve a project using its ID
- Create a new deployment
- Update deployment status
- Show the latest deployments for a project
- Store deployment events
- Maintain account and billing information
This simple list already tells us more about our database requirements than saying:
"We need a scalable database."
Now we can start asking useful questions.
Does the data have relationships?
Do several records need to change together?
Will we frequently join information?
Are most requests simple lookups using known keys?
Will traffic be predictable or highly variable?
How much consistency does the application require?
These answers begin pointing us toward the right database model.
When Relationships Are Part of the Application
Suppose our platform has:
User | +--- Project | +--- Deployment | +--- DeploymentEventProjects belong to users.
Deployments belong to projects.
Billing records may belong to accounts.
Several operations may also need transactional guarantees.
This is naturally relational data.
Amazon RDS or Amazon Aurora becomes an attractive choice because the application benefits from structured relationships, SQL queries, transactions, constraints, and familiar PostgreSQL or MySQL tooling.
Instead of trying to avoid relationships, we are selecting a database that handles them well.
That sounds obvious, but architecture becomes unnecessarily complicated when we force relational workloads into a database simply because another technology sounds more "cloud native."
Cloud native does not mean using NoSQL everywhere.
It means using the technology that fits the workload.
When the Access Pattern Is the Strongest Signal
Now consider another requirement:
DeploymentID -> Deployment StatusOur application may receive a huge number of requests asking:
What is the current status of deploymentdep_8472?
There may be no joins.
No complex reporting.
No need to traverse several relationships.
The application knows the key and wants the value quickly.
This is where Amazon DynamoDB starts becoming interesting.
Instead of designing tables first and figuring out queries later, DynamoDB encourages us to understand our access patterns early.
For example:
PK: PROJECT#184SK: DEPLOYMENT#20260826-001The important lesson here is not the key format.
It is the thinking behind it.
We already know how the application needs to retrieve its data.
The database design follows that requirement.
The Same Application Can Have Different Data Problems
This is where database architecture becomes interesting.
One application does not necessarily have one type of data problem.
Our deployment platform might eventually use:
Application | +--------------+--------------+ | | v v Amazon Aurora Amazon DynamoDB | | Users / Accounts Deployment Status Projects / Billing Fast Lookups Transactions Event StateThis does not mean every project should immediately deploy several databases.
Actually, I would argue the opposite.
Start simple.
Introduce another database only when a clear workload justifies the additional operational complexity.
Using five databases because AWS offers five database services is not good architecture.
Using two databases because the application has two genuinely different data access patterns can be.
Aurora Serverless Changes Another Part of the Decision
Sometimes the question is not relational versus NoSQL.
The real problem is capacity.
Imagine an internal development platform.
During working hours, developers may create hundreds of deployments.
At night, database activity might become very low.
The application still benefits from PostgreSQL-compatible relational data, but its demand changes throughout the day.
That makes an autoscaling relational option such as Aurora serverless worth evaluating.
Notice what happened here.
We did not choose serverless because "serverless is modern."
We identified a workload characteristic first:
Relational Data +Variable Demand +Need for Managed ScalingThen we evaluated a database configuration that matches it.
That is the pattern I try to follow for infrastructure decisions in general.
A Small Decision Framework
Before choosing an AWS database for a new application, I now try to answer questions like these:
Question | What It May Suggest |
|---|---|
Are relationships and joins important? | Amazon RDS or Amazon Aurora |
Do I need strong transactional behavior? | Relational database |
Are most requests predictable key-based lookups? | Amazon DynamoDB |
Does relational demand change significantly over time? | Aurora serverless |
Am I repeatedly reading the same hot data? | Consider Amazon ElastiCache |
Is the application based heavily on connected relationships? | Consider Amazon Neptune |
Do I actually need another database yet? | Maybe keep the architecture simple |
The last question is probably the most important one.
Don't Choose Based Only on Scale
One misconception I often see is:
"The application may become large, so we should use NoSQL."
Scale alone is not enough information.
A relational database can support very serious workloads.
A NoSQL database can also be designed poorly.
What matters is the combination of:
access patterns + consistency + relationships + latency + scale + operational requirements
For example, if your application needs complicated joins and transactional workflows, replacing them with application-side logic just to use a particular database may actually make the system harder to maintain.
Similarly, repeatedly scanning a NoSQL database because the access pattern was never designed properly defeats much of the reason for choosing it.
Architecture decisions always involve trade-offs.
Your Indexes Are Part of the Application Design
There is another habit worth developing.
Whenever I write an important query, I ask:
How is the database going to find this data?
Consider:
SELECT *FROM deploymentsWHERE project_id = ?ORDER BY created_at DESCLIMIT 20;This looks like an application query.
But it is also an infrastructure decision.
If this becomes one of the most frequently executed operations in the system, its indexing strategy matters.
The same concept applies to DynamoDB.
Your partition keys, sort keys, and secondary indexes should reflect actual application access patterns.
Performance should not become something we think about only after production becomes slow.
Don't Forget the Operational Questions
Database selection should also include the questions developers sometimes leave until the end:
- How will backups work?
- What happens if an Availability Zone has a problem?
- How will database credentials be managed?
- What metrics should be monitored?
- What happens when connections increase?
- How will schema changes be deployed?
- What is our recovery strategy?
- What will this cost when traffic grows?
A database that works perfectly on a developer laptop is only the beginning.
The production database is part of the reliability architecture of the entire application.
Start Simple, Then Specialize
There is a temptation when learning AWS to use as many services as possible.
I have learned that a better architecture often starts with fewer.
If I were building a new application today, my process would look something like this:
Step 1: Write the important access patterns
What information does the application need to create, retrieve, update, and search?
Step 2: Identify the data relationships
Are the entities strongly connected? Are transactions important?
Step 3: Understand traffic characteristics
Are requests predictable, extremely high volume, bursty, or mostly idle?
Step 4: Choose the simplest database that satisfies those requirements
Not the most exciting one.
Not the newest one.
The simplest one that solves the problem well.
Step 5: Measure before adding complexity
CloudWatch metrics, query performance, latency, throughput, and actual usage should guide future architecture changes.
Only then should another specialized data store be introduced if the workload needs it.
The Database Comes After the Question
AWS provides relational, key-value, document, graph, in-memory, time-series, and other purpose-built database options.
That variety is powerful.
But having more choices also means developers need a better way to choose.
Instead of beginning with:
Which AWS database should I use?
Start with:
What does my application need to do with its data?
Write down the reads.
Write down the writes.
Understand the relationships.
Identify the consistency requirements.
Think about traffic and failure scenarios.
Then choose the database.
That small change in thinking can prevent a surprising amount of complexity later.
And sometimes, after going through the entire process, the best architecture decision turns out to be the least exciting one:
keep the database you already have.
What is the first thing you look at when choosing a database for a new application: relationships, access patterns, scalability, cost, or something else?
I'd love to hear how other builders approach this decision.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article