Database Architecture Guide: Principles, Patterns, and Consistency Solutions

1. Database Architecture Principles

High availability

High performance

Scalability

Consistency

2. Common Architecture Schemes

Scheme 1: Primary‑Standby (only primary handles read/write, standby for failover)

JDBC URL: jdbc:mysql://vip:3306/xxdb

High availability analysis: If primary fails, keepalive automatically switches to standby, transparent to the business layer.

High performance analysis: All reads and writes go to the primary, creating a bottleneck for read‑heavy workloads.

Consistency analysis: No consistency issues because all operations use the primary.

Scalability analysis: Cannot improve read performance by adding replicas.

Practical implementation: Improve performance with efficient indexes and caching; improve scalability via sharding.

Scheme 2: Dual‑Primary (both primaries provide service with load balancing)

JDBC URL: jdbc:mysql://vip:3306/xxdb

High availability analysis: Failure of one primary does not affect the other; transparent to the business layer.

High performance analysis: Read/write performance roughly doubles compared to Scheme 1.

Consistency analysis: Data consistency issues arise; see consistency solutions.

Scalability analysis: Can extend to three primaries but not recommended due to added synchronization latency.

Practical implementation: Two main issues: data consistency (solved by consistency solutions) and primary‑key conflicts, which can be addressed by a distributed ID generation service.

Scheme 3: Primary‑Replica (one primary, multiple replicas, read/write separation)

JDBC URLs:

jdbc:mysql://master-ip:3306/xxdb

jdbc:mysql://slave1-ip:3306/xxdb

jdbc:mysql://slave2-ip:3306/xxdb

High availability analysis: Primary is a single point of failure; replicas are highly available but cannot write if the primary fails.

High performance analysis: Read‑heavy workloads benefit from replica reads; primary can use different indexes than replicas.

Consistency analysis: Data consistency issues exist; see consistency solutions.

Scalability analysis: Adding replicas improves read performance but increases load on the primary for binlog replication.

Practical implementation: Two main issues: data consistency (solved by consistency solutions) and primary single‑point failure (no simple solution presented).

Scheme 4: Dual‑Primary + Primary‑Replica (seems perfect)

JDBC URLs:

jdbc:mysql://vip:3306/xxdb

jdbc:mysql://slave1-ip:3306/xxdb

jdbc:mysql://slave2-ip:3306/xxdb

High availability analysis: High availability.

High performance analysis: High performance.

Consistency analysis: Data consistency issues; see consistency solutions.

Scalability analysis: Can add replicas to improve reads, but suffers the same synchronization latency as Scheme 2.

Practical implementation: Similar to Scheme 2, but the added synchronization layer increases data delay.

3. Consistency Solutions

Category 1: Primary‑Replica Consistency

Note: The circled area in the diagram shows data synchronization (MySQL binlog replication). During replication delay, primary and replica may be inconsistent, causing stale reads.

Possible solutions:

Ignore the delay if the business can tolerate it.

Force reads from the primary (use primary‑standby scheme or code‑level routing).

Cache a key generated from DB/table/business characteristics with a TTL longer than the replication delay; use the cache to decide whether to read the primary or replica.

Introduce a database middleware (e.g., Mycat) to route reads, though it adds cost and complexity.

Category 2: DB‑Cache Consistency

Typical cache workflow: evict cache → write DB → read cache? → read DB → write cache.

In high‑concurrency scenarios, a read may hit stale cache if it occurs between cache eviction and DB write. Solutions include delaying cache eviction after DB write, using a local queue to serialize reads/writes, or employing distributed locks (e.g., Zookeeper) to coordinate access.

Note: Always set an expiration time for cached entries.

4. Personal Insights

Architecture Evolution

Evolution 1: Scheme 1 → Scheme 1 + sharding.

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

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

Note: Scheme 4 is generally not used.

Key Takeaways

Adding cache and indexes is a universal way to boost DB performance.

Sharding brings huge benefits but also introduces challenges.

Choose architecture based on specific business scenarios; most use Scheme 1 or Scheme 1 + sharding, while read‑heavy workloads often adopt Scheme 3 with read/write separation and sharding.

Remember: Architecture without business context is ineffective.