Distributed Database Architecture

Distributed databases trade simplicity for scalability. A single PostgreSQL instance handles millions of rows effortlessly, but at some point you need more throughput, more storage, or more availability than one server provides. That’s when you distribute data across multiple nodes — and everything gets harder.

---

CAP Theorem in Practice

Property	What It Means	Examples
Consistency	All reads return most recent write	PostgreSQL, CockroachDB, Spanner
Availability	Every request gets a response	Cassandra, DynamoDB
Partition Tolerance	System works despite network failures	All distributed systems (mandatory)

You can only pick two (realistically: CP or AP):

Choice	Trade-off	Use Case
CP (Consistent + Partition-tolerant)	Unavailable during partition	Financial transactions, inventory
AP (Available + Partition-tolerant)	May return stale data during partition	Social feeds, analytics, caching

---

Replication Topologies

Topology	Consistency	Availability	Latency	Use Case
Single-leader	Strong	Medium	Low (local reads)	Most applications
Multi-leader	Eventual	High	Low (local writes)	Multi-region apps
Leaderless	Tunable (quorum)	Highest	Varies	High-availability systems

---

Sharding Strategies

Strategy	How	Pros	Cons
Range	Shard by key range (A-M, N-Z)	Range queries efficient	Hot spots (uneven distribution)
Hash	Hash(key) → shard	Even distribution	Range queries cross all shards
Directory	Lookup table maps key → shard	Flexible	Lookup table is single point of failure
Geographic	Shard by region	Data locality, compliance	Cross-region queries expensive

---

Database Selection Guide

Database	Type	Consistency	Best For
PostgreSQL	Relational (single-node)	Strong	Most workloads < 1TB
CockroachDB	Distributed SQL	Strong (serializable)	Global SQL, strong consistency
Vitess	Sharded MySQL	Strong (per-shard)	Scaling MySQL horizontally
Cassandra	Wide-column	Tunable	High-write, time-series
DynamoDB	Key-value / document	Eventual (or strong per-item)	Serverless, predictable perf
MongoDB	Document	Tunable (causal)	Document workloads, flexible schema
ScyllaDB	Wide-column (C++)	Tunable	Cassandra-compatible, lower latency

---

Anti-Patterns

Anti-Pattern	Problem	Fix
Shard before you need to	Complexity without benefit	Scale up first (bigger instance), shard only when necessary
Cross-shard transactions	Slow, complex, failure-prone	Design schema to keep related data on same shard
Distributed joins	Scatter-gather across all shards	Denormalize, materialize views
Ignoring data locality	User data spread across distant shards	Shard by tenant/user, geo-shard if multi-region

---

Checklist

• Data volume and growth assessed (do you actually need distribution?)
• CAP trade-off chosen: CP or AP based on business requirements
• Replication topology selected and tested for failure scenarios
• Sharding key chosen to avoid hot spots and cross-shard queries
• Conflict resolution strategy for multi-leader/leaderless
• Backup and recovery tested for distributed setup
• Monitoring: replication lag, shard balance, query routing

---

:::note[Source] This guide is derived from operational intelligence at Garnet Grid Consulting. For database architecture consulting, visit garnetgrid.com. :::