Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 70 ยท Databases

RDS and Aurora

Understand managed relational databases on AWS: RDS engines, Multi-AZ versus read replicas, backups, parameter groups, Aurora's shared storage and RDS Proxy, with Python stand-ins.

Running a relational database yourself means patching the engine, scheduling backups, setting up replication, planning failover and watching disks fill up. Amazon RDS (Relational Database Service) takes over those chores: you pick an engine and a size, and AWS provisions the server, installs the software, takes backups, applies patches in a maintenance window and replaces failed hardware. Amazon Aurora is AWS's own cloud-built engine family that keeps the MySQL and PostgreSQL wire protocols but rebuilds the storage layer underneath.

The real services cannot run on this site, so the CLI is read-only. Two Python stand-ins show the ideas that cause the most confusion in practice: asynchronous replication lag (and what a failover can lose), and why a connection pool in front of the database matters. They use sqlite3 as a pretend database; they are teaching models, not RDS.

What RDS manages and what you manage

RDS supports Aurora (MySQL-compatible and PostgreSQL-compatible), and RDS for MySQL, PostgreSQL, MariaDB, Oracle, SQL Server and Db2. You do not get an operating system login; you get a database endpoint (a DNS name) and an admin user.

AWS handles You handle
Hardware, OS and engine patching Schema, queries, indexes
Automated backups and point-in-time recovery Choosing retention and restoring when needed
Failover mechanics for Multi-AZ Deciding whether you need Multi-AZ at all
Storage provisioning and optional autoscaling Instance size and storage type
Encryption at rest with KMS Network access: subnets, security groups, TLS

An RDS instance lives in a DB subnet group, a set of subnets in at least two Availability Zones of your VPC (VPC Fundamentals). Keep it in private subnets and allow its port (3306 for MySQL, 5432 for PostgreSQL) only from the security group of your application. A publicly reachable database is almost never what you want.

Read-only commands to create an instance look like this:

Terminal
aws rds create-db-instance \
  --db-instance-identifier shop-db \
  --engine postgres \
  --db-instance-class db.t4g.medium \
  --allocated-storage 50 --storage-type gp3 \
  --master-username shopadmin --manage-master-user-password \
  --db-subnet-group-name private-db-subnets \
  --vpc-security-group-ids sg-0abc1234 \
  --multi-az --backup-retention-period 7 \
  --storage-encrypted --deletion-protection

The --manage-master-user-password flag lets RDS keep the admin password in Secrets Manager instead of in your shell history. Deletion protection and a final snapshot guard against the wrong command.

Multi-AZ versus read replicas

These two features are often confused, but they solve different problems.

Multi-AZ is about availability. In the classic Multi-AZ DB instance setup, RDS keeps a standby copy in another Availability Zone and replicates to it synchronously: a commit finishes only after the standby has the data. You cannot read from the standby. If the primary fails, RDS flips the endpoint's DNS to the standby, typically within one to two minutes, and nothing acknowledged is lost. A newer Multi-AZ DB cluster option for MySQL and PostgreSQL uses one writer and two readable standbys in three AZs with typically faster failover.

Read replicas are about scale. They replicate asynchronously from the primary, so they can be a little behind (replication lag) but you can send read-only queries to them, put them in another Region, and promote one to a standalone database. MySQL, MariaDB and PostgreSQL engines typically allow up to 15 of them. Because they are asynchronous, a failover to a replica can lose the latest commits that had not yet arrived.

flowchart LR
    APP["Application"] -->|"writes and reads"| P["Primary<br/>AZ a"]
    P -->|"synchronous<br/>not readable"| S["Multi-AZ standby<br/>AZ b"]
    P -->|"asynchronous"| R1["Read replica<br/>AZ c"]
    P -->|"asynchronous"| R2["Read replica<br/>other Region"]
    APP -->|"read-only queries"| R1

Multi-AZ gives a synchronous, passive standby for failover; read replicas are asynchronous copies you can query.

You can combine both: Multi-AZ for the primary's resilience, read replicas for read scaling. The model below makes the difference concrete. It simulates a primary and a replica whose log is applied after a lag measured in ticks.

Python Replica lag and what a failover can lose

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The practical lesson: read from a replica only when slightly stale data is acceptable (reports, dashboards, product listings), and always read your own just-written data from the primary. Multi-AZ never has this problem, because the standby is synchronous, but it cannot serve reads.

Backups, snapshots and parameters

RDS takes a daily automated backup during a window you choose and continuously uploads transaction logs. Together they allow point-in-time recovery to any second within the retention period, typically up to the last few minutes. The retention period is 1 to 35 days (0 disables automated backups, and with them point-in-time recovery). A restore always creates a new instance with a new endpoint; you then repoint your application. Manual snapshots are kept until you delete them, survive deleting the instance, and can be copied to other Regions or shared with other accounts. Backups protect against mistakes like a bad DELETE; Multi-AZ protects against hardware and AZ failure. You need both.

Engine settings live in a parameter group, a named set of values like max_connections or log_min_duration_statement. You cannot edit the default group, so you create a custom one and attach it. Dynamic parameters apply immediately; static parameters take effect only after a reboot of the instance. Changing instance class, storage or engine version can cause a short outage unless you use Multi-AZ or a blue/green deployment, so schedule it.

Aurora

Aurora keeps your application compatible with MySQL or PostgreSQL but separates compute from storage. All instances in an Aurora cluster share one cluster volume that is replicated into six copies across three Availability Zones, grows automatically (up to 128 TiB), and heals itself in small segments. Writes go to the single writer instance; up to 15 Aurora Replicas read the same shared storage, so replica lag is usually measured in milliseconds, and any replica can be promoted to writer, typically in about thirty seconds or less. A cluster has a writer endpoint, which always points at the current writer, and a reader endpoint that spreads connections across replicas.

Other Aurora features: Aurora Serverless v2 scales capacity up and down in small increments (measured in Aurora capacity units), Global Database replicates to other Regions with typically under a second of lag, and fast cloning uses copy-on-write storage. Aurora usually costs more per instance than plain RDS but may pay off through higher throughput, simpler replicas and faster failover. For small or predictable workloads, plain RDS is often cheaper and entirely adequate.

Backtrack and the old multi-master mode

Backtrack (Aurora MySQL-compatible only) rewinds a whole cluster to an earlier moment in place, without restoring a backup into a new cluster. You turn it on with a backtrack window (up to 72 hours), and Aurora keeps change records for it, which are billed. After a bad DELETE or a failed migration you ask for a time a few minutes ago, and the cluster is back in seconds to minutes. It is not a replacement for backups, because the window is limited, and it is not available for Aurora PostgreSQL, where you use point-in-time restore or fast cloning instead.

An earlier feature, multi-master clusters, allowed several writers in one Aurora MySQL cluster for continuous write availability. It was limited to an old engine version and has been retired, so treat it as history: the current design is one writer plus replicas, with Aurora Global Database or Serverless v2 for other needs.

Working with an RDS database

You reach an RDS database like any other: connect to its endpoint with a normal client such as psql, mysql or your application's driver, from inside the VPC (or through a bastion or VPN). You manage the instance through the console, CLI or API (aws rds describe-db-instances, modify-db-instance, create-db-snapshot). You do not get the operating system or a true superuser; the engine's administrative actions are offered through limited roles and parameter groups. That is the price of a managed service. The main drawbacks are exactly that limited OS and engine customization, slower access to the newest engine versions, and a higher cost than a database you run yourself on EC2 (which in turn costs you the operations work).

RDS or Aurora?

RDS (MySQL, PostgreSQL, MariaDB) Aurora
Storage EBS volume per instance Shared cluster volume, six copies across three AZs
Failover Multi-AZ standby, typically one to two minutes Replica promotion, typically about thirty seconds or less
Read replicas Up to 15 for several engines, each with its own copy of the data Up to 15, all reading the same storage
Self-healing storage Depends on EBS Built in
Engines Many, including Oracle and SQL Server MySQL and PostgreSQL compatible only
Price Lower for small, steady databases Higher per instance; may pay off at scale

The quick chooser below turns those points into a rule of thumb.

Python RDS or Aurora?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

RDS Proxy

Every database connection uses memory, and each engine has a max_connections limit. A fleet of Lambda functions or containers can open thousands of connections at once, far more than the database can serve, and each failover forces everyone to reconnect. RDS Proxy sits between the application and the database, keeps a warm pool of connections and multiplexes many client connections over a few database ones. It also reduces failover time by holding client connections while the database switches, and it supports IAM authentication and Secrets Manager credentials. It is particularly useful with serverless code (Lambda Concurrency, Cold Starts and Limits).

Python Why a connection pool saves the database

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A pool is a trade: fewer database connections, but a queue forms when demand exceeds the pool. The last table shows that doubling the pool halves the waiting, until the database itself becomes the bottleneck, so you tune the pool with the instance's max_connections in mind.

Choosing and operating

  • Start with RDS for PostgreSQL or MySQL unless you need a specific commercial engine or Aurora's features.
  • Turn on Multi-AZ for anything production; use read replicas for read-heavy workloads and cross-Region reads.
  • Keep backups on, test a restore, and protect production with deletion protection.
  • Right-size using CloudWatch metrics (CloudWatch Metrics, Logs and Alarms) such as CPU, freeable memory, connections and replica lag.
  • For key-value access at massive scale, consider DynamoDB Fundamentals instead of forcing a relational design.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the main purpose of a classic Multi-AZ DB instance deployment?

    The Multi-AZ standby is synchronous and passive: it exists for failover, not for read scaling.
  2. 2. Why can promoting a read replica after a primary failure lose data?

    Read replicas apply changes after the primary commits them, so the replica can be slightly behind when the primary fails.
  3. 3. How does an Aurora cluster store data?

    Aurora separates compute from a shared, self-healing storage volume that keeps six copies across three AZs.
  4. 4. A fleet of Lambda functions exhausts the database's connection limit. What is the best fix?

    RDS Proxy keeps a pool of database connections and shares them among many short-lived clients.
  5. 5. What does restoring an RDS instance to a point in time create?

    A restore never overwrites the original; it launches a new instance that you then switch your application to.

Summary

  • RDS and Aurora are managed relational databases: AWS runs patching, backups and failover, you run schema, queries and network access.
  • Multi-AZ gives synchronous, unreadable standbys for availability; read replicas are asynchronous copies for scaling reads.
  • Backups (automated plus manual snapshots) and Multi-AZ solve different problems; restores create new instances.
  • Aurora shares a six-copy, three-AZ storage volume between a writer and up to 15 replicas.
  • RDS Proxy pools connections, which protects the database from connection storms.

Next: DynamoDB Fundamentals.