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 |