Phase 13Advanced14 of 18 in Database Design

Operations: Migrations, Backup, Disaster Recovery and Multi-Region

Zero-downtime schema migrations with expand-contract, Flyway and safe DDL; backups, restore testing and PITR; RPO and RTO; active-passive and active-active regions; and hot, warm and cold data lifecycle.

A database design is only as good as your ability to change it without downtime and to recover it after the worst day. This phase turns those into routine, tested procedures.

0/5 · 0%

5 topics ~41 min 8 code blocks & diagrams

Start with the first topic

13.1

Zero-Downtime Schema Migrations

Schema changes run while old and new application versions are both live, so every migration must be backward compatible. The expand–contract pattern adds new structures, dual-writes and backfills, switches reads, then removes the old. Versioned migration tools (Flyway, Liquibase) apply changes in order, and PostgreSQL DDL must be written to avoid long locks.

9 min 1 diagram 1 code practice

13.2

Backup and Restore

Backups are only as good as your last successful restore. Combine physical base backups (full plus incremental or differential) with continuous WAL archiving for point-in-time recovery, keep copies in another account and region, and restore-test automatically, measuring time and verifying data. Logical dumps are for migrations and small databases, not large-scale recovery.

8 min 2 code practice

13.3

Disaster Recovery: RPO, RTO, Failover and Drills

RPO is how much data you can afford to lose; RTO is how long you can be down. They drive the DR architecture: backups only (hours), a warm standby in another region (minutes, some data loss with async replication), or active-active (seconds, with conflict handling). Failover, failback and regular drills make the numbers real.

8 min 1 diagram 1 code practice

13.4

Multi-Region Database Design

Multi-region designs place data near users and survive regional outages. Options: single-writer with cross-region read replicas, active-passive, geo-partitioning (each user's data lives in a home region), or multi-writer active-active with conflict resolution or consensus. Every choice trades write latency, consistency, data residency and complexity.

8 min 1 code practice

13.5

Data Lifecycle: Hot, Warm, Cold, Retention and Deletion

Data's value and access frequency drop with age. Keep hot data on fast storage and in the transactional database, move warm data to cheaper tiers or partitions, archive cold data to object storage, and delete on schedule according to retention policy and law. Lifecycle is designed into the schema (time partitioning, TTLs), not bolted on.