Selecting Relational Database Engines: MySQL vs. PostgreSQL on AWS

This guide explores the critical decision-making process for selecting a database engine within Amazon RDS and Amazon Aurora, specifically focusing on the trade-offs between MySQL and PostgreSQL as required for the SAA-C03 exam.

Learning Objectives

By the end of this guide, you should be able to:

• Distinguish between the operational features of MySQL and PostgreSQL on AWS.
• Identify the appropriate engine for specific migration scenarios (e.g., Oracle to PostgreSQL).
• Compare Amazon Aurora's MySQL-compatible and PostgreSQL-compatible editions.
• Choose between Amazon RDS and Amazon Aurora editions for a given workload.

Key Terms & Glossary

• Amazon RDS: Relational Database Service; a managed service for six popular database engines.
• Amazon Aurora: A MySQL and PostgreSQL-compatible relational database built for the cloud with a virtualized storage layer.
• InnoDB: The recommended storage engine for MySQL and MariaDB on RDS; the only one supported by Aurora MySQL.
• OLTP (Online Transaction Processing): Databases designed for high-frequency, small transactions (standard for RDS).
• License Included: A licensing model where the cost of the DB engine license is bundled into the AWS hourly rate.

The "Big Idea"

In the AWS ecosystem, choosing between MySQL and PostgreSQL isn't just about syntax—it's about migration compatibility and performance scaling. While both are open-source relational engines, MySQL is often the default for web-scale applications and PostgreSQL is the usual destination for workloads leaving a commercial engine. The decision is rarely about which engine is better in the abstract. It is about which one the existing code, the team's experience and the migration path already point towards.

Key Facts and Comparisons

Hierarchical Outline

1. Start from the migration, not the feature list
   • Coming from Oracle or SQL Server, PostgreSQL usually means less rewriting
   • Coming from MySQL or MariaDB, staying on MySQL is a homogeneous migration and needs no schema conversion
2. Decide between Amazon RDS and Amazon Aurora
   • Aurora separates compute from a shared storage layer that grows automatically
   • Aurora supports more replicas at lower lag; the standard engines cost less per instance-hour
3. Check the features the application actually uses
   • Extensions, data types and procedural language differ between the engines
4. Plan the migration
   • Homogeneous: AWS Database Migration Service alone
   • Heterogeneous: Schema Conversion Tool first, then ongoing replication until cutover

Visual Anchors

• Picture two doors out of a commercial database. The PostgreSQL door is wider, because more of the SQL and procedural code fits through it unchanged.
• Picture Aurora as the same engine with its storage layer removed and replaced by a shared, self-growing one. The dialect the application speaks does not change; what sits underneath does.

Definition-Example Pairs

• Homogeneous migration: source and target run the same engine. Example: on-premises MySQL to Amazon RDS for MySQL, with no schema conversion.
• Heterogeneous migration: source and target engines differ. Example: Oracle to Aurora PostgreSQL-Compatible Edition, which needs schema and procedural code converted first.
• Wire compatibility: an engine accepting an existing driver and dialect. Example: an application pointed at Aurora MySQL connects with its existing MySQL driver.

Worked Examples

Leaving Oracle on a deadline. An application relies heavily on stored procedures. PostgreSQL is chosen because it is the most Oracle-compatible open-source target, the Schema Conversion Tool converts most of the procedural code, and Database Migration Service replicates changes until cutover so the outage is the cutover alone.

A read-heavy catalogue outgrowing its primary. The application is already MySQL and its SQL is straightforward. Aurora MySQL-Compatible Edition keeps the driver and dialect unchanged while adding low-lag replicas to absorb the reads, so no engine change is needed at all.

Checkpoint Questions

1. Which engine is the usual target for a migration away from Oracle, and why?
2. What is the difference between a homogeneous and a heterogeneous migration, and which needs the Schema Conversion Tool?
3. Which storage engine does Aurora MySQL-Compatible Edition support?
4. What does Aurora change about the database, and what does it deliberately leave alone?