Database Design • Relational vs NoSQLMedium⏱️ ~3 min
Consistency, Availability, and Latency Tradeoffs
Relational databases with a single primary provide strong consistency but face unavailability during primary failover, typically lasting seconds. Within a region, commit latencies are low and predictable because all writes go through one coordinator. Cross shard or cross region transactions add coordination latency through mechanisms like two phase commit (2PC), often adding tens to hundreds of milliseconds. Amazon Aurora commits succeed on a quorum of 4 out of 6 replicas across 3 availability zones, achieving single digit millisecond commit latency with failover generally completing in 10 to 30 seconds.
NoSQL systems using leaderless replication with quorum reads and writes prioritize high availability and write anywhere capability. They accept eventual consistency anomalies to preserve low latencies at high throughput with lower coordination overhead. Amazon DynamoDB uses leaderless replication with quorum operations, delivering single digit millisecond latencies at scale. Modern DynamoDB global tables replicate across regions in sub second to seconds.
Distributed SQL systems like Google Spanner bridge this gap by providing ACID guarantees with scale out capability, but at the cost of higher write latencies. Spanner uses external consistency across regions via Paxos consensus and tightly bounded clock uncertainty. Within a single region, commits are often 10 to 20 ms; multi region topologies add 50 to 150 ms depending on inter region Round Trip Time (RTT). Azure Cosmos DB offers tunable consistency with five levels ranging from strong to eventual, with Service Level Agreement (SLA) backed 99.999% availability for multi region writes and P99 latencies under 10 ms for 1 KB reads within a region.
💡 Key Takeaways
•Single primary relational systems provide strong consistency with 10 to 30 ms local latencies but face unavailability during failover (10 to 30 seconds typical)
•Leaderless NoSQL achieves high availability and single digit millisecond latencies (often under 5 ms) by accepting eventual consistency and moving coordination to application layer
•Distributed SQL like Google Spanner provides both ACID and horizontal scale but pays with higher write latencies: 10 to 20 ms within region, 50 to 150 ms across regions due to consensus protocols
•Azure Cosmos DB offers tunable consistency with five levels, allowing applications to choose specific tradeoffs per operation with SLA backed 99.999% availability for multi region writes
•Cross shard or cross region transactions in any system add coordination overhead via two phase commit or consensus, increasing latency and reducing availability during coordinator failures
📌 Examples
Amazon Aurora: 6 way replication across 3 availability zones with quorum (4 of 6) commits achieving single digit millisecond latency and 10 to 30 second failover
Google Spanner for Ad systems: external consistency with commit wait adding 10 to 20 ms within region, 50 to 150 ms multi region for globally consistent transactions
DynamoDB global tables: leaderless replication across regions converging in sub second to seconds, delivering single digit millisecond local latencies at scale
Financial transactions use relational with strong consistency; session state and activity feeds use NoSQL with session consistency for low latency with read your writes guarantees