Opscraft
Database operations · Lesson 1 of 6 · 3 min read

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):

  1. A transaction's changes are first appended to the log and flushed to disk (fsync).
  2. Only then is the commit acknowledged to the client.
  3. The actual data files are updated later, in the background (checkpoints).
  4. 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.

Enlarge diagramCOMMITfrom the appWAL on diskappend + fsyncdata filesupdated later at checkpointsreplicasstream the WALarchive in S3for point-in-time recovery
Figure 1. Write-ahead logging: the change is safe on disk in the log before the commit is confirmed. The same log feeds replicas and backups.
Swipe sideways to see all of it, or tap to open it full screen.

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
Enlarge diagramRPO: data lostRTO: time downlast good copyfailureservice back
Figure 2. RPO is measured back from the failure, RTO forward from it.
Swipe sideways to see all of it, or tap to open it full screen.

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".