Selecting the Optimal Database Engine: MySQL, PostgreSQL, and AWS RDS Essentials

This guide focuses on the critical decision-making process for selecting a relational database engine within the AWS ecosystem, specifically focusing on the differences between popular open-source options and Amazon Aurora.

Learning Objectives

After studying this guide, you should be able to:

• Differentiate between the primary database engines supported by Amazon RDS (MySQL, PostgreSQL, MariaDB, Oracle, SQL Server, and Aurora).
• Evaluate the licensing models (License Included vs. BYOL) for various database engines.
• Identify the appropriate storage engines for MySQL and MariaDB deployments.
• Determine the best engine choice based on specific migration requirements, such as Oracle compatibility or performance needs.

Key Terms & Glossary

• RDS (Relational Database Service): A managed service that handles routine database tasks like hardware provisioning, setup, patching, and backups.
• Aurora: A MySQL and PostgreSQL-compatible relational database built for the cloud that combines the performance of high-end commercial databases with the simplicity of open source.
• InnoDB: The default and only recommended storage engine for MySQL and MariaDB on Amazon RDS, supporting transactions and automated backups.
• OLTP (Online Transaction Processing): A class of software programs capable of supporting transaction-oriented applications (high volume of quick transactions).
• BYOL (Bring Your Own License): A licensing model where you use your existing software licenses on AWS hardware.

The "Big Idea"

Selecting a database engine is not just about choosing a brand; it is about matching application requirements to engine characteristics. In the AWS SAA-C03 context, this choice often hinges on three factors: Compatibility (can I migrate easily?), Performance (does it handle my IOPS requirements?), and Cost/Licensing (do I pay for the license or bring my own?).

Formula / Concept Box

[!IMPORTANT] IOPS Calculation Tip: Page size affects IOPS. Since MariaDB uses 16 KB pages and PostgreSQL uses 8 KB, writing the same amount of data (e.g., 200MB) requires twice as many IOPS on PostgreSQL as it does on MariaDB.

Hierarchical Outline

• I. Open Source Engines
  • MySQL: Most popular for general web apps; binary compatible with Aurora MySQL.
  • MariaDB: A fork of MySQL; drop-in replacement with similar licensing.
  • PostgreSQL: Known for extensibility; often used for migrating away from Oracle.
• II. Commercial Engines
  • Oracle: Supports both License Included and BYOL models.
  • Microsoft SQL Server: Multiple versions (Express to Enterprise); License Included only.
• III. Amazon Aurora (Cloud Native)
  • Performance: Up to 5x throughput of standard MySQL; 3x of standard PostgreSQL.
  • Features: Serverless options, Backtrack (restore without snapshots), and auto-scaling storage.

Visual Anchors

Database Selection Logic

Aurora vs. Standard RDS Architecture

Definition-Example Pairs

• Drop-in Replacement: Software that can be substituted for another without changing the application code.
  • Example: Switching a local MySQL database to Amazon Aurora MySQL-Compatible edition usually requires only a change to the connection string.
• License Included Model: The cost of the software license is bundled into the hourly instance price.
  • Example: Running Microsoft SQL Server on RDS where AWS handles the licensing fees, simplifying compliance for the user.
• Binary Compatibility: Software that can run identical binary files or tools.
  • Example: Using standard MySQL dump and restore tools directly against an Amazon Aurora instance.

Worked Examples

Scenario 1: The Oracle Migration

Problem: A company wants to move an internal app from an on-premises Oracle DB to AWS while reducing licensing costs. They have heavy use of PL/SQL. Solution:

1. Selection: Choose PostgreSQL.
2. Reasoning: PostgreSQL is advertised as the most Oracle-compatible open-source engine, making the migration of stored procedures and complex queries easier than MySQL.

Scenario 2: High-Performance Web Scale

Problem: A high-traffic social media site requires a relational database that can scale storage automatically and handle instant failover without complex management. Solution:

1. Selection: Amazon Aurora (MySQL compatible).
2. Reasoning: Aurora provides 6-way replication across 3 Availability Zones and automatic storage scaling up to 128 TiB. Its storage layer reduces the overhead of write operations compared to standard RDS MySQL.

Checkpoint Questions

1. Which database engine is recommended for users who want "Oracle-compatible" open-source features?
2. True or False: Amazon RDS for SQL Server supports the Bring Your Own License (BYOL) model.
3. What is the only storage engine Amazon recommends for MySQL deployments in RDS if you want automated backups?
4. Why does MariaDB require fewer IOPS than PostgreSQL to write the same 100MB of data?

▶Click to see answers

1. PostgreSQL.
2. False. Only Oracle supports BYOL on RDS.
3. InnoDB.
4. Page size difference. MariaDB uses a 16 KB page size, while PostgreSQL uses an 8 KB page size; larger pages mean fewer discrete I/O operations for the same volume of data.