Choosing the Right Database Architecture: Pros, Cons, and Consistency Solutions

Database Architecture Principles

High Availability

High Performance

Consistency

Scalability

Common Architecture Schemes

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

High Availability Analysis : If the primary fails, a keepalive tool automatically switches to the backup, transparent to the business layer.

High Performance Analysis : All reads/writes go to 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 data consistency is guaranteed.

Scalability Analysis : Adding replicas does not improve read performance, limiting overall scalability.

Practicality Analysis : Performance can be improved with efficient indexes and caching; scalability issues can be mitigated by sharding.

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

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 issues arise; see the consistency solutions section.

Scalability Analysis : Adding a third master is possible but adds synchronization overhead; a fourth scheme is recommended for better scalability.

Practicality Analysis : Consistency problems can be solved with distributed ID services; performance remains solid.

Scheme 3: Master‑Slave with Read/Write Separation

High Availability Analysis : Primary is a single point of failure; if it goes down, write services stop.

High Performance Analysis : Reads are offloaded to slaves, improving overall performance; slaves can have different indexes than the primary.

Consistency Analysis : Data consistency issues exist; see the consistency solutions section.

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

Practicality Analysis : Consistency solutions are needed; no clear remedy for primary single‑point failure.

Scheme 4: Dual‑Master + Master‑Slave Hybrid

High Availability : Both masters provide service; failure of one does not affect the other.

High Performance : Performance is high due to dual masters.

Consistency : Still faces consistency challenges; see solutions.

Scalability : Can add slaves to boost reads, but synchronization overhead grows.

Practicality : Additional replication layer increases data latency.

Consistency Solutions

1. Primary‑Backup Consistency Solutions

Ignore the lag if business tolerates delayed data.

Force reads from the primary (use caching to boost read performance).

Read‑from‑primary strategy: cache keys with TTL longer than replication lag; read from cache first, fallback to primary if miss.

Semi‑synchronous replication: wait for master‑slave sync before acknowledging writes.

Introduce a database middleware (e.g., MyCAT) to route reads intelligently.

2. DB‑Cache Consistency Solutions

Set cache entries with expiration to avoid stale data.

Use write‑through or write‑behind patterns to keep DB and cache in sync.

Employ read‑through caching with versioning to detect and resolve inconsistencies.

Personal Insights

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

Sharding brings huge benefits but also introduces new challenges.

Choose architecture based on concrete business scenarios; many companies still rely on primary‑backup or primary‑backup + sharding.

Remember: an architecture that ignores business context is ineffective.