Database Architecture Principles, Common Schemes, and Consistency Solutions

1. Database Architecture Principles

High availability

High performance

Consistency

Scalability

2. Common Architecture Schemes

Scheme 1: Primary‑Backup (Master‑Slave) Architecture

High‑availability analysis: When the primary fails, a keepalive tool automatically switches to the backup, transparent to the business layer.

Performance analysis: All reads and writes go to the primary, creating a bottleneck for read‑heavy workloads; the backup’s resources are under‑utilized (≈50%).

Consistency analysis: All operations on the primary guarantee data consistency.

Scalability analysis: Adding read replicas does not improve write performance; overall scalability is limited.

Practical considerations: Performance can be improved with efficient indexes and caching; scalability can be enhanced by sharding.

Scheme 2: Dual‑Master Architecture (Both Masters Serve Traffic, Load‑Balanced)

High‑availability analysis: If one master fails, the other continues serving traffic without code changes.

Performance analysis: Read/write capacity roughly doubles compared to Scheme 1.

Consistency analysis: Data consistency issues arise; a dedicated consistency solution is required.

Scalability analysis: Adding a third master is possible but adds data‑sync overhead; sharding is recommended for true scalability.

Practical considerations: Consistency can be addressed with distributed ID services; primary‑key conflicts must be managed.

Scheme 3: Primary‑Slave with Read‑Write Separation (One Primary, Multiple Slaves)

High‑availability analysis: The primary is a single point of failure; if it goes down, write operations stop.

Performance analysis: Reads are offloaded to slaves, improving overall throughput; slaves can have different indexing strategies.

Consistency analysis: Replication lag can cause stale reads; consistency solutions are needed.

Scalability analysis: Adding more slaves improves read capacity but increases replication load on the primary.

Practical considerations: Consistency problems remain; no simple solution beyond sharding or stronger replication.

Scheme 4: Hybrid Dual‑Master + Primary‑Slave Architecture

This combines the benefits of Schemes 2 and 3, offering high availability and performance, but introduces additional data‑sync layers that increase latency.

High‑availability: Both masters provide redundancy.

Performance: High, due to load‑balancing and read‑write separation.

Consistency: Still faces data‑sync delays; a consistency solution is required.

Scalability: Can be expanded by adding more slaves, with the same replication overhead as Scheme 3.

3. Consistency Solutions

Category 1 – Master‑Slave Consistency Approaches

Ignore inconsistency if business tolerates delay.

Force all reads to the primary (master‑backup) and use caching to boost read performance.

Read‑through cache: generate a key based on DB/table/business, store in cache with TTL longer than replication lag; read from cache first, fall back to primary if miss.

Semi‑synchronous replication: write returns only after slave acknowledges, increasing latency but ensuring consistency.

Database middleware (e.g., Mycat) to route reads/writes, similar to cache‑first strategy but adds an extra layer.

Category 2 – DB‑Cache Consistency Approaches

Typical cache workflow:

Evict stale cache.

Write to the database.

Read cache? If miss, read from DB.

After DB read, write result back to cache.

To avoid DB‑cache divergence, ensure cache entries have expiration times and consider the following remedies:

Directly ignore if eventual consistency is acceptable.

Read‑through with TTL longer than replication lag.

Use cache‑aside pattern with explicit invalidation on writes.

Employ semi‑sync replication to reduce lag.

Introduce middleware that enforces read‑master policy.

4. Personal Insights

Architecture Evolution Paths

Evolution 1: Scheme 1 → Scheme 1 + sharding → Scheme 2 + sharding → Scheme 4 + sharding

Evolution 2: Scheme 1 → Scheme 1 + sharding → Scheme 3 + sharding → Scheme 4 + sharding

Evolution 3: Scheme 1 → Scheme 2 → Scheme 4 → Scheme 4 + sharding

Evolution 4: Scheme 1 → Scheme 3 → Scheme 4 → Scheme 4 + sharding

Key Takeaways

Adding cache and proper indexing are universal ways to improve database performance.

Sharding brings significant benefits but also introduces complexity.

Choosing an architecture must consider specific business scenarios; many companies still rely on simple master‑backup setups.

Never adopt an architecture without evaluating its fit for the actual workload.