SQL vs NoSQL

The SQL vs NoSQL decision is one of the most fundamental choices in system design. Rather than a binary choice, modern systems often use both.

Architecture Differences

SQL databases (PostgreSQL, MySQL, SQL Server) organize data into tables with predefined schemas, enforcing relationships through foreign keys and constraints. They use B-tree indexes, query optimizers, and the relational algebra to process complex queries efficiently.

NoSQL databases come in four main categories: document stores (MongoDB), key-value stores (Redis, DynamoDB), wide-column stores (Cassandra), and graph databases (Neo4j). Each is optimized for specific data models and access patterns.

Performance Characteristics

SQL databases excel when queries span multiple tables with joins, aggregations, and subqueries. A well-indexed relational database can handle complex analytical queries that would be impossible or very slow in NoSQL. NoSQL databases dominate for single-entity lookups by key, range scans within a partition, and workloads with predictable access patterns.

The key insight for system design interviews is that performance depends on matching your data model to your access patterns, not on the database category itself.

Trade-offs

The CAP theorem constrains distributed databases: you can prioritize consistency and partition tolerance (CP) or availability and partition tolerance (AP). Most SQL databases choose CP, while many NoSQL databases offer tunable consistency between CP and AP.

SQL databases provide ACID transactions that simplify application logic for complex business operations. NoSQL databases typically offer single-document or single-partition atomicity, pushing cross-entity consistency to the application layer.

When to Use Both (Polyglot Persistence)

Modern architectures often use multiple databases. A typical pattern: PostgreSQL for transactional data, Redis for caching, Elasticsearch for search, and Cassandra for time-series data. This polyglot persistence approach matches each workload to the best tool.

Real-World Examples

Uber uses MySQL for trip data, Cassandra for real-time analytics, and Redis for caching. Netflix uses Cassandra for user activity, PostgreSQL for billing, and Elasticsearch for search. The key is understanding your data access patterns.

Prepare for database questions with our interview question bank and system design examples. See pricing for full access.