Which Database Architecture Best Balances Availability, Performance, and Consistency?

Database Architecture Principles

Four core criteria guide the design of a robust database system: high availability, high performance, data consistency, and scalability.

Common Architecture Schemes

Primary‑standby (single master, one standby)

Dual‑primary (two masters with load balancing)

Master‑slave with read/write separation (one master, multiple slaves)

Hybrid dual‑primary + master‑slave (two masters plus slaves)

Scheme 1: Primary‑Standby

High‑availability analysis: If the primary fails, a keepalive tool automatically switches to the standby, transparent to the application.

Performance analysis: All reads and writes go to the primary, creating a bottleneck; read‑heavy workloads suffer most. The standby’s resources are under‑utilized.

Consistency analysis: All operations target the primary, so no consistency issues arise.

Scalability analysis: Adding slaves does not improve read performance because reads still hit the primary.

Practical considerations: Performance can be improved with better indexes and caching; scalability requires sharding.

jdbc:mysql://vip:3306/xxdb

Scheme 2: Dual‑Primary

High‑availability analysis: If one primary fails, the other continues serving traffic without interruption.

Performance analysis: Read/write load is split between two masters, roughly doubling throughput.

Consistency analysis: Data consistency issues can appear and must be addressed.

Scalability analysis: Adding more masters is possible but not recommended due to added synchronization overhead.

jdbc:mysql://vip:3306/xxdb

Scheme 3: Master‑Slave Read/Write Separation

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

Performance analysis: Reads are distributed across slaves, improving read throughput; writes still go to the primary.

Consistency analysis: Inconsistent reads can occur during replication lag.

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

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

Scheme 4: Hybrid Dual‑Primary + Master‑Slave

Combines the benefits of dual‑primary and master‑slave setups, offering high availability and read scalability, but introduces an extra synchronization layer that can increase latency.

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

Consistency‑Resolution Strategies

Ignore the delay if the business can tolerate eventual consistency.

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

Use a cache with a TTL longer than the replication lag; read from cache first, fall back to the primary if a miss occurs.

Enable semi‑synchronous replication so writes return only after the slave has confirmed receipt.

Introduce a database middleware layer (e.g., Mycat) to manage routing and consistency.

Cache Consistency Workflow

Evict the stale cache entry.

Write the new data to the database.

Read from the cache; if a miss, read from the database.

After reading from the database, write the fresh value back to the cache with an appropriate TTL.

Personal Insights

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

Sharding (splitting databases/tables) yields massive scalability gains but introduces its own challenges.

Choosing an architecture must be driven by concrete business scenarios; most workloads favor the primary‑standby pattern with optional sharding.

Never deploy an architecture without considering its impact on consistency and availability.

Architecture Evolution Paths

Scheme 1 → Scheme 1 + sharding → Scheme 2 + sharding → Scheme 4 + sharding

Scheme 1 → Scheme 1 + sharding → Scheme 3 + sharding → Scheme 4 + sharding

Scheme 1 → Scheme 2 → Scheme 4 → Scheme 4 + sharding

Scheme 1 → Scheme 3 → Scheme 4 → Scheme 4 + sharding