Last week, I created a blog post describing the differences between databases in AWS RDS. Now, it seems fitting to elucidate the differences between Aurora MySQL and PostgreSQL.
Breakdown
Security
| MySQL | PostgreSQL |
| Kerberos Auth | ✓ | ✓ |
Aurora PostgreSQL has supported Kerberos Auth for a long time. As for MySQL, it appears to have been recently included, as detailed in Using Kerberos authentication for Aurora MySQL. Unfortunately, I could not ascertain the exact date this feature was added.
Monitoring
| MySQL | PostgreSQL |
| Performance Insights | ✓ | ✓ |
| Enhanced Monitoring | ✓ | ✓ |
| Data Activity Stream (Sync) | × | ✓ |
| Data Activity Stream (Async) | ✓ | ✓ |
| Advanced Auditing | ✓ | × |
Much like AWS RDS, both Aurora's MySQL and PostgreSQL support Performance Insights and Enhanced Monitoring features.
Both can also execute Data Activity Streams, essentially logs that record database activity. Examples of such activities include selecting commands against a database, connecting logs, and configuration changes.
However, only PostgreSQL supports Synchronous mode.
Furthermore, there is an Advanced Auditing feature for Aurora MySQL. This feature facilitates the collection of database activity logs and operates with high performance.
Availability
| MySQL | PostgreSQL |
| Backtrack | ✓ | × |
| Fault Injection Queries | ✓ | ✓ |
| Fast DDL (Instant DDL) | ✓ | × |
| Parallel Query | ✓ | × |
| Replica | ✓ | ✓ |
| Cross-Region Replica | ✓ | × |
| Multi-Master Cluster | ✓ | × |
Backtracking "rewinds" the DB cluster to the time you specify. Simple as it may sound, it is a powerful feature. Traditionally (a.k.a AWS RDS MySQL) to retrieve lost data we need to start the instance from backups and get data from there. Unfortunately, it only supports MySQL at the time of writing.
Fault Injection is a way to "sabotage" your instance. Why do we do this? Mainly test the failover capability of our cluster. It supports both Aurora's MySQL and PostgreSQL.
Fast DDL or Instant DDL depending on Aurora MySQL versions, is DDL on steroids. Creating, Altering etc can be done faster with this feature. It supports only Aurora MySQL.
The parallel Query feature is a way to improve I/O. It supports Aurora Mysql
Aurora MySQL supports normal replica, but MySQL support normal replica, cross-region replica and multi-Master Cluster (Replica that can accept write request)
Others
| MySQL | PostgreSQL |
| Aurora Serverless v1 | ✓ | ✓ |
| Aurora Serverless v2 | ✓ | × |
| Global Database | ✓ | ✓ |
| Aurora Lab Mode | ✓ | × |
| Babelfish | × | ✓ |
Aurora Serverless like Aurora we can use without managing instances, v2 have more features that v1 doesn't have like AWS Identity and Access Management (IAM) database authentication, and Performance Insights. While we should use v2 if we can it doesn't support Aurora PostgreSQL at the of writing.
Aurora Global Database is a feature that allows replication to cluster in cross-region and it supports MySQL and PostgreSQL.
Aurora lab mode is used to enable Aurora features that are available in the current Aurora database version but are not enabled by default. Only supports Aurora Mysql.
Babelfish for Aurora PostgreSQL is a new capability for Amazon Aurora PostgreSQL-Compatible Edition that enables Aurora to understand commands from applications written for Microsoft SQL Server.
Conclusion
In conclusion, while both Aurora MySQL and PostgreSQL offer a multitude of robust features, there are distinct differences that could influence your choice between the two. When selecting a database, you must carefully consider the unique requirements and constraints of your use case. Features such as Backtracking, Fast DDL, and Advanced Auditing can be pivotal for certain applications and are thus only available for Aurora MySQL. Conversely, the synchronous mode in Data Activity Stream and Babelfish are exclusive to PostgreSQL. Therefore, understanding these differences can guide you in making a more informed choice, ensuring that you leverage the best of what AWS has to offer.
Disclaimer
This document is intended as a general guide to the features of Amazon Aurora within AWS RDS as of the time of writing in July 2023. While every effort has been made to ensure accuracy, the rapidly evolving nature of cloud services means that some information may become outdated or inaccurate over time.
The document does not constitute professional advice, and decisions should not be made solely based on this content. It is recommended that readers refer to the latest official AWS documentation, or consult with an AWS certified professional or the AWS support team for the most accurate and up-to-date information.
Feel free to correct me if I made any mistakes :)
