Choosing the Right Database: CAP Theorem and Practical Use Cases

Scenario context

Database selection is a trade-off between consistency, availability, and scalability. By using the CAP Theorem as a compass and matching your data access patterns to the right storage engine (Relational, Document, KV, or Wide-Column), you can build systems that scale without collapsing under architectural debt.

Observable outcome: It works beautifully for the first week.

Stage 2 — Core Mechanics: Sharding and Partitioning

• How do databases actually scale?
• They all eventually run out of room on a single disk.
• The mechanic for solving this is Partitioning (often called Sharding).
• Vertical Partitioning: Putting different tables on different servers (e.g., Users table on DB1, Orders table on DB2).

Observable outcome: Vertical Partitioning: Putting different tables on different servers (e.g., Users table on DB1, Orders table on DB2).

Stage 3 — Visualizing the Flow of Database Sharding

• architecture { "version": 1, "title": "Visualizing the Flow of Database Sharding - Storage Topology", "description": "Multi-tier caching and replicated storage pipeline ensuring fast reads and transactional consistency.…
• The application doesn't need to know where the data is; it sends the request to a Proxy.
• Based on the Shard Key (in this case, the user's region), the Proxy routes the request to the correct physical database instance.
• This allows the system to scale infinitely by simply adding more shards.

graph TD
    App[Application Layer] --> Proxy[Database Proxy/Router]
    Proxy -->|Shard Key: US| DB_US[Shard 1: US Region]
    Proxy -->|Shard Key: EU| DB_EU[Shard 2: EU Region]
    Proxy -->|Shard Key: ASIA| DB_ASIA[Shard 3: ASIA Region]

    subgraph Storage_Layer
        DB_US
        DB_EU
        DB_ASIA
    end

Observable outcome: This allows the system to scale infinitely by simply adding more shards.

Stage 4 — Deep Dive: CAP Theorem and The Partition Choice

• The CAP Theorem is the fundamental law of distributed databases.
• 🛡️ The Internals: CP vs AP - CP (Consistency + Partition Tolerance): If the network breaks, the database will stop accepting writes to ensure that it never serves an incorrect value.
• MongoDB and HBase are typically CP.
• • AP (Availability + Partition Tolerance): If the network breaks, the database keeps working, but nodes might temporarily disagree.

Observable outcome: - AP (Availability + Partition Tolerance): If the network breaks, the database keeps working, but nodes might temporarily disagree.

Stage 5 — Advanced Concepts: NewSQL and Distributed SQL

• A new category called NewSQL (like TiDB or Google Spanner ) attempts to provide the best of both worlds: the SQL interface and ACID transactions of a relational DB with the horizontal scale of NoSQL.
• They achieve this using consensus algorithms like Paxos or Raft to manage state across many nodes.

Observable outcome: They achieve this using consensus algorithms like Paxos or Raft to manage state across many nodes.

Stage 6 — Practical Example: TiDB (NewSQL)

• TiDB is the leading open-source Distributed SQL database.
• It looks like MySQL to your app but scales like Cassandra.
• Example 1: Horizontal Scaling In a traditional DB, you'd be stuck.
• In TiDB, you just add more TiKV nodes.

# Adding storage capacity in TiDB is a one-command operation
tiup cluster scale-out my-cluster tikv-node-info.yaml

-- This transaction is distributed across multiple storage nodes
-- but remains atomic and consistent.
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'Sarah';
UPDATE accounts SET balance = balance + 100 WHERE id = 'James';
COMMIT;

Observable outcome: In TiDB, you just add more TiKV nodes.

End-to-end outcome

After working through the stages, you should be able to explain:

• Vertical scaling (bigger RDS instance) takes you further than you think and is much simpler.
• Your code still expects a certain structure.
• • SQL for complexity and integrity.
• • NoSQL for scale and flexibility.

Alternatives considered

The Basics of SQL vs. NoSQL

The most common divide is between Relational (SQL) and Non-Relational (NoSQL) databases.

Decision Guide: The Database Compass

Decision Guide: The Database Compass

Failure and degraded modes

Why Your Database Choice is a Trade-off Decision

• Detection: In modern system design, there is no "best" database.
• User or operator impact: In modern system design, there is no "best" database.

Trade-offs & Failure Modes

• Detection: Denormalized: SQL thrives on normalization (no duplicate data).
• User or operator impact: Denormalized: SQL thrives on normalization (no duplicate data).