Choosing the Right Database Architecture: Principles, Schemes, and Consistency Solutions

Database Architecture Principles

1. High availability 2. High performance 3. Consistency 4. Scalability

Common Architecture Schemes

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

jdbc:mysql://vip:3306/xxdb

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

High‑performance analysis: All reads and writes hit the primary, creating a bottleneck; read‑heavy workloads suffer, and the backup’s resources are under‑utilized.

Consistency analysis: All operations use the primary, so no data‑consistency issues.

Scalability analysis: Adding replicas does not improve read performance.

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

Scheme 2: Dual‑Master Architecture (Load‑Balanced)

jdbc:mysql://vip:3306/xxdb

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

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

Consistency analysis: Data‑consistency problems may arise; see the consistency solutions section.

Scalability analysis: Extending to three masters is possible but adds synchronization overhead; instead, consider Scheme 4.

Practical deployment: Consistency issues can be mitigated with distributed ID services; performance can be boosted with indexes and caching.

Scheme 3: Master‑Slave with Read/Write Separation

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

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

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

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

High‑performance analysis: Reads are offloaded to slaves, improving overall throughput; slaves can have different indexes for specific use cases.

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

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

Practical deployment: Consistency issues can be solved with the methods described later; primary failure remains a challenge.

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

jdbc:mysql://vip:3306/xxdb

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

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

High‑availability analysis: High availability is achieved through multiple primaries and replicas.

High‑performance analysis: High performance due to parallel primary handling.

Consistency analysis: Replication lag still introduces consistency gaps.

Scalability analysis: Adding more slaves can increase read capacity, but replication overhead grows.

Practical deployment: Data latency is higher than in Scheme 2 because of extra synchronization layers.

Consistency Solutions

1. Primary‑Replica Consistency

Data synchronization occurs when replicas pull binlog from the primary; during this window, reads from replicas may return stale data. Solutions include:

Ignore the delay if the business can tolerate it.

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

Read‑from‑primary only when a cache miss occurs; otherwise read from cache, reducing stale reads.

Use semi‑synchronous replication so writes return only after replicas confirm receipt, at the cost of higher latency.

Introduce a database middleware (e.g., Mycat) to manage routing and consistency, though it adds complexity.

2. DB‑Cache Consistency

Typical cache workflow:

Evict cache.

Write to database.

Read cache? If miss, read from database.

After reading from database, write back to cache.

If step 4 executes before step 3’s synchronization, cache may hold dirty data. To avoid this:

Set an expiration time on cache entries to limit stale data windows.

Use a “read‑primary” strategy: generate a cache key based on table and business characteristics; on reads, check cache first—if hit, read primary; if miss, read replica.

Personal Insights

Architecture Evolution

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

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

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

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

Key Takeaways

Adding cache and indexes is a universal way to improve database performance.

Sharding brings huge benefits but also introduces new challenges (see “MySQL database common sharding solutions”).

Choose architecture based on concrete business scenarios; many companies still rely on simple master‑backup setups.

Remember: an architecture that ignores business context is ineffective.