Lesson 3 of 11 · 11 min · Cloud basics
Managed databases
What you will learn
- What a managed database does for you, and what it does not
- Choosing between relational, key-value/document, cache and analytics services
- Multi-AZ failover versus read replicas
- Backups, point-in-time recovery and testing restores
- Secure access: private subnets, security groups, encryption, secrets
- Connection management and other operational realities
In simple words
Running a database yourself means installing it, backing it up, updating it and repairing it at night. A managed database is one the cloud provider runs for you. You still decide how it is used, who may reach it, and whether your backups have ever really been tested.
Words to know
- Managed database: a database the provider installs, updates and backs up for you. RDS is one.
- Replica: a copy of the database that is kept up to date.
- Failover: switching automatically to a standby copy when the main one fails.
- Point-in-time recovery: restoring the database to how it was at a chosen moment.
Why use a managed database
Running a database yourself on an EC2 instance means you handle installation, OS and engine patching, backups, replication, failover, monitoring, disk growth, and 3 a.m. recovery. A managed database service takes over the undifferentiated parts: provisioning, patching windows, automated backups, replication across zones, failover, storage scaling and metrics. You still own the schema, queries, indexes, access control, sizing and cost. For most teams the right default is: use the managed service unless you have a specific reason not to (unsupported extension, extreme tuning, licence terms).
The families
| Need | Service type | AWS | Azure | Google Cloud |
|---|---|---|---|---|
| Relational (SQL, transactions, joins) | managed MySQL/PostgreSQL/SQL Server/Oracle/MariaDB | RDS, Aurora (cloud-native MySQL/PostgreSQL-compatible) | Azure SQL, Database for PostgreSQL/MySQL | Cloud SQL, AlloyDB, Spanner |
| Key-value / document at huge scale, single-digit-millisecond access | NoSQL | DynamoDB, DocumentDB (MongoDB-compatible) | Cosmos DB | Firestore, Bigtable |
| In-memory cache / fast data structures | Redis/Memcached | ElastiCache, MemoryDB | Azure Cache for Redis | Memorystore |
| Analytics (warehouse) | columnar SQL | Redshift, Athena (query S3) | Synapse | BigQuery |
| Search | full-text | OpenSearch Service | Azure AI Search | (Elastic on GCP) |
| Graph, time series, ledger | specialised | Neptune, Timestream, QLDB | Cosmos DB graph | Bigtable, etc. |
How to choose: start with a relational database (PostgreSQL is an excellent default) because most applications have relational data and need transactions; add a cache for hot reads; use DynamoDB-style stores when you have a known access pattern at very large scale or need serverless scaling; use a warehouse for analytics instead of querying production. Choosing a NoSQL database to avoid learning SQL usually backfires.
Availability: Multi-AZ
A Multi-AZ database keeps a standby copy in another availability zone with synchronous replication of every committed write. If the primary or its zone fails (or during maintenance), the service fails over automatically to the standby: the DNS endpoint is repointed, typically within one to two minutes, and your application reconnects. Properties:
- The standby is for availability, not extra capacity: you cannot read from it (in the classic RDS Multi-AZ instance deployment; Multi-AZ cluster deployments and Aurora do expose readable standbys).
- Your application must use the endpoint name (never an IP) and retry connections with backoff, because open connections break during failover.
- It does not protect against bad data (a dropped table replicates instantly), so you also need backups.
- Production databases should be Multi-AZ; dev/test often are not, to save cost.
Aurora separates compute from a distributed storage layer that keeps six copies across three zones and can fail over to a replica in about 30 seconds or less; it offers up to 15 low-latency read replicas and a serverless option.
Scaling reads: read replicas
A read replica is an asynchronously replicated copy that serves read-only queries, so you can spread read-heavy load (reports, search pages) away from the primary. Points to know:
- Replication is asynchronous: replicas lag behind the primary by milliseconds to seconds (or more under load). A user who writes and immediately reads from a replica may not see their own write; read-your-writes paths should use the primary.
- Replicas have their own endpoints; the application (or a proxy) must route reads to them.
- They can be promoted to standalone databases (a disaster-recovery tool) and can be placed in other regions.
- Writes cannot scale this way. Vertical scaling (bigger instance), caching, query tuning, partitioning/sharding, or an architecture like Aurora/DynamoDB address write limits.
Backups and recovery
Managed services provide:
- Automated backups: a daily snapshot plus transaction logs, retained for 1-35 days (you choose), enabling point-in-time recovery (PITR) to any second within the window. A restore creates a new instance (new endpoint); you then switch the application over or copy data back.
- Manual snapshots: kept until you delete them; take one before risky migrations; copy to another region or account for disaster recovery.
- AWS Backup for centrally managed schedules and retention across services.
The golden rule: a backup you have not restored is not a backup. Test restores on a schedule, time them (that is your real RTO), and verify the data. Also keep your own logical backups (pg_dump) for portability and for protection from account-level problems. As a small illustration, with PostgreSQL 16 we ran a dump and restore and checked the data:
pg_dump -U postgres -Fc shop > shop.dump # logical backup, custom format
createdb -U postgres shop_restored
pg_restore -U postgres -d shop_restored shop.dump # restore into a fresh database
psql -U postgres -d shop_restored -c 'SELECT count(*) FROM customers;'
With a 1,000-row test table the dump file was about 9 KB and the restored database held the same 1,000 rows. Remember that deletion protection and final snapshots (deletion_protection = true, skip_final_snapshot = false in Terraform) stop an accidental destroy from erasing your data.
Security
- Private by default: put databases in private subnets with no public IP, and allow connections only from the application's security group on the database port (VPC and security-group lessons). A publicly accessible database is a finding in every security audit.
- Encryption at rest (KMS) enabled when the instance is created (it cannot be added later without a snapshot-copy-restore) and TLS in transit (
rds.force_ssl/require_secure_transport). - Credentials: never hard-code passwords. Use Secrets Manager (with automatic rotation) or IAM database authentication (short-lived tokens), and let RDS manage the master password. Applications use least-privilege database users, not the master user.
- Network access for people: no database ports on the internet; reach it through a bastion, Session Manager port forwarding or a VPN.
- Auditing and logs: enable the engine's audit/slow-query logs and ship them to CloudWatch.
DynamoDB in short
DynamoDB is a serverless key-value and document database: you define a table with a partition key (and optionally a sort key), and access data by key with predictable low latency at any scale. There are no servers or instances, you pay per request (on-demand) or per provisioned capacity, and it replicates across three zones. The design shifts from "model entities and join" to "model your access patterns first": queries are by key (or by secondary indexes), there are no joins, and a hot partition key limits throughput. It is excellent for sessions, carts, IoT events and lookups; a poor fit for ad-hoc analytics or complex relational queries.
Common mistakes
- Running a database on EC2 without a reason, and without tested backups.
- Public accessibility or
0.0.0.0/0rules on database ports. - Hard-coded master passwords in code or Terraform variables.
- Believing Multi-AZ is a backup, or that replicas are instantly consistent.
- No connection pooling and no retry logic.
- Never testing a restore.
- Forgetting deletion protection on production databases.
- Using one giant database instance for everything (no isolation between services).
Try it yourself
- In a sandbox account, create a small PostgreSQL RDS instance in a private subnet; allow access only from a security group; connect via Session Manager port forwarding.
- Take a manual snapshot, restore it as a new instance, and compare the endpoints.
- Explain what happens to your application during a Multi-AZ failover and write the retry logic.
- Run
pg_dump/pg_restorelocally and prove the restored data matches.
Check yourself
What is the difference between Multi-AZ and a read replica?
Multi-AZ is a synchronous standby for automatic failover (availability); a read replica is an asynchronous copy for scaling reads (and DR), which can lag.
Why does the application need retry logic with a managed database?
Failovers, maintenance and network issues drop connections for a short time. The application must reconnect to the endpoint name with backoff.
How do you restore a managed database to a specific minute yesterday?
Use point-in-time recovery from the automated backups, which creates a new instance restored to that time; then redirect the application or copy data.
Name three controls that keep a database from being exposed.
Private subnet with no public IP, security group allowing only the app tier on the DB port, and credentials from a secrets manager with TLS and encryption at rest.
Interview questions
How would you design the database tier for a production web application?
A managed relational database (RDS/Aurora) Multi-AZ in private subnets, encryption with KMS, automated backups with PITR plus cross-region copies, read replicas or a cache for read load, connection pooling, secrets rotation, monitoring and alerts, and tested restore and failover procedures.
RDS or DynamoDB?
RDS (SQL) for relational data, transactions and flexible queries; DynamoDB for key-based access at massive scale with predictable latency and no server management, designing around known access patterns.
What is RPO and RTO for a database?
RPO (recovery point objective) is how much data loss is acceptable (PITR gives seconds-to-minutes); RTO (recovery time objective) is how long recovery may take (failover about a minute or two; restoring from snapshot can take much longer for large databases).
Summary
- Managed databases handle provisioning, patching, backups and failover; you own schema, queries, access and sizing.
- Choose relational by default; cache for hot reads; DynamoDB for key-based scale; a warehouse for analytics.
- Multi-AZ for availability, replicas for read scaling and DR, backups with PITR for data protection, with restores tested.
- Keep databases private, encrypted, with secrets managed, pooled connections and deletion protection.
Going deeper
- Amazon RDS user guide: features, Multi-AZ, replicas and backups.
- Multi-AZ deployments: how failover works.
- Amazon Aurora: storage architecture and replicas.
- Amazon DynamoDB developer guide: data modelling and capacity.
- PostgreSQL: backup and restore: logical and physical backups.
See how much of this you can explain
A free mock interview asks you new questions on Cloud platforms and AWS fundamentals for DevOps Engineer and scores every answer, with what to fix.