Amazon RDS and Relational Database Selection – Complete Guide for AWS Data Engineer Associate
Why Is Amazon RDS and Relational Database Selection Important?
Amazon Relational Database Service (RDS) is one of the foundational managed database services on AWS and plays a central role in the AWS Data Engineer Associate exam. Understanding how to select the right relational database engine, configure it for performance, and integrate it into data pipelines is critical. As a data engineer, you will frequently encounter scenarios where structured, transactional data must be stored, queried, and transformed — and RDS is often the go-to solution. Selecting the wrong engine or configuration can lead to poor performance, excessive cost, or operational headaches. The exam tests your ability to make informed decisions about when and how to use RDS versus other AWS database services.
What Is Amazon RDS?
Amazon RDS is a fully managed relational database service that simplifies the setup, operation, and scaling of relational databases in the cloud. AWS handles routine database tasks such as provisioning, patching, backups, recovery, and scaling, allowing data engineers to focus on application logic and data pipeline design.
Amazon RDS supports the following database engines:
• Amazon Aurora (MySQL-compatible and PostgreSQL-compatible)
• MySQL
• PostgreSQL
• MariaDB
• Oracle Database
• Microsoft SQL Server
Each engine has distinct characteristics, licensing models, performance profiles, and feature sets that influence selection.
How Does Amazon RDS Work?
When you create an RDS instance, AWS provisions the underlying compute, storage, and networking infrastructure. Here is how the key components work:
1. DB Instances:
An RDS DB instance is an isolated database environment running in the cloud. You choose an instance class (e.g., db.m5.large, db.r6g.xlarge) that determines CPU, memory, and networking capacity. Instance classes are categorized into general purpose, memory-optimized, and burstable performance tiers.
2. Storage:
RDS uses Amazon EBS (Elastic Block Store) for database storage. Three storage types are available:
• General Purpose SSD (gp2/gp3): Balanced price-performance for most workloads.
• Provisioned IOPS SSD (io1/io2): Designed for I/O-intensive, latency-sensitive workloads.
• Magnetic (standard): Legacy option, not recommended for new deployments.
Storage Auto Scaling can automatically increase storage capacity when usage approaches the allocated limit.
3. Multi-AZ Deployments:
For high availability, RDS supports Multi-AZ deployments. A synchronous standby replica is maintained in a different Availability Zone. In the event of a failure, RDS automatically fails over to the standby with minimal downtime. This is not used for read scaling — it is purely for availability and durability.
4. Read Replicas:
Read replicas use asynchronous replication to offload read traffic from the primary instance. They are available for MySQL, PostgreSQL, MariaDB, Oracle, and SQL Server. Aurora supports up to 15 read replicas with very low replication lag. Read replicas can be promoted to standalone instances and can be created in different AWS Regions for disaster recovery or latency reduction.
5. Backups and Snapshots:
• Automated Backups: RDS performs daily full backups and captures transaction logs, enabling point-in-time recovery (PITR) within the retention period (1–35 days).
• Manual Snapshots: User-initiated snapshots that persist until explicitly deleted. These can be shared across accounts or copied across Regions.
6. Security:
• Encryption at rest using AWS KMS (must be enabled at creation time).
• Encryption in transit using SSL/TLS.
• VPC isolation, security groups, and IAM authentication (for MySQL and PostgreSQL).
• Integration with AWS Secrets Manager for credential rotation.
7. Monitoring:
• Amazon CloudWatch metrics for CPU, memory, IOPS, storage, and connections.
• Enhanced Monitoring provides OS-level metrics at granular intervals.
• Performance Insights helps identify database bottlenecks by analyzing DB load.
How to Select the Right Relational Database Engine
Choosing the correct engine depends on several factors:
Amazon Aurora:
• Best for cloud-native relational workloads requiring high performance and availability.
• Up to 5x throughput of standard MySQL and 3x of standard PostgreSQL.
• Automatically replicates data across 3 AZs with 6 copies of data.
• Supports Aurora Serverless for variable or unpredictable workloads.
• Supports Aurora Global Database for cross-Region replication with sub-second latency.
• Choose Aurora when you need maximum performance, scalability, and availability from a managed relational database.
MySQL / MariaDB:
• Open-source, widely adopted, strong community support.
• Good for web applications, content management, and general-purpose OLTP.
• MariaDB is a fork of MySQL with additional storage engines and features.
• Choose these when application compatibility with MySQL is required and Aurora's cost or features are unnecessary.
PostgreSQL:
• Advanced open-source database with strong support for complex queries, JSON data types, full-text search, and geospatial data (PostGIS).
• Excellent standards compliance and extensibility.
• Choose PostgreSQL when you need advanced SQL features, complex data types, or spatial capabilities.
Oracle Database:
• Enterprise-grade commercial database for complex business applications.
• Supports Bring Your Own License (BYOL) or License Included pricing.
• Choose Oracle when existing enterprise applications mandate Oracle compatibility.
Microsoft SQL Server:
• Popular with .NET-based applications and Microsoft ecosystems.
• Available in Express, Web, Standard, and Enterprise editions.
• Choose SQL Server when integration with Microsoft technologies is required.
When NOT to Use Amazon RDS
Understanding when RDS is not the right choice is equally important:
• NoSQL / Key-Value workloads: Use DynamoDB instead.
• Data warehousing / OLAP: Use Amazon Redshift instead.
• Document or graph databases: Use Amazon DocumentDB or Amazon Neptune.
• In-memory caching: Use Amazon ElastiCache (Redis or Memcached).
• Time-series data: Use Amazon Timestream.
• Ledger / immutable records: Use Amazon QLDB.
• Need full OS-level access: Consider running a database on EC2 instead of RDS.
RDS in Data Engineering Pipelines
As a data engineer, you will commonly use RDS in these patterns:
• Source for ETL: Extract data from RDS using AWS Glue, AWS DMS, or custom scripts into S3 or a data lake.
• Change Data Capture (CDC): Use AWS DMS with CDC to replicate ongoing changes from RDS to a target such as S3, Redshift, or another RDS instance.
• Target for processed data: Load transformed data back into RDS for consumption by applications.
• RDS as a Glue JDBC source: AWS Glue can connect to RDS via JDBC connections to crawl schemas and run ETL jobs.
• Exporting snapshots to S3: RDS supports exporting DB snapshots to S3 in Apache Parquet format for analytics. This is a cost-effective method for performing analytics on RDS data without impacting production performance.
Key Concepts to Remember
• Aurora Serverless v2: Scales compute capacity automatically based on demand. Ideal for development, testing, and applications with intermittent or unpredictable workloads.
• Aurora Global Database: Enables a single Aurora database to span multiple AWS Regions for low-latency global reads and disaster recovery with an RPO of ~1 second.
• RDS Proxy: A fully managed database proxy that pools and shares database connections, improving application scalability and resilience. Particularly useful with Lambda functions to avoid connection exhaustion.
• IAM Database Authentication: Allows authentication to MySQL and PostgreSQL using IAM roles and tokens instead of passwords.
• Cross-Region Read Replicas: Useful for disaster recovery and serving read traffic closer to global users.
• Blue/Green Deployments: RDS supports managed blue/green deployments for safer database updates, allowing you to create a staging environment that mirrors production, make changes, and then switch over with minimal downtime.
Exam Tips: Answering Questions on Amazon RDS and Relational Database Selection
1. If the question mentions high availability: Think Multi-AZ deployment. Multi-AZ is for failover and availability — not for read scaling. Read replicas are for read scaling.
2. If the question mentions read-heavy workloads: Think Read Replicas. Aurora supports up to 15 read replicas with millisecond replication lag. Other engines support up to 5 read replicas.
3. If the question mentions highest performance for relational data: Choose Amazon Aurora. It is the default best answer for high-performance managed relational databases on AWS.
4. If the question mentions variable or unpredictable workloads: Think Aurora Serverless. It automatically scales capacity up and down based on demand.
5. If the question mentions migrating data from RDS to S3: Consider RDS snapshot export to S3 (Parquet format) for one-time or periodic analytical workloads. For continuous replication, use AWS DMS with CDC.
6. If the question mentions global applications or disaster recovery across Regions: Think Aurora Global Database or cross-Region read replicas.
7. If the question mentions Lambda connecting to RDS: RDS Proxy is the answer. It manages connection pooling and prevents connection exhaustion from concurrent Lambda invocations.
8. If the question mentions OLAP or data warehousing: Do not choose RDS. Choose Amazon Redshift. RDS is designed for OLTP workloads.
9. If the question mentions schema-less or key-value data: Do not choose RDS. Choose DynamoDB.
10. If the question mentions encryption: Remember that RDS encryption at rest must be enabled at instance creation. You cannot encrypt an existing unencrypted instance directly — you must create an encrypted snapshot and restore from it.
11. If the question mentions cost optimization: Consider Reserved Instances for steady-state workloads, Aurora Serverless for variable workloads, and read replicas instead of scaling up the primary instance.
12. If the question mentions connecting AWS Glue to RDS: Think JDBC connection within a VPC. The Glue job needs a VPC configuration with proper security group rules to access the RDS instance.
13. If the question mentions automated credential management: Think AWS Secrets Manager with automatic rotation for RDS credentials.
14. Watch for distractors: The exam may present scenarios where multiple services seem viable. Always match the workload pattern (OLTP vs. OLAP, structured vs. unstructured, transactional vs. analytical) to the appropriate service.
15. Remember the storage limits: Standard RDS instances support up to 64 TB of storage (engine-dependent). Aurora supports up to 128 TB with automatic storage scaling.
Summary
Amazon RDS is a versatile, fully managed relational database service that supports six popular engines. For the AWS Data Engineer Associate exam, focus on understanding when to choose RDS (and which engine), how to configure it for high availability and performance, how to integrate it into data pipelines, and when to choose alternative AWS database services. Aurora is generally the preferred choice for cloud-native relational workloads, while other engines are selected based on compatibility requirements. Master the distinctions between Multi-AZ (availability), Read Replicas (read scaling), and Aurora Serverless (variable workloads), and you will be well-prepared to answer RDS-related exam questions confidently.
