System Design & Distributed Databases: A Practical Guide to PACELC, CAP Theorem, ACID, Replication, Quorum, and Modern Database Selection

This preprint provides a comprehensive technical reference on distributed database architecture and modern system design. It explores the evolution from single-server architectures to highly distributed systems, explaining the trade-offs introduced by replication, network partitions, consistency models, and scalability. The publication presents an in-depth analysis of the CAP Theorem and the PACELC model, demonstrating how availability, consistency, latency, and partition tolerance influence architectural decisions in real-world systems. Fifteen production-grade distributed databases are examined, including Apache Cassandra, MongoDB, Amazon DynamoDB, CockroachDB, Google Spanner, PostgreSQL, Redis, etcd, Riak, Neo4j, Elasticsearch, FoundationDB, ScyllaDB, Couchbase, and YugabyteDB. Each database is analyzed regarding architecture, replication strategy, consensus algorithms, consistency guarantees, operational trade-offs, scalability, production use cases, and typical workloads. Special attention is given to Apache Cassandra and tunable consistency, providing practical explanations of consistency levels (ANY, ONE, TWO, THREE, QUORUM, LOCAL_QUORUM, and ALL), quorum calculations, replication factors, and production-ready Java examples illustrating how applications can dynamically balance consistency, availability, and latency according to business requirements. The publication also discusses ACID transactions, database-per-service patterns in microservices, distributed architecture principles, cloud-native data platforms, and practical decision frameworks for selecting the appropriate database technology for different business scenarios. Designed for software architects, engineering managers, senior software engineers, technical leaders, and graduate students, this work serves as both an educational reference and a practical decision guide for designing scalable, resilient, and cloud-native distributed systems.