Managed Databases: RDS and Aurora

In part seven we agreed that a server can vanish at any moment, so nothing important stays on it. The most important thing you have is your data, so where does it live?

You could install PostgreSQL on an EC2 server yourself. That works, but now you own the backups, the patches, the replication, the failover and the disk that fills up at 3 a.m. RDS takes most of that off your hands.

What RDS does for you #

RDS (Relational Database Service) runs the database engine you choose on servers AWS manages. The supported engines are PostgreSQL, MySQL, MariaDB, Oracle, SQL Server and Db2, plus Aurora, which we'll reach shortly.

The trade-off is that you can't SSH into the server or touch the operating system. If you truly need that with Oracle or SQL Server, RDS Custom gives you access to the server while AWS keeps much of the automation.

Storage that grows by itself #

Turn on Storage Auto Scaling and set a ceiling, and RDS expands the disk when free space runs low, with no downtime. Note that it only grows: you can't shrink it afterwards.

Multi-AZ and read replicas #

This is the most important idea in this part, and the two often get mixed up. One protects you from failures; the other helps you serve more reads.

Multi-AZ #

Synchronous means a write isn't confirmed until it reaches both copies. When the primary fails, RDS points the same DNS name at the standby, and your application reconnects with no changes. You can turn this on for a running database without downtime.

There's also a newer form called a Multi-AZ DB cluster for MySQL and PostgreSQL: one primary with two standbys you can read from, and faster failover.

Read replicas #

You can have up to 15 replicas on PostgreSQL, MySQL and MariaDB, and fewer on the others. A common example: your dashboard's heavy reports read from a replica, leaving the production database alone.

Watch out for two things:

• RDS doesn't split reads for you. Your application sends reads to the replica's endpoint.
• Replication is asynchronous. A user may not see what they wrote a moment ago, so keep reads that must be current on the primary.

Replicating within a Region has no data transfer charge; between Regions it does.

Backups and restore #

RDS takes an automatic daily backup and saves transaction logs every 5 minutes, so you can restore to any second within the retention period, up to about 5 minutes ago. You set the period from 1 to 35 days, and 0 turns automatic backups off.

Manual snapshots stay until you delete them, which makes them a good step before any big change.

The key point: a restore creates a new database with a new endpoint; it doesn't overwrite the old one. You then point your application at the new endpoint.

Security #

It's the same pattern we saw in part six: the database sits in a private network and accepts traffic only from the servers' security group.

• Network: turn off public access, and let the security group allow the port (5432 or 3306) only from the servers' security group.
• Encryption at rest: through KMS, and decide at creation. Enabling it later means encrypting a copy of a snapshot and restoring from it.
• Encryption in transit: TLS.
• Passwords: let RDS manage the admin password in Secrets Manager, or use IAM authentication with short-lived tokens on PostgreSQL, MySQL and MariaDB.
• Logs: export them to CloudWatch Logs.

RDS Proxy #

If you have many short-lived connections, such as the Lambda functions in part twenty-one, they can exhaust the database's connection limit. RDS Proxy sits in between and reuses connections, and your application connects to it as if it were the database.

It also shortens failover, because it keeps your application's connections open and routes them to the new database. It's reachable only from inside your private network.

Aurora #

Aurora is a database engine AWS built for the cloud, compatible with MySQL or PostgreSQL, so your application and tools work with little or no change. The fundamental difference is that storage is separate from the servers.

• Storage: you don't reserve space in advance. It grows by itself up to 256 TiB on recent versions, and keeps six copies across three Availability Zones.
• Servers: one writer and up to 15 readers. Because they share storage, replica lag is usually very small.
• Failover: on failure a reader is promoted, usually in under 30 seconds.
• Endpoints: the writer endpoint always points at the writer, and the reader endpoint spreads across the readers. You can create custom endpoints too, for example to reserve your strongest readers for reports.
• Replica scaling: Aurora can add and remove readers according to load.

Features worth knowing #

RDS or Aurora? #

On cost, Aurora's servers are pricier per hour and storage is billed differently. The standard option charges for read and write operations, while I/O-Optimized removes those charges in exchange for higher server and storage rates. Compare them in the pricing calculator using your real load.

Hands-on: a private database only your server can reach #

We'll use the server you launched in part three. Open Aurora and RDS and choose Create database:

1. Engine: choose PostgreSQL, and the setup option that shows all the details rather than the quick one.
2. Template: the cheapest on offer, such as Free tier if your account shows it.
3. Name: call the database series-db, keep the username postgres, choose Self managed and set a strong password.
4. Size: db.t4g.micro with 20 GiB.
5. Network: choose Connect to an EC2 compute resource and select your server. RDS sets up the security groups for you, and public access stays off.

Wait until the status shows Available, and copy the Endpoint from the Connectivity & security tab. Then sign in to the server through EC2 Instance Connect:

sudo dnf install -y postgresql16
psql -h YOUR-DB-ENDPOINT -U postgres -d postgres

Inside psql, create a table and try it:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  item TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (item) VALUES ('keyboard'), ('screen');

SELECT * FROM orders;

Leave with \q. Now confirm the database really is private by trying to reach it from CloudShell, which sits outside your network:

timeout 5 bash -c '</dev/tcp/YOUR-DB-ENDPOINT/5432' && echo open || echo closed

You should see closed. Then take a manual snapshot from CloudShell, which is what you'd do before any risky change:

aws rds create-db-snapshot \
  --db-instance-identifier series-db \
  --db-snapshot-identifier series-db-demo

aws rds describe-db-snapshots \
  --db-snapshot-identifier series-db-demo \
  --query "DBSnapshots[0].[Status,PercentProgress]" --output table

Extra: try a read replica #

A read replica needs automatic backups turned on for the primary, and it's a separately billed server. Some free account plans don't allow this. If yours does:

aws rds modify-db-instance --db-instance-identifier series-db \
  --backup-retention-period 1 --apply-immediately

aws rds create-db-instance-read-replica \
  --db-instance-identifier series-db-replica \
  --source-db-instance-identifier series-db

Once it's ready, connect to its endpoint from your server. You'll find all the rows in orders, and if you try to add one you'll get the error cannot execute INSERT in a read-only transaction.

aws rds delete-db-instance --db-instance-identifier series-db-replica \
  --skip-final-snapshot

aws rds delete-db-instance --db-instance-identifier series-db \
  --skip-final-snapshot --delete-automated-backups

aws rds delete-db-snapshot --db-snapshot-identifier series-db-demo

Summary #

• RDS handles operations, backups and patches, while designing tables and queries stays with you.
• Multi-AZ is for availability and can't be read from; read replicas are for scaling and can lag.
• A restore creates a new database with a new endpoint.
• Keep the database in a private network, accepting traffic only from the servers' security group.
• Aurora has shared storage with six copies, up to 15 readers and fast failover, but is compatible only with MySQL and PostgreSQL.

Test yourself #

Before you move on, try these questions to check you've got this part. Each one is a practical scenario, and the explanation appears once you answer.

Even with a strong database, repeating the same query thousands of times is wasteful. In part nine we add a fast layer in front of it with ElastiCache.