Day 4 - RDS/Aurora and Store Selection: OLTP, Zero-ETL, Workload-to-Store Decision

📌 Key Takeaways

• RDS/Aurora are row-based OLTP stores. They suit normalized schemas, transactions, and reading and writing small numbers of rows, and they do not suit bulk column aggregation.
• Aurora separates compute from storage and replicates data to 6 storage nodes across 3 AZs. There can be up to 15 readers (Aurora replicas).
• Analytics load on an operational database is peeled off in stages: Read Replica (same engine, first relief) → replicate to a warehouse (zero-ETL, DMS) → export to the lake if needed.
• Zero-ETL is AWS-managed replication that moves the source into Redshift in near real time. It does no transformation, and the target database is read-only. Heterogeneous database migration and a wide range of source and target combinations belong to DMS.
• Choose a store by access pattern: transactions and joins = RDS/Aurora, key lookups = DynamoDB, bulk aggregation = Redshift, ad hoc on S3 = Athena, search = OpenSearch.

RDS and Aurora Overview

• Amazon RDS: Hosts MySQL, PostgreSQL, MariaDB, Oracle, SQL Server, and Db2 as a managed service. The engine stays as it is, and the service takes care of patching, backup, recovery, and failover. Storage is an EBS volume attached to the instance.
• Amazon Aurora: A cloud-native engine compatible with MySQL and PostgreSQL. The instances (compute) and the cluster volume (storage) are separated.

Aurora cluster
  ├─ 1 writer instance ──┐
  ├─ reader instances (up to 15) ┼──► Cluster volume: replicated across 3 AZs · 6 storage nodes, auto-expanding
  └─ ...                 ─┘     (adding instances does not copy the data anew)

• The upper limit of an Aurora cluster volume was long known as 128 TiB, and the current documentation lists 256 TiB for the latest engine versions (Aurora PostgreSQL 15.13, 16.9, 17.5 and later, Aurora MySQL 3.10 and later). Exam materials may use the old value.
• Aurora storage is billed as you use it, and when you delete data the allocation shrinks too. For the storage configuration, you choose between Aurora Standard (I/O requests billed separately) and Aurora I/O-Optimized (no I/O charge). If I/O spending is 25% or more of total Aurora cost, I/O-Optimized is advantageous.
• If a reader exists, it is promoted when the writer fails, and recovery usually takes under 60 seconds. If there is no reader at all, a new instance must be created, which takes much longer.

💡 Related Theory: RDS/Aurora use row-based storage. All the columns of one row sit together on disk, which is optimal for "read and update one order," but to "sum only the amount column across 100 million rows" it has to read the unneeded columns as well. Columnar Redshift and Parquet are the exact opposite. This is why the first question in choosing a store is "are you handling rows, or aggregating columns?"

OLTP vs OLAP

Moving OLTP data into an OLAP store for analytics is ETL. The problem is building and maintaining that pipeline. Scheduling, retries, handling schema changes, and latency all become an operational burden.

Stages for Peeling Off Analytics Load

If you throw heavy report queries straight at the operational database, transactions slow down from lock and I/O contention. The ways of peeling the load off come in an order, starting from the lightest burden.

• An RDS Read Replica uses asynchronous replication and is read-only. The Multi-AZ standby instance uses synchronous replication but cannot accept read traffic. For "availability," it is Multi-AZ; for "spreading reads and separating reporting," it is a Read Replica.
• RDS does not automatically add or remove Read Replicas. A replica is billed at the same rate as a regular DB instance, so if it is needed only while reports run, weigh that cost.
• There are also federated queries that query the operational database directly from Redshift or Athena. They do not move data, but every query puts load on the operational database, so they suit occasional lookups or checking the latest values.

Zero-ETL Integration

A zero-ETL integration replicates source data into Redshift without building a pipeline. AWS takes care of the initial load, subsequent change replication, health monitoring, and recovery.

Aurora MySQL / Aurora PostgreSQL / RDS / DynamoDB ...
   │  (zero-ETL integration — after the initial load, keeps replicating changes)
   ▼
Amazon Redshift (Serverless or RA3-family provisioned)
   └─ "Target database" created by the integration — read-only
        └─ Process with materialized views and cross-database queries from other databases

Constraints to be aware of are as follows.

• The source tables must have a primary key. Tables without one are not replicated.
• The source cluster and the target must be in the same Region.
• If you run DDL such as ALTER TABLE on the source, that table is resynchronized, and during that time it may not be queryable from Redshift.
• The initial load can take 20 to 25 minutes or more when you first create the integration and at resynchronization.
• Aurora MySQL integration depends on the binary log (binlog). Aurora PostgreSQL requires you to specify at least one data filter (at the database level).

⚠️ Pitfall: "Zero-ETL" comes close to meaning there is no T (transformation). The normalized operational schema arrives in Redshift as it is. To convert it to a star schema or build aggregate tables, you must process it inside Redshift with materialized views or SQL. If the question asks for "masking or transforming columns during replication," it is not zero-ETL but the transformation rules of DMS or Glue.

Comparison with DMS

AWS DMS (Database Migration Service) runs a task that reads from a source endpoint and writes to a target endpoint on a replication instance (or serverless).

• For heterogeneous engines (for example Oracle → Aurora PostgreSQL), the schema and code objects must be converted first. DMS Schema Conversion or AWS SCT does this work, and DMS moves the data. With the same engine, no conversion is needed.
• During migration, the source keeps running.
• When the target is Redshift, DMS uploads the data to S3 as CSV and then loads it with COPY. In this case the Redshift cluster must be in the same account and the same Region as the replication instance.
• If you make S3 the target, it becomes a CDC pipeline that accumulates change history in the lake.

Ways to Export from RDS/Aurora to the Lake

Workload → Store Decision Framework

Decide the access pattern first, then match the store.

① Do you read and write a single record, or aggregate many rows?
   ├─ A single record (transaction) ──► go to ②
   └─ Aggregation (analytics) ────────► go to ③
 
② Do you need relationships, joins, and transactions across multiple rows?
   ├─ Needed ─────────────► RDS / Aurora
   └─ Lookups by key only, with large scale and concurrency ──► DynamoDB
 
③ How often and how heavily do you analyze?
   ├─ Constant, repeated, complex joins, fast response ──► Redshift
   ├─ Occasional, directly on data in S3 ─────────────────► Athena
   └─ Warehouse + S3 history together ────────────────────► Redshift Spectrum
 
④ Is the shape special?
   ├─ Full-text search, log exploration ───► OpenSearch Service
   ├─ Microsecond cache, sessions ─────────► ElastiCache / MemoryDB
   ├─ Relationship traversal (graph) ──────► Neptune
   ├─ Time-series metrics ─────────────────► Timestream
   └─ JSON documents (MongoDB compatible) ─► DocumentDB

The selection criteria, one line each, are as follows.

• Access pattern: key lookups / transactions and joins / aggregation / search / relationship traversal.
• Data shape: structured / semi-structured / unstructured.
• Latency and concurrency: many millisecond requests / heavy queries taking seconds to minutes.
• Operating model: serverless (Athena, DynamoDB on-demand, Redshift Serverless, Aurora Serverless) / provisioned.
• Billing unit: instance hours (RDS, Aurora) / request and capacity units (DynamoDB) / node hours and RPUs (Redshift) / scan volume (Athena).

Symptom → Cause → Action

Question Clue → Answer Lookup Table

Tomorrow we put this week's stores on a single page and review the choice by workload and the data movement paths between stores.

📖 Glossary

• OLTP / OLAP: Transaction processing of small numbers of rows, and analytical and aggregation processing of large volumes of data.
• Cluster volume: Aurora's shared storage. It is replicated across several AZs and used jointly by all instances.
• Read Replica: A read-only copy that follows the source asynchronously. It shares the read load.
• Multi-AZ: An availability configuration that keeps a standby instance in a different AZ and switches over automatically on failure.
• CDC: Change Data Capture. A method that captures and delivers only the changes (inserts, updates, deletes) of the source.
• Zero-ETL integration: A managed feature in which AWS continuously replicates a source into an analytics store without building a pipeline.
• DMS: A service that moves databases and keeps replicating changes. It consists of a replication instance, endpoints, and tasks.
• Schema conversion: The work of converting table definitions and code objects into the target engine's syntax when moving to a database with a different engine.
• Federated query: A query that reads another store directly without moving the data.
• Star schema: An analytics model made of a central fact table and surrounding dimension tables.