Database Architecture Principles, Common Schemes, and Consistency Solutions

1. Database Architecture Principles The foundation of a robust database system includes four key attributes: high availability, high performance, data consistency, and scalability.

2. Common Architecture Schemes

Scheme 1: Primary‑Standby – Only the primary handles read/write; the standby is used for failover. High availability is achieved via automatic failover, but performance can become a bottleneck because all traffic hits the primary, and scalability is limited. jdbc:mysql://vip:3306/xxdb Analysis highlights:

High availability: transparent failover.

Performance: primary becomes a bottleneck; read‑heavy workloads suffer.

Consistency: reads and writes go to the primary, so no consistency issues.

Scalability: cannot improve read capacity by adding replicas.

Practical considerations: indexing and caching can mitigate performance limits; sharding can address scalability.

Scheme 2: Dual‑Primary – Two primaries provide services with load balancing. Improves availability (one primary can fail) and roughly doubles performance, but introduces data consistency challenges that require explicit resolution. jdbc:mysql://vip:3306/xxdb Analysis highlights:

High availability: failover to the other primary.

Performance: roughly twice the throughput.

Consistency: potential conflicts; a consistency‑resolution approach is needed.

Scalability: can be extended to three primaries, though added synchronization overhead is a concern.

Scheme 3: Primary‑Replica (Read‑Write Separation) – One primary with multiple replicas. Improves read performance via load‑balanced replicas, but writes are single‑point‑of‑failure and consistency issues persist.

jdbc:mysql://master-ip:3306/xxdb</code>
<code>jdbc:mysql://slave1-ip:3306/xxdb</code>
<code>jdbc:mysql://slave2-ip:3306/xxdb

Analysis highlights:

Availability: primary failure stops writes.

Performance: reads are distributed, overall throughput improves.

Consistency: replicas may lag, causing stale reads.

Scalability: adding replicas boosts read capacity, but increases binlog pull load and synchronization latency.

Scheme 4: Dual‑Primary + Replicas – Combines dual‑primary with replicas for maximum read/write capacity. Offers high availability and performance but inherits consistency and synchronization complexities of both prior schemes.

jdbc:mysql://vip:3306/xxdb</code>
<code>jdbc:mysql://slave1-ip:3306/xxdb</code>
<code>jdbc:mysql://slave2-ip:3306/xxdb

Analysis highlights:

High availability and performance are strong.

Consistency issues remain; require explicit resolution.

Scalability: adding more replicas improves reads, at the cost of longer sync times.

3. Consistency Solutions

Category 1 – Primary/Replica Consistency

Ignore delay if business tolerates it.

Force all reads to the primary (use primary‑standby scheme) and employ caching for read acceleration.

Cache‑assisted reads: generate a cache key based on DB/table/business characteristics; read from cache first, fall back to replica if miss, otherwise read primary.

Semi‑synchronous replication: commit only after replica acknowledges, improving consistency at the cost of higher write latency.

Database middleware (e.g., MyCat) to route reads/writes intelligently, though it adds operational complexity.

Category 2 – DB and Cache Consistency

Typical cache workflow: eviction → write DB → read cache? → read DB → write cache. Inconsistent states arise when step 4 (write cache) lags behind step 3 (read DB). Solutions include setting appropriate TTLs, using write‑through or write‑behind strategies, and ensuring cache invalidation on DB updates.

Key recommendations:

Always set an expiration time on cached entries to avoid stale data.

Consider read‑through caching to keep DB and cache in sync.

Use middleware or custom logic to decide when to read from primary versus replica based on cache hit/miss.

4. Personal Insights

The author reflects on architecture evolution paths, emphasizing that adding sharding, caching, and indexing are common performance boosters, but the ultimate design must align with specific business scenarios. Most production systems still rely on primary‑standby or primary‑replica patterns, with advanced setups reserved for high‑traffic cases.

Remember: an architecture that ignores business context is merely “playing tricks”.