How databases keep data safe
Databases hold the one thing you can't redeploy: the data. DevOps and platform engineers are expected to run them safely even without being database administrators: backups that actually restore, replication and failover that apps survive, connection and performance problems, schema changes without downtime, and upgrades. Interviewers probe this with "your database is down", "someone dropped a table" and "how do you upgrade PostgreSQL with no downtime?". This topic focuses on PostgreSQL and MySQL on AWS (RDS and Aurora), with ideas that carry over anywhere.
What operations owns
Even with a managed service, someone has to own:
- Backups and restores, tested regularly, meeting agreed RPO and RTO.
- High availability: failover that applications survive.
- Capacity and performance: connections, CPU, memory, storage, IOPS, slow queries.
- Changes: schema migrations, parameter changes, engine upgrades.
- Security: encryption, network access, credentials and their rotation, auditing.
A managed service like RDS takes care of hardware, OS patching, automated backups and the failover mechanism. It does not design your schema, fix slow queries, test your restores or make your app reconnect after a failover.
The write-ahead log
Relational databases make writes durable with a write-ahead log (WAL in PostgreSQL, the redo log and binary log in MySQL):
- A transaction's changes are first appended to the log and flushed to disk (
fsync). - Only then is the commit acknowledged to the client.
- The actual data files are updated later, in the background (checkpoints).
- After a crash, the database replays the log from the last checkpoint to recover committed transactions.
The same log powers most of this topic: replication ships it to replicas, and point-in-time recovery replays archived log on top of a backup.
RPO and RTO
| Term | Question it answers | Driven by |
|---|---|---|
| RPO (recovery point objective) | How much data can we afford to lose? | How often data is copied elsewhere: backups, log archiving, replication |
| RTO (recovery time objective) | How long can we be down? | How fast you can fail over or restore: standby replicas, restore speed, automation |
Agree these with the business per system. "RPO 5 minutes, RTO 1 hour" leads to a very different design (and bill) than "RPO zero, RTO 1 minute".