AWS: Selecting a Relational SQL Database

Choosing which SQL database to use with Amazon Web Services can be confusing. Which of the many SQL database options is the simplest way?  There are various choices such as serverless, global, RDS, Aurora, and DSQL available among others. When to use which and why?

Answering a question with more questions

It really starts with the workload; do you need multiple servers in a cluster for reliability, high availability, fail-over, to split read and write operations, or multi-region support? 

How much of the database configuration and infrastructure do you want to manage for yourself?

Do you know what your expected traffic load is or what your cost sensitivities are?

Aurora and RDS Console:

Create database

In the RDS console the first choice is “Choose a database creation method” “Standard Create” or “Easy Create“.

“Easy create” provides pre-made opinionated templates for non-production (development / test) and production roles. Most choices may be modified later.

“Standard create” is preferred so that choices are thought through in advance. These options only apply to the console. If using Terraform, Cloudformation, Pulumi or other automations. “Standard” and “Easy” create do not apply.

RDS Options

If considering Oracle, SQL Server, MariaDB or DB2, an RDS instance will be launched. For MySQL or PostgreSQL, additional choices expand to include Aurora, and options like using serveless and autoscaled readers for example. 

Aurora or “Plain RDS”

Aurora makes many choices for you, such as automatically scaling storage, using multiple-AZs. It also makes adding and scaling read-only instances very easy. With RDS instances you will make choices, including sizing and scaling of storage and managing read only instances. Each choice comes with a plus or minus on the cost of the workload. 

Aurora

For most modern AWS workloads, Aurora MySQL or Aurora PostgreSQL are the obvious choice. Aurora offers a high performance database that operates as the chosen engine, MySQL or PostgreSQ:. An aurora cluster with one or two instances can be made available very quickly with minimal choices and good performance.

Provisioned instance types offer a predictable fixed hourly cost, can easily be scaled (with downtime or fast failover) for changing traffic needs, and will fit the bill for most workloads.

Aurora Serverless V2 (V1 is dead) is best for spiky workloads, automatically scales quickly, and charges based on the number of Aurora Compute Units (ACUs) consumed so the cost projection can be tricky. The cost drop during low traffic periods may offer a significant reduction in runtime expense.

Plain RDS

If you really DONT want or need high availability, or database servers in a cluster, then plain RDS is a good choice. Aurora also does not support SQL Server, Oracle, or DB2 at present. Plain RDS does still offer readers and replication for high-availability for MySQL and PostgreSQL (possibly others). It takes a little more setup to maintain by the operator depending on the database product chosen. With plain RDS you will provision the disk size, multiple AZs and a number of other options that Aurora handles for you. Autoscaling of storage is an option. Otherwise, monitoring the free disk on the database is a critical task. Plain RDS may cost less than Aurora, depending on these choices. With RDS choices for single or multiple Availability Zone deployments along with active/standby versus active/active options come into play.

Other Options

All of the other options, global databases, DSQL regional (PostgreSql), limitless (PostgreSql) and many others are safe to rule out until you know you need them.

Conclusion

At Castlerock, our goto for a SQL database is Aurora MySQL or Aurora PostgreSQL with provisioned instances to get started with a new workload. Once we know the properties of the workload, we will assess whether serverless is a good fit, and then can grow from there. Keeping it simple works well for us and our clients.