
SQL vs NoSQL on AWS (Aurora, RDS or DynamoDB)
A breakdown of how to decide between relational and key-value databases on AWS using a single application as the example. This post walks through the question that settles the choice, the requirements that override it and the service behaviors that only surface after you have committed.
Intro
A database is one of the few architecture decisions that can be expensive to reverse. Pipelines can get replaced and caches get swapped easily, but by the time you know whether the database choice was right, you already have a schema, query layer and production data that must move together.
Consider our working example:
You're a software engineer at REXTECH, a company that operates public EV charging stations across the United States and you've been handed the next version of their platform along with ownership of the data layer. The platform does three jobs:
- Drivers open an app to find charging stations near them and see which connectors are free
- The platform meters and bills each charging session as it happens
- The operations team needs to know which stations are busy, which are failing and what revenue looks like by region
REXTECH runs all this today on a single PostgreSQL database in their own data center. It uses PostGIS, an open-source extension that adds geospatial queries to PostgreSQL, to answer which stations fall within 5 miles of a driver and a trigger writes an audit row every time a session closes.The company is moving to AWS and opening its first European stations next year, which means the data layer has to be chosen rather than inherited.
This article walks through how to make that decision, beginning with a framing that sends many teams to the wrong answer.
SQL vs. NoSQL framing falls short
The first instinct is to treat this as a choice between relational (SQL) and NoSQL, which sounds like a question about the shape of your data, but it rarely is.
On AWS, there are three services that you'll most likely choose from:
- Amazon DynamoDB is a fully managed key-value database where items are stored and retrieved by a key you already hold, with no instances to provision or size at any scale.
- Amazon Aurora is a managed relational database offering PostgreSQL-compatible and MySQL-compatible engines built for AWS, with higher throughput and storage ceilings than the community versions.
- Amazon Relational Database Service (Amazon RDS) is the managed service for running the standard engines directly, covering PostgreSQL, MySQL, MariaDB, SQL Server, Oracle and Db2.
REXTECH's entire platform could be built on DynamoDB but it could just as reasonably be built on Aurora. Both are durable, both are fully managed and scale well past anything a national charging network will require. That similarity is what makes a data model comparison unhelpful, because when two options are genuinely close, the decision tends to falls back to whichever model the team knows better.
The deciding factor is when you have to commit to your queries. A relational database allows you to model the data now and work out the queries later, which is what keeps an unplanned reporting question answerable years after the schema is designed. A key-value database inverts that order, asking you to work out the queries first and rewarding you with scaling characteristics that have no practical ceiling.
Both options will defer something but the key decision to make is which deferral the project can afford.
Step 1. Write down every read operation
This is not an inventory of the data you intend to store but every distinct way the application will retrieve it. For REXTECH, that list comes out roughly as:
- Find stations within a radius of a driver's coordinates
- Get the current status of every connector at one station
- Get one charging session by session ID
- Get a driver's session history, most recent first
- Get the current billing total for an in-progress session
- Average utilization per station, by hour, for the last quarter
- Revenue by region and connector type, month over month
- Which stations reported a fault more than three times last week
Those entries separate into three groups that behave differently.
Entries 2 - 5 each begin from an identifier the application already holds, whether a station ID, session ID or driver ID and each returns a bounded result. These are key-based reads and DynamoDB serves them at request rates of millions of requests per second with no change to the table as traffic grows.
Entries 6 - 8 are different. They don't begin from a single identifier, each one aggregates across the whole dataset along a dimension that is not a primary key and each one is a question the operations team raised recently rather than a requirement that shipped with the platform. A relational database answers all three with ordinary queries against the schema you already have. However, DynamoDB needs a separate secondary index, designed in advance, for every one of them.
Entry 1 sits outside both groups, because finding stations near a driver is a geospatial query that needs PostGIS, that runs only on PostgreSQL, so that read is decided by which engine you pick rather than how you access the data.. The list is the first step in answering the more important question of knowing how confident you are that the list is complete.
Step 2. Test whether list is complete
For entries 2 - 5, confidence is high. Those reads are determined by what the charging hardware does, so they will most likely look the same in three years. For entries 6 - 8, confidence is close to zero, because we anticipate the operations team or finance team asking for another requirement next quarter that wasn't accounted for.
This produces three outcomes encountered by REXTECH .
Where a group of reads is short, key-based and confidently final, DynamoDB is the answer.
For REXTECH that group is entries 2 - 5, the connector status read and the three session reads. Design the partition key and sort key around them and there are no instances to size and no capacity planning, because on-demand capacity mode adjusts throughput as traffic changes.
Where the list cannot be finished, Aurora or RDS is the answer. REXTECH's operations and finance queries are the clearest instance of this. If you cannot finish the list, that in itself tells you queries will arrive which you cannot design for today. A relational schema queried with SQL is well suited to exactly that, since the schema describes the data rather than a specific read path.
Where the list is complete but contains joins or aggregations, Aurora or RDS is again the answer. Entries 6 - 8 are aggregations, and DynamoDB has no built-in aggregation, so each would need precomputed totals maintained as sessions are written, usually through DynamoDB Streams and a Lambda function. However, a relational engine offers all three directly from the tables that already exist.
Most teams cannot finish the list and that is the single most common reason they'll choose relational. What's not accounted for in this execercise is how hard requiremesnts can override this process.
Step 3. Check the requirements that override decision
The read list gives you a starting answer but it's not neccesarily the final one. Three requirements can override it, which REXTECH runs into.
A dependency on a specific engine feature. Finding stations near a driver requires PostGIS and that requirement will decide station search before access patterns enter the conversation, pointing to Aurora PostgreSQL or RDS for PostgreSQL. The same reasoning applies to pgvector for vector search, pg_trgm for fuzzy text matching and to procedural languages and triggers. Check for this first, since it can eliminate options before you evaluate anything else.
An existing database being migrated. REXTECH already runs PostgreSQL with a working schema and a trigger the audit process depends on and between Aurora and RDS there are nine engines to choose from, so the odds are good one of them already speaks what the application speaks. In practice, using what the application already expects, offers less complexity and is more desired by teams than moving to new cleaner design.
Write throughput beyond a single instance. If REXTECH's writes eventually exceed what one Aurora instance can absorb, or if drivers on two continents need to write to the same logical database, relational options exist rather than an automatic move to DynamoDB. Aurora PostgreSQL Limitless Database shards automatically while preserving a single-database experience, and Aurora DSQL is a serverless distributed SQL database, also PostgreSQL-compatible, that spreads writes across Regions with active-active replication and strong consistency. You no longer have to leave SQL behind to scale writes horizontally. Two parts of that need unpacking though involving what serverless actually means across these services and where Aurora DSQL draws its line.
Things that trip teams up
Serverless tends to means two different things. Serverless operations means there are no instances to provision, size or patch, so you receive an endpoint and begin querying, which is how DynamoDB, Aurora DSQL and Amazon Keyspaces work. Serverless capacity means you still create and operate a cluster while its compute and storage scale automatically, which is what Aurora PostgreSQL and Aurora MySQL offer as Aurora Serverless v2. One removes the cluster and the other removes the capacity planning, so if the goal is fewer things to operate rather than a smaller bill, serverless capacity does not neccesserilly achieve it.
Aurora DSQL draws its line at the schema. It supports foreign keys, table relationships, JOIN operations and views, so having a relational schema is not grounds for ruling it out, which is the reason teams most often cite. What decides it is whether the rest of the schema fits a deliberately smaller PostgreSQL surface. Triggers, stored procedures and temporary tables are not supported, each cluster holds a single database and transaction isolation is fixed at Repeatable Read. Two constraints shape application code: a single transaction can modify up to 3,000 rows, so REXTECH's nightly billing batch has to be chunked, and optimistic concurrency control returns a serialization error at commit rather than blocking on a lock, so the application needs retry logic with backoff.
REXTECH's decision
Charging sessions, billing and the operations and finance queries go to Aurora PostgreSQL, supported by four independent reasons pointing the same direction. The read list cannot be completed, the queries require joins and aggregates, an existing PostgreSQL schema is worth preserving and PostGIS handles station search. Connector status goes to DynamoDB, where the list is short, key-based and stable for the service life of the hardware, and where the platform's highest-volume read benefits most from an engine that absorbs traffic growth without intervention.
How you can begin today
Start with the list. Write down every distinct read the application performs, including the ones that feel obvious, then mark each with how confident you are that it will still be there in a couple years. Where a group of reads is short, key-based and complete, design a DynamoDB table around it. Where the list cannot be finished, or where it needs joins and aggregations, choose Aurora or RDS. Then check the result against the three overrides, since an extension dependency, a migration or write volume beyond one instance will override whatever the access patterns are.
One caution if you verify any of this with a coding assistant. Three specifics in this post changed within roughly the last year and a model's training data has a cutoff that service limits do not respect. AWS publishes reusable skills through the Agent Toolkit for AWS for exactly this, including one that routes database questions to current service guidance. Leverage it to confirm the limits your design depends on to guide you in making a decision.
Additional resources
Authored by:
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article