Search for a command to run...
The variety is astounding - MySQL, MariaDB, PostgreSQL, Oracle, and SQL Server, each with its own set of strengths and functionalities.
Hi there 👋
I'm an Indonesian who loves coffee and dogs!! Also, code sometimes.
Currently studying at Kyoto College of Graduate Studies for Informatics. 🏫
Work as Full-Stack Engineer
No comments yet. Be the first to comment.
Or: Why we stopped trying to build software for everyone, and built Lensiro just for optical shops. If you've ever walked into an optical shop in Indonesia to buy a pair of glasses, you probably didn'
How event-sourced ledger architecture eliminated stock discrepancies in our optical retail app
ホコリをかぶった12台のMacBookから、今では本番LLMトラフィックの4分の1を担う200ノードのAppleシリコンファームへ
From twelve dusty MacBooks to a 200‑node Apple‑silicon farm that now powers our live LLM traffic
Discover how integrating GitHub with Snowflake empowers seamless version control and collaborative workflows for managing your UDFs and procedures.
As an aspiring AWS Certified Database Specialist, I've spent a considerable amount of time diving into the extensive world of AWS Relational Database Services (RDS). The certification demands a deep understanding of AWS RDS, encompassing the various database engines it supports, their distinct features, capabilities, and how they can be optimally used to meet specific application needs.
Yet, as I ventured further into my studies, I quickly realized that grasping the full range of offerings across the different flavors of RDS was no small feat. The variety is astounding - MySQL, MariaDB, PostgreSQL, Oracle, and SQL Server, each with its own set of strengths and functionalities. It became apparent that a comprehensive comparison of these systems, highlighting what each one can do, would be an invaluable resource not just for me, but for anyone on a similar path.
This sparked the idea to create this blog post, a concentrated effort to demystify the capabilities of the different RDS systems. The aim was to dissect the key aspects of each RDS flavor, focusing on categories like Backup, Storage, Security, Read Replica, Monitoring, and others, and present a clear, concise, and useful comparison that could serve as a go-to guide for AWS RDS.
| MySQL | MariaDB | PostgreSQL | Oracle | SQL Server | |
| Parameter Group (PG) | ✓ | ✓ | ✓ | ✓ | ✓ |
| Option Group (OG) | ✓ | ✓ | ✓ | ✓ | ✓ |
The above table gives a quick comparison of the AWS RDS offerings, indicating whether they support Parameter Group (PG) and Option Group (OG).
| MySQL | MariaDB | PostgreSQL | Oracle | SQL Server | Reference link | ||
| Automatic Backup | ○ | ○ | ○ | ○ | ○ | ||
| Manual Backup (Snapshots) | ○ | ○ | ○ | ○ | ○ | ||
| Multi-AZ | ○ | ○ | ○ | ○ | ○ | ||
| Database Mirroring and Always on | × | × | × | × | ○ |
The above table showcases the high availability options supported by each RDS engine.
All systems in RDS support automatic backup, manual backup (Snapshots) and Multi-AZ for additional availability.
Amazon RDS supports Multi-AZ deployments for Microsoft SQL Server by using either SQL Server Database Mirroring (DBM) or Always On Availability Groups (AGs).
When setting up SQL Server Multi-AZ in RDS, it automatically configures all databases on the instance to use DBM or AGs. Amazon RDS manages the primary, the witness, and the secondary DB instance for you. Because the configuration is automatic, RDS selects DBM or Always On AGs based on the version of SQL Server that you deploy.
Refer to the following official documentation for more details:
| MySQL | MariaDB | PostgreSQL | Oracle | SQL Server | |
| Windows Authentication | × | × | × | × | ✓ |
| Encryption in Transit | ALTER REQUIRE SSL | ALTER REQUIRE SSL | Set rds.force_ssl to 1 in PG | Add SSL in OG | Set rds.force_ssl to 1 in PG |
| Encryption at Rest | ✓ | ✓ | ✓ | ✓ | ✓ |
| Transparent Data Encryption (TDE) | × | × | × | Set TDE Option in OG | Set TRANSPARENT_DATA_ENCRYPTION in OG |
Both MySQL and MariaDB support the use of SSL/TLS connections for specific user accounts. For example, you can use one of the following statements:
ALTER USER 'encrypted_user'@'%' REQUIRE SSL;
Users can then connect over SSL with the following commands:
mysql -h mysql–instance1.123456789012.us-east-1.rds.amazonaws.com --ssl-ca=global-bundle.pem --ssl-mode=VERIFY_IDENTITY -P 3306 -u myadmin -p
mysql -h mysql–instance1.123456789012.us-east-1.rds.amazonaws.com --ssl-ca=global-bundle.pem --ssl-verify-server-cert -P 3306 -u myadmin -p
mysql -h mysql–instance1.123456789012.us-east-1.rds.amazonaws.com --ssl-ca=global-bundle.pem --ssl-mode=REQUIRED -P 3306 -u myadmin -p
Refer to the official documentation for more details:
For PostgreSQL and SQL Server, you can set rds.force_ssl to 1 in the parameter group to require SSL. By default, this setting is off (0) in SQL Server and PostgreSQL versions below 15. It's on (1) in PostgreSQL version 15 and above.
Refer to the official documentation for more details:
To enable SSL encryption for an Oracle DB instance, add the Oracle SSL option to the option group associated with the DB instance.
Refer to the official documentation for more details:
Amazon RDS supports TDE to encrypt stored data on your DB instances running Microsoft SQL Server or Oracle. TDE automatically encrypts data before it is written to storage and automatically decrypts data when it is read from storage.
Refer to the official documentation for more details:
| MySQL | MariaDB | PostgreSQL | Oracle | SQL Server | |
| Read Replica (RR) | ✓ | ✓ | ✓ | ✓ | ✓ |
| Second Tier RR | ✓ | × | × | × | × |
| Cross Region RR | ✓ | ✓ | ✓ | ✓ | ✓ |
| Enable Write RR | Set read_only to 0 in PG | Set read_only to 0 in PG | × | × | × |
| External Database RR | ✓ | ✓ | × | × | × |
| Automated Backup on RR | ✓ | ✓○ | × | ✓ | × |
| Manual Snapshot on RR | ✓ | ✓ | ✓ | × | × |
By creating a second-tier Read Replica, you can potentially distribute some of the replication load from the master database instance to a first-tier Read Replica.
AWS RDS allows the creation of 5 first-tier read replicas. Each read replica can create another 5 replicas. Thus, a total of 30 replicas can exist with one master using this feature. However, note that second-tier read replicas may experience higher replication lag.
Refer to this article for more details.
AWS RDS can create cross-region read replicas on any system. Notably, SQL Server began supporting cross-region read replicas recently.
Refer to this document for more details.
To enable write operations on read replicas in MySQL and MariaDB, set the read_only parameter to false for the DB parameter group associated with your DB instance. Other Amazon RDS engines, such as Amazon Aurora, do not permit the modification of the read_only parameter.
Refer to this article for more details.
Replication can be set up between an RDS for MySQL or MariaDB DB instance and a MySQL or MariaDB instance that is external to Amazon RDS using binary log file replication.
Refer to this document for more details.
Automatic backups and manual snapshots are supported on RDS for MySQL, RDS for Oracle or RDS for MariaDB read replicas.
For RDS for PostgreSQL, you can create a manual snapshot of read replicas. However, automated backups for read replicas are only supported for RDS for PostgreSQL 14.1 and higher versions. If you're using RDS for PostgreSQL versions earlier than 14.1 and want a backup, create a snapshot from a read replica.
Refer to this document for more details.
| MySQL | MariaDB | PostgreSQL | Oracle | SQL Server | |
| Common Metrics | ✓ | ✓ | ✓ | ✓ | ✓ |
| Audit Log | Enable MariaDB Audit Plugin on OG | Enable MariaDB Audit Plugin on OG | with pgaudit extension | ✓ | ✓ |
| AWS Trusted Advisor | ✓ | ✓ | ✓ | ✓ | ✓ |
| RDS Event Notifications | ✓ | ✓ | ✓ | ✓ | ✓ |
| Enhanced Monitoring | ✓ | ✓ | ✓ | ✓ | ✓ |
| Performance Insight | ✓ | ✓ | ✓ | ✓ | ✓ |
| RDS Recommendations | ✓ | ✓ | ✓ | ✓ | ✓ |
| CloudTrail | ✓ | ✓ | ✓ | ✓ | ✓ |
| CloudWatch Application Insight | × | × | × | × | ✓ |
| Set Log Retention | set with stored procedures | × | log_retantion_period on PG | × | × |
Check the available Cloudwatch Metrics here.
Notes on Enhanced Monitoring:
Currently, Physical Devices graphs are not available for Microsoft SQL Server DB instances.
Currently, viewing OS metrics for a Multi-AZ standby replica is not supported for MariaDB or Microsoft SQL Server DB instances.
RDS Event Notifications resources include:
DB instance
DB snapshot
DB parameter group
DB security group
RDS Proxy
Custom engine version
Amazon RDS provides automated recommendations for database resources such as DB instances, read replicas, and DB parameter groups. These recommendations offer best practice guidance by analyzing DB instance configuration, usage, and performance data.
From the Trusted Advisor dashboard, you can review the following cost optimization, security, fault tolerance, and performance improvement checks:
Amazon RDS Idle DB Instances
Amazon RDS Security Group Access Risk
Amazon RDS Backups
Amazon RDS Multi-AZ
When you add your applications to Amazon CloudWatch Application Insights, it scans the resources in the applications and recommends and configures metrics and logs on CloudWatch for application components. Example application components include SQL Server backend databases and Microsoft IIS/Web tiers.
Watch this video for more information.
We have examined various features of popular relational database services (RDBS) including MySQL, MariaDB, PostgreSQL, Oracle, and SQL Server within Amazon's RDS framework. These databases each possess unique strengths and offer different capabilities that can be leveraged depending on the specific requirements of your application. With careful consideration of these features, you can select the RDBS that best fits your needs, thus maximizing performance, cost-efficiency, and overall productivity.
Remember that, as cloud technologies and AWS services continue to evolve, the features and capabilities of these RDBS could change over time. Hence, it is crucial to keep up-to-date with the latest AWS documentation, updates, and best practice guidelines to ensure the optimal use of the selected RDBS.
This document is intended as a general guide to the features of relational database services 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 :)