Work / Database selection
Database selection, June 2025
The right AWS database for each workload, with no rewrite
A client ran Aurora Serverless v1 at maximum capacity 24 hours a day and paid more than Aurora Provisioned would cost. The application code depended on Aurora-specific features, so the client could not switch. We built a connection layer that makes the database type a configuration setting.
The situation
The client's Aurora Serverless v1 cluster ran at its maximum capacity all day. A provisioned cluster of the same size costs less, but the application was written against Aurora-specific features and a change of database type meant a rewrite. We have found the same pattern in four forms.
Serverless for a steady load
The team chose Aurora Serverless for a quick start in development. The traffic is predictable, and the cluster now costs 3 times what a provisioned cluster would.
Over-provisioned RDS
The team bought a db.r6g.2xlarge instance as a reserve. Utilization is 15 %, and a smaller instance cannot be tested without risk.
RDS Proxy without its benefit
The team added RDS Proxy for Lambda, but the application sets session variables in every connection. Each connection is pinned to one database connection, so the proxy pools nothing.
The wrong engine
The team chose MySQL because they knew it. The workload is read-heavy and needs Aurora read replicas, which the team cannot add without changes.
The connection layer
For every client we add a connection layer between the application and the database. The layer owns pooling, retries, failover and read and write routing. The application holds no session state in a connection. A change from RDS to Aurora, or from provisioned to serverless, is then a configuration change and not a code change.
Lambda functions
RDS Proxy connection pool, no session stateRDS MySQL steady load
Aurora Provisioned high availability
Aurora Serverless variable traffic
A database change is a configuration change
We document the trade-offs of each database option and prepare the migration path to each one. When the requirements change, the client switches without a rewrite.
Five rules we apply
We set up the same five rules for every client, so the database can be changed from the first day.
| Rule | What we do | Effect |
|---|---|---|
| No session state in connections | We remove SET statements, temporary tables and user-defined variables from the code. | RDS Proxy reuses connections without pinning. |
| Endpoints from the environment | Database endpoints come from environment variables with one naming standard. | A change from RDS to Aurora is a deployment configuration change, with no code change. |
| Read replica ready | We structure queries so that read operations can be routed to a replica, even before a replica exists. | Reads scale without a refactor. |
| Pool size per environment | Development, staging and production each have their own pool size and can each use a different database type. | Aurora Serverless in development, Aurora Provisioned in production. |
| Performance baseline before a migration | We record query performance metrics before any migration. | The new database is shown to perform equally or better before cutover is final. |
RDS Proxy and connection pinning
RDS Proxy pools connections between an application and the database. When a client sets session state on a connection, the proxy must pin that client to one database connection, and the pool gives no benefit. The cause is in the application code, so we trace each pinned connection back to the statement that caused it.
SET @variable = valueSET SESSION timezoneCREATE TEMPORARY TABLEPREPARE statementBefore: pinned connections
95 % of connections were pinned. A Lambda cold start took 800 ms. The connection limit was reached at peak traffic, for years.
After: pooled connections
5 % of connections are pinned. A Lambda cold start takes 50 ms, 16 times shorter. There is no downtime at peak traffic.
Three migrations and their figures
With the connection layer in place, each migration below was a configuration change and a cutover window.
| From | To | Reason | Saving, USD per month | Migration time, h |
|---|---|---|---|---|
| Aurora Serverless v1 | Aurora Provisioned | The traffic was predictable. Serverless cost 2.5 times more. | 3,200 | 2 |
RDS db.r6g.2xlarge | RDS db.r6g.large and one read replica | 80 % of the load was reads. Routing reads to the replica reduced the load on the primary. | 1,800 | 4 |
| Aurora Provisioned | Aurora Serverless v2 | The development and staging environments were idle 70 % of the time. | 2,400 | 1 |
Protection of production data
A DELETE statement without a WHERE clause removes a table's data in seconds. AWS offers more than one way to recover, and each has a cost. We select the level of protection for each environment, so that critical production data has the most options and development data has the least cost.
| Aurora recovery option | What it does | Window | Engines | Downtime |
|---|---|---|---|---|
| Backtrack | Rewinds the cluster in place to a point in time, without a restore from backup. Recovery takes seconds. | 72 h | Aurora MySQL | None |
| Point-in-time recovery | Restores a new cluster from any point within the backup retention period. The original cluster is not changed. | up to 35 days | Aurora MySQL and Aurora PostgreSQL | None, new cluster |
Protection level by environment
What the client received
- A connection layer with pooling, retries, failover and read and write routing, and application code with no session state.
- Database endpoints and pool sizes set per environment, so each environment can run a different database type.
- A performance baseline recorded before each migration and compared after it.
- RDS Proxy with 5 % pinned connections, and Lambda cold starts of 50 ms.
- A documented migration path to RDS, Aurora Provisioned and Aurora Serverless, and a recovery level for each environment.
The right database for each of your workloads
We review your current databases and their utilization, apply the five rules to the application, and migrate each workload to the database that fits its load. The application code does not change.
Talk to an engineer