Designing Database Architecture
1. Choosing SQL vs NoSQL Databases
Aspect SQL NoSQL Schema Strict, evolving via migrations Flexible / schema-on-read Joins First-class Denormalized; app-side joins ACID Default Often BASE; some ACID (MongoDB 4+) Scale Vertical + read replicas + sharding Native horizontal Examples Postgres, MySQL, Spanner, Cockroach Mongo, Cassandra, DynamoDB, Redis
2. Understanding ACID Properties
Property Meaning Atomicity All operations succeed or none Consistency Constraints / invariants preserved Isolation Concurrent txns appear serial Durability Committed data survives crash (WAL fsync)
3. Understanding BASE Properties
Property Meaning Basically Available System responds even if stale Soft state State may change without input (replication) Eventual consistency Replicas converge given no new writes
4. Designing Database Replication
Topology Description Use Case Single-leader One primary writes; followers read Most OLTP Multi-leader Writes accepted at multiple sites Multi-region writes Leaderless (quorum) Any node accepts; R+W > N Cassandra, Dynamo Sync mode Strong consistency; high write latency Banking Async mode Low latency; risk of data loss on failover Read replicas
5. Implementing Database Sharding
Step Action Pick shard key High cardinality, even distribution Hash function Consistent hash recommended Routing Smart client or proxy (Vitess, Citus) Cross-shard queries Scatter-gather + aggregate Resharding Online split/merge with double-write
6. Implementing Database Partitioning
Type Postgres Syntax Use Range PARTITION BY RANGE(date)Time-series List PARTITION BY LIST(region)Categorical Hash PARTITION BY HASH(id)Even distribution Composite Range subpartitioned by hash Time + tenant
Example: Time-range partition (Postgres)
CREATE TABLE events (id bigserial , ts timestamptz NOT NULL , payload jsonb) PARTITION BY RANGE (ts); CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ( '2026-01-01' ) TO ( '2026-02-01' ); CREATE INDEX ON events_2026_01 (ts);
7. Implementing Read Replicas
Aspect Detail Replication lag Monitor; route critical reads to primary Routing Driver-level (RW vs RO endpoints) Cascading replicas Reduce primary load Failover Promote replica via Patroni/RDS
8. Designing Read-Write Splitting
Layer Approach Application Two datasources; route by op Driver / ORM @Transactional(readOnly=true) → replica Proxy ProxySQL, MaxScale parse SQL Read-after-write Pin to primary for N seconds after write
9. Designing for Time-Series Data
DB Strength TimescaleDB Postgres extension, hypertables InfluxDB Native TSDB, Flux query Prometheus Pull-based metrics, PromQL ClickHouse Columnar, MergeTree, very fast aggregation Patterns Time-bucketed partitions, downsampling, retention policies
10. Designing Database Backup and Recovery
Type Frequency RPO/RTO Full snapshot Daily RPO 24h Incremental Hourly RPO 1h WAL/binlog archive Continuous RPO seconds (PITR) Cross-region copy Async DR readiness
Warning: Untested backups are not backups. Run quarterly restore drills and verify integrity (checksums, sample queries).
11. Designing Database High Availability
Pattern Tool Streaming replication + failover Patroni, Pacemaker, RDS Multi-AZ Synchronous replicas Quorum commit (Postgres) Group replication MySQL Group Replication, Galera Distributed SQL Spanner, CockroachDB, Yugabyte
12. Designing Multi-Region Database Architecture
Topology Trade-off Primary in one region + async replicas Simple; cross-region reads stale; RPO > 0 on failover Active-active with conflict resolution Multi-region writes; LWW or CRDT Geo-partitioned (Spanner) Data lives near users; strong consistency Read-local, write-global Reads fast; writes go to home region