When it comes to selecting the right database tool on AWS, the options can seem daunting. This guide will help you find your way.
Join the DZone community and get the full member experience. Amazon Web Services (AWS) is the biggest cloud platform in the world, with over 200 features. In this article, we break down 10 AWS services that support at least some SQL syntax, talk about their use cases, and give examples of how to write queries. The table above shows how SQL support varies between the services. A graph database cannot be queried in the same way as a classic relational database, and various subsets of SQL, like PartiQL, have emerged to fit these models. In fact, even within standard SQL, there are many SQL dialects for different companies like Oracle and Microsoft. From relational to graph to ledger to time series, the services below cover almost every conceivable hosted database use case. Let’s examine what each is for and how to write a SQL query against them! Amazon RDS is one of the most basic AWS database services, used mainly for offloading your database management operations to a platform. Therefore, it is used for small or medium enterprises where the data volume is limited, and the functionalities required for company operations are not too complex. Amazon RDS supports database engines such as MySQL, MariaDB, PostgreSQL, Oracle, and Microsoft SQL Server. It comes with workflows to secure your RDS instance using SSH and offers a straightforward cloud console for connecting. RDS is just some nice management options around the core databases so it supports the exact SQL that each database supports. Here would be a subquery that could be run against Postgres, MySQL, or Oracle. Amazon Aurora is an elevated version of Amazon RDS. Large enterprises use this since their data volume and complexity of operations are much higher. It doesn’t support all the same database engines as Amazon RDS, and instead only supports MySQL and PostgreSQL. Aurora scales up and down as the load on your database increases and decreases. Newer providers like PlanetScale also offer this capability with additional schema migration features and lower costs. Amazon Aurora, like RDS, can perform replication. It actually offers about 15 different types of replications, and one replication can be done within milliseconds. On the other hand, RDS can perform only five types of replications, taking more time. Some of the use cases that can depict the strength of Amazon Aurora are enterprise applications, SaaS applications, and web/mobile gaming. Like Amazon Aurora, Amazon Redshift is used by large enterprises. However, Redshift is more complex, can handle more data, and is referred to as a data warehouse. This is because Redshift is built for OLAP (Online Analytical Processing). Furthermore, Redshift can scale up to Petabytes of data and supports up to 60 user-defined databases per cluster. On the other hand, Aurora can only scale to terabytes and support up to 40. Besides this, the security and the maintenance of both the database services are pretty much the same. A few use cases of Amazon Redshift are creating machine models for forecasting operations, optimizing your company’s business intelligence, and increasing developer productivity. Redshift supports some SQL functions and queries which would generally only be necessary with large data warehouse applications. For example, PERCENTILE_CONT computes a linear interpolation to return a percentile. DynamoDB is Amazon’s answer to MongoDB, a NoSQL database that works on JSON documents. These databases rely heavily on nested data and do not enforce any strict schema unless the developer turns that option on.
