Unit 3: Database Services

Databases on AWS let applications persist structured and unstructured data without the operational burden of running the engine software, patching, backups, and failover by hand. This unit builds on one governing distinction — the split between relational and nonrelational models — and then examines how AWS packages each as a managed service (RDS, Redshift, DynamoDB).

A relational database (model formalised by E. F. Codd, 1970) organises data into tables of rows and columns linked by keys.

• Table (relation): the basic store — a grid of rows (tuples) and columns (attributes), e.g. a Customers table with customer_id, name, email.
• Schema: a fixed structure defined before data is written; every row obeys the same column definitions and types.
• Primary key: a column (or set) uniquely identifying each row, e.g. customer_id.
• Foreign key: a column referencing another table's primary key, enforcing referential integrity between Orders.customer_id and Customers.customer_id.
• SQL: the declarative query language — SELECT, JOIN, INSERT — used to read and manipulate data.
• ACID properties: Atomicity, Consistency, Isolation, Durability — guarantees that transactions complete fully or not at all, keeping data valid.
• Normalisation: splitting data across tables to remove redundancy, trading storage economy for JOIN cost at query time.

II. Amazon Relational Database Service — managed SQL engines

A. Service overview

Amazon RDS provisions and operates a relational database engine on your behalf, so you manage data and schema while AWS manages the host.

• Managed scope: AWS handles OS patching, backups, snapshots, and automatic failover; you keep control of the schema, queries, and tuning parameters.
• Core objects: a DB instance (an isolated database environment running one engine), hosted inside a VPC for network isolation.
• Billing model: charged per instance-hour plus storage, I/O, and backup consumption — no up-front licence purchase required for open-source engines.

B. Database Engines

RDS supports six engines, letting you keep an existing SQL dialect while moving to a managed host.

• MySQL / MariaDB: popular open-source engines for web workloads; MariaDB is a community fork of MySQL.
• PostgreSQL: open-source engine with rich SQL features (window functions, JSON types, extensions like PostGIS).
• Oracle / Microsoft SQL Server: commercial engines carrying their own licences, used where enterprise applications demand them.
• Amazon Aurora: AWS's own MySQL- and PostgreSQL-compatible engine, with storage that auto-grows and replicates six copies across three Availability Zones (AZs).

C. Licensing Consideration

Engine choice determines how you pay for the database software itself.

1. License Included: AWS bundles the licence cost into the hourly instance price — available for SQL Server and some Oracle editions; simplest for short-lived or variable workloads.
2. Bring Your Own License (BYOL): you supply an existing Oracle licence you already own; cost-effective when you have paid-up commercial licences to reuse.
   • Open-source engines: MySQL, MariaDB, PostgreSQL carry no licence fee, so you pay only for infrastructure.

D. Database Option groups

Option groups let you enable engine-specific features that sit beside the core database.

• Purpose: a container of optional capabilities attached to a DB instance, applied by engine, e.g. Oracle Transparent Data Encryption (TDE) or SQL Server SQLSERVER_AUDIT.
• Scope: relevant mainly to Oracle and SQL Server; MySQL/PostgreSQL rely more on parameter groups.
• Contrast with parameter groups: an option group adds a feature module; a parameter group tunes engine configuration values such as max_connections.

E. Database Instance classes

The instance class fixes the compute and memory allocated to the DB instance, independent of storage.

• Standard (M-class): balanced CPU and memory for general workloads, e.g. db.m6g.large.
• Memory-optimised (R/X-class): high RAM for large working sets and caches, e.g. db.r6g.xlarge.
• Burstable (T-class): low baseline CPU with burst credits for intermittent, low-traffic databases, e.g. db.t3.micro.
• Scaling: changing class is a vertical scale operation; it requires a brief reboot (or failover on Multi-AZ) to take effect.

F. Storage

Storage is provisioned separately from compute and chosen by the I/O profile the workload needs.

• General Purpose SSD (gp2/gp3): cost-effective baseline for most workloads; gp3 lets you set IOPS and throughput independently of size.
• Provisioned IOPS SSD (io1/io2): for I/O-intensive, latency-sensitive databases where you specify a guaranteed IOPS figure.
• Magnetic: legacy, low-performance tier retained for backward compatibility.
• Autoscaling: RDS can grow storage automatically when free space falls below a threshold, avoiding downtime from running out of disk.

G. Read Replicas

Read Replicas scale read traffic by serving queries from asynchronous copies of the primary.

• Mechanism: the source instance streams changes to one or more replicas using the engine's native asynchronous replication, so replicas lag slightly behind.
• Use case: offload reporting and heavy SELECT queries from the write node; applications direct reads to replica endpoints.
• Reach: replicas can be created in the same Region or cross-Region, aiding disaster recovery and low-latency local reads.
• Promotion: a replica can be promoted to a standalone writable instance, breaking replication — a manual recovery path, not automatic failover.
• Limit: because replication is asynchronous, a replica may return slightly stale data; it does not guarantee read-after-write consistency.

H. High Availability

High availability on RDS means surviving the loss of an Availability Zone with minimal downtime.

• Multi-AZ deployment: RDS maintains a synchronous standby in a second AZ; writes commit to both before acknowledgement, so no committed data is lost on failover.
• Automatic failover: on primary failure, RDS flips the DNS endpoint to the standby, typically within 60–120 seconds, with no manual intervention.
• Standby role: the standby is not readable (unlike a read replica) — its sole purpose is resilience.
• Distinction: read replicas scale throughput (asynchronous, readable); Multi-AZ delivers availability (synchronous, not readable).

I. Single Master

The single-master topology routes every write through one primary node, the default RDS design.

• Write path: all INSERT/UPDATE/DELETE statements hit one writer; replicas and standbys receive changes from it.
• Consistency: having a single authoritative writer avoids write-write conflicts entirely, keeping conflict resolution unnecessary.
• Trade-off: the single writer is a throughput ceiling and a potential bottleneck for write-heavy applications.

J. Multi Master service

Multi-master allows more than one node to accept writes simultaneously, raising write availability.

• Capability: offered by Aurora Multi-Master, where multiple instances in a cluster all serve read and write traffic.
• Benefit: continuous write availability — if one writer fails, applications keep writing to another without failover delay.
• Conflict handling: concurrent writes to the same data create conflicts the system must detect and resolve, adding complexity absent in single-master.
• Contrast with Single Master: single-master trades write scalability for simplicity; multi-master trades simplicity for write availability and horizontal write scaling.

III. Amazon Redshift — managed data warehouse

Redshift is a petabyte-scale, SQL-based data warehouse built for analytics (OLAP) rather than transactions (OLTP).

A. Architecture and purpose

Redshift optimises large analytical scans over historical data instead of many small transactions.

• Columnar storage: data is stored by column, so a query touching few columns reads far less disk than a row store would.
• MPP (Massively Parallel Processing): a leader node plans queries and compute nodes execute slices in parallel across the cluster.
• Compression: column data compresses well because values in a column are similar, cutting I/O.
• Integration: loads from Amazon S3 and queries external S3 data directly via Redshift Spectrum.
• Contrast with RDS: RDS serves fast transactional reads/writes; Redshift aggregates billions of rows for business-intelligence reporting.

IV. Nonrelational Databases and DynamoDB

Nonrelational (NoSQL) databases drop the fixed-schema, table-join model in favour of flexible structures that scale horizontally.

A. Nonrelational Database

A nonrelational database stores data in formats other than rigid tables, trading some SQL power for scale and flexibility.

• Schema-less: records in the same store can carry different attributes, suiting evolving or semi-structured data.
• Model types: key-value, document, column-family, and graph stores, each shaped to a different access pattern.
• Horizontal scaling: data is partitioned (sharded) across many servers, so capacity grows by adding nodes rather than enlarging one.
• Consistency model: many NoSQL stores favour availability and partition tolerance, offering eventual consistency instead of strict ACID.
• Fit: high-throughput, low-latency workloads with simple access patterns — session stores, catalogues, IoT telemetry.

B. DynamoDB

Amazon DynamoDB is a fully managed key-value and document NoSQL service delivering single-digit-millisecond latency at any scale.

• Core structure: data lives in tables of items (rows), each item a set of attributes; there is no fixed schema beyond the key.
• Primary key: either a partition key alone, or a partition key + sort key composite that groups related items; the partition key is hashed to place data across nodes.

Table: Orders
Partition key: customer_id   Sort key: order_date
Item: { customer_id: "C100", order_date: "2026-10-11", total: 59.90 }

• Secondary indexes: Global (GSI) and Local (LSI) secondary indexes allow queries on non-key attributes.
• Capacity modes: provisioned (set read/write units) or on-demand (pay per request) to match predictable versus spiky traffic.
• Managed scaling: DynamoDB partitions and replicates data automatically across three AZs, and Global Tables give multi-Region, multi-master replication.
• DAX: DynamoDB Accelerator, an in-memory cache cutting read latency to microseconds for read-heavy workloads.
• Limit: lacks JOINs and complex ad-hoc queries, so the data model must be designed around known access patterns up front.