Scalable Data Storage: SQL vs NoSQL
Choose between relational databases, key-value stores, document databases, and wide-column stores based on access patterns, consistency, and scale requirements.
Scalable Data Storage: SQL vs NoSQL is a free DSA Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the DSA Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Storage Choice Is a Trade-Off
Choosing a data store is one of the most consequential decisions in system design. No database is universally best — each type optimises for different access patterns, consistency guarantees, and scale characteristics. Getting this wrong in production causes months of painful migration work.
In interviews, interviewers probe whether you understand the fundamental differences and can match a storage engine to a problem's requirements. The question is never 'which is better?' but 'which is better for this specific workload?' Always justify your choice with specific requirements.
# Storage decision matrix summary
factors = [
'Data structure (tabular, documents, key-value, graph, time-series)',
'Read vs write ratio (read-heavy, write-heavy, balanced)',
'Query patterns (point lookups, range scans, aggregations, joins)',
'Consistency requirements (ACID vs eventual consistency)',
'Scale requirements (single node, sharding, global distribution)',
'Latency requirements (milliseconds vs microseconds)',
'Team familiarity and operational complexity',
]
print('Key factors for storage selection:')
for f in factors:
print(f' - {f}')Relational Databases (SQL): Strengths
Relational databases (PostgreSQL, MySQL, SQLite) store data in tables with fixed schemas and support ACID transactions — Atomicity, Consistency, Isolation, Durability. They excel at complex queries with joins, aggregations, and filters, making them ideal for structured data with well-defined relationships.
Key strengths: complex multi-table queries via SQL, foreign key constraints for data integrity, powerful indexing (B-tree, hash, full-text), mature ecosystem with replication and backups. Use SQL when your data is highly relational, consistency is critical, and queries are complex and varied.
# SQL excels at: complex queries, joins, transactions
# Example: find top 5 products by revenue this month
sql_query = '''
SELECT p.name, SUM(oi.quantity * oi.price) AS revenue
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= DATE_TRUNC('month', NOW())
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 5;
'''
print('SQL shines for relational queries with JOINs:')
print(sql_query)
print('ACID guarantees example (transfer $100 between accounts):')
transfer_sql = '''
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- either both succeed or neither does
'''
print(transfer_sql)SQL Scaling Challenges
SQL databases scale vertically naturally (bigger server, more CPU/RAM) but horizontal scaling is complex. Read replicas handle read-heavy workloads by routing reads to replica nodes and writes to the primary. But write throughput is limited to a single primary node unless you add sharding.
Sharding partitions data across multiple DB instances by a shard key (e.g., user_id mod N). This achieves write scale but breaks cross-shard joins and transactions — two of SQL's strongest features. Most web applications outgrow single-node SQL at around 5-10 TB of data or ~100K writes/second.
# SQL scaling strategies
strategies = {
'Read replicas': {
'how': 'One primary (writes), multiple replicas (reads)',
'scales': 'Read throughput (10x+)',
'limit': 'Write throughput still bounded by single primary',
},
'Connection pooling (PgBouncer)': {
'how': 'Pool of persistent DB connections shared among app servers',
'scales': 'Connection count (PostgreSQL max ~500 connections)',
'limit': 'Does not increase query throughput',
},
'Horizontal sharding': {
'how': 'Partition rows by shard key across N database instances',
'scales': 'Both reads and writes (N×)',
'limit': 'Cross-shard joins and transactions broken; complex routing',
},
'CQRS': {
'how': 'Separate write model (SQL) from read model (denormalised/NoSQL)',
'scales': 'Optimise each path independently',
'limit': 'Eventual consistency between write and read models',
},
}
for strategy, info in strategies.items():
print(f'{strategy}:\n How: {info["how"]}\n Scales: {info["scales"]}\n Limit: {info["limit"]}\n')Key-Value Stores: Redis and DynamoDB
Key-value stores store data as key → value pairs with O(1) read and write on the key. They sacrifice query flexibility for extreme performance and horizontal scalability. Redis (in-memory) achieves microsecond latency; DynamoDB (managed) achieves single-digit millisecond latency with automatic scaling to any throughput.
Use key-value stores for: session storage, caching, feature flags, URL shortener mappings, shopping carts, real-time leaderboards. Do not use when you need: complex queries, relations between entities, or ad-hoc filtering — you can only look up by exact key.
# Key-value store use cases
kv_operations = {
'GET key': 'O(1) point lookup — the core operation',
'SET key value': 'O(1) insert or update',
'DEL key': 'O(1) delete',
'EXPIRE key ttl': 'Set time-to-live; key auto-deleted after ttl seconds',
'INCR key': 'Atomic increment — useful for counters and rate limiting',
'LPUSH/LRANGE': 'List operations — useful for queues and recent-items feeds',
'ZADD/ZRANGE': 'Sorted set — leaderboards, rate limiting with sliding window',
}
print('Redis operation set:')
for op, desc in kv_operations.items():
print(f' {op:25s}: {desc}')
print('\nDynamoDB vs Redis:')
print(' Redis: microsecond latency, in-memory, needs persistence config')
print(' DynamoDB: single-digit ms, managed, auto-scaling, durable by default')Document Stores: MongoDB
Document stores (MongoDB, Couchbase) store data as JSON-like documents, allowing flexible schemas — fields can differ between documents in the same collection. They support indexing on any field and reasonably rich queries (filters, projections, aggregations), though multi-document transactions are more limited than SQL.
Document stores fit applications where: the data shape varies per entity (user profiles with different attributes), rapid schema evolution is expected (startups changing data models frequently), or read patterns are dominated by retrieving whole entities rather than joining across tables.
# MongoDB document example
user_doc = {
'_id': 'user123',
'name': 'Alice',
'email': 'alice@example.com',
'preferences': {
'theme': 'dark',
'language': 'en',
'notifications': ['email', 'push']
},
'addresses': [
{'type': 'home', 'city': 'Berlin', 'country': 'DE'},
{'type': 'work', 'city': 'Munich', 'country': 'DE'}
],
'subscription_tier': 'pro',
# Note: not all users have all fields -- flexible schema!
}
import json
print('Document structure (flexible schema):')
print(json.dumps(user_doc, indent=2))
print('\nDocument store strengths:')
print(' - Nested/array fields without joins')
print(' - Flexible schema (different fields per document)')
print(' - Scales horizontally by sharding on _id')Wide-Column Stores: Cassandra
Wide-column stores (Apache Cassandra, HBase) store data in rows and columns but allow each row to have a different set of columns. They are designed for massive write throughput distributed across many nodes, with eventual consistency as the default. Cassandra achieves linear write scale — doubling nodes doubles write throughput.
The trade-off: queries must be designed around the partition key. You cannot filter or sort on arbitrary columns efficiently — you must define the query pattern first, then design the table to match it. This is the opposite of SQL's 'design data, then write any query' approach.
# Cassandra table design for time-series events
# Design around the query: 'give me all events for user X, most recent first'
cassandra_table = '''
CREATE TABLE user_events (
user_id UUID,
event_time TIMESTAMP,
event_type TEXT,
metadata MAP<TEXT, TEXT>,
PRIMARY KEY (user_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);
-- Query (matches partition key exactly):
SELECT * FROM user_events WHERE user_id = ? LIMIT 100;
'''
print('Cassandra wide-column design:')
print(cassandra_table)
print('Properties:')
print(' - user_id = partition key (all rows for one user on same node)')
print(' - event_time = clustering key (sorted within partition)')
print(' - Very fast writes: append-only, no locking')
print(' - Cannot query by event_type alone (no partition key)')CAP Theorem: Consistency, Availability, Partition Tolerance
The CAP theorem states that a distributed system can guarantee at most two of: Consistency (every read returns the latest write), Availability (every request gets a non-error response), and Partition tolerance (system continues operating despite network partitions).
Since network partitions always occur in distributed systems, the real choice is between CP (prioritise consistency, may reject requests during partition) and AP (prioritise availability, may return stale data). SQL databases are generally CP; Cassandra and DynamoDB are AP (eventual consistency by default). Redis Cluster is CP.
# CAP theorem applied to common databases
databases = {
'PostgreSQL (single node)': {'C': True, 'A': True, 'P': False, 'note': 'Not distributed; CA'},
'PostgreSQL (multi-AZ)': {'C': True, 'A': False, 'P': True, 'note': 'CP: primary fails over, brief downtime'},
'MySQL Cluster': {'C': True, 'A': False, 'P': True, 'note': 'CP'},
'Cassandra': {'C': False, 'A': True, 'P': True, 'note': 'AP: eventual consistency default'},
'DynamoDB (default)': {'C': False, 'A': True, 'P': True, 'note': 'AP: eventual consistency'},
'DynamoDB (strong read)': {'C': True, 'A': False, 'P': True, 'note': 'CP: strongly consistent reads'},
'Redis Cluster': {'C': True, 'A': False, 'P': True, 'note': 'CP'},
'MongoDB (default)': {'C': True, 'A': False, 'P': True, 'note': 'CP: reads from primary'},
}
for db, caps in databases.items():
c_str = 'C' if caps['C'] else '-'
a_str = 'A' if caps['A'] else '-'
p_str = 'P' if caps['P'] else '-'
print(f'{db:35s} [{c_str}{a_str}{p_str}] {caps["note"]}')Choosing Storage: A Decision Framework
A practical decision framework for storage choice in interviews:
- Need ACID transactions? → Relational DB (PostgreSQL, MySQL)
- Need sub-millisecond lookups or caching? → Key-value store (Redis)
- Need flexible schema or document-oriented data? → Document store (MongoDB)
- Need massive write throughput (>100K/sec) with time-series or event data? → Wide-column (Cassandra)
- Need global distribution with managed scaling? → DynamoDB or Cosmos DB
- Need graph traversals? → Graph DB (Neo4j)
# Decision tree in code form
def choose_storage(needs_acid, high_write_throughput, flexible_schema,
sub_ms_latency, graph_queries, global_scale):
if sub_ms_latency:
return 'Redis (in-memory key-value)'
if graph_queries:
return 'Neo4j (graph database)'
if needs_acid:
return 'PostgreSQL / MySQL (relational)'
if high_write_throughput and not flexible_schema:
return 'Cassandra (wide-column, write-optimised)'
if flexible_schema:
return 'MongoDB (document store)'
if global_scale:
return 'DynamoDB or Cosmos DB (managed global KV/document)'
return 'PostgreSQL (safe default for most web apps)'
# Example scenarios
scenarios = [
{'needs_acid': True, 'high_write_throughput': False, 'flexible_schema': False,
'sub_ms_latency': False, 'graph_queries': False, 'global_scale': False},
{'needs_acid': False, 'high_write_throughput': True, 'flexible_schema': False,
'sub_ms_latency': False, 'graph_queries': False, 'global_scale': False},
{'needs_acid': False, 'high_write_throughput': False, 'flexible_schema': False,
'sub_ms_latency': True, 'graph_queries': False, 'global_scale': False},
]
for s in scenarios:
print(f'{choose_storage(**s)}')Polyglot Persistence: Using Multiple Stores
Production systems rarely use a single database for everything. Polyglot persistence means using the right storage technology for each part of the system. A typical web application might use: PostgreSQL for authoritative user and order data, Redis for session storage and caching, Elasticsearch for full-text search, S3 for file storage, and Cassandra for event logs and analytics.
The trade-off: operational complexity increases with each database type. The team must maintain, monitor, and backup multiple systems. Design justification: the performance and scalability gains from matching each workload to the right storage outweigh the operational cost at scale.
# Polyglot persistence in an e-commerce system
components = {
'User accounts, orders, payments': {
'storage': 'PostgreSQL',
'reason': 'ACID transactions (payment integrity), complex queries',
},
'Product catalogue': {
'storage': 'MongoDB or PostgreSQL with JSONB',
'reason': 'Flexible product attributes vary by category',
},
'Session tokens': {
'storage': 'Redis (with TTL)',
'reason': 'O(1) lookup, automatic expiry, high throughput',
},
'Product search': {
'storage': 'Elasticsearch',
'reason': 'Full-text search, faceted filtering, relevance scoring',
},
'Activity/event log': {
'storage': 'Cassandra or Kafka + S3',
'reason': 'High write throughput, append-only, time-series queries',
},
'Product images / videos': {
'storage': 'S3 + CloudFront CDN',
'reason': 'Cheap object storage, global distribution via CDN',
},
}
for component, info in components.items():
print(f'{component}:\n {info["storage"]}: {info["reason"]}\n')Indexing Strategy Across Storage Types
All storage systems use indexes to speed up reads at the cost of slower writes and extra storage. Understanding indexing across storage types is crucial for system design:
- SQL: B-tree index on any column; composite indexes for multi-column queries; covering indexes to avoid table lookups
- MongoDB: Index on any field; compound indexes; TTL indexes for automatic expiry
- Cassandra: Only partition key and clustering columns are indexed natively; secondary indexes are expensive
- Redis: Sorted sets as range-query indexes; no traditional indexing needed for key-value
# Indexing examples across storage types
# PostgreSQL: B-tree composite index
postgres_index = '''
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);
-- Optimises: SELECT * FROM orders WHERE user_id=? ORDER BY created_at DESC
'''
# MongoDB: compound index
mongo_index = '''
db.products.createIndex({ category: 1, price: -1 })
// Optimises: db.products.find({category:'Electronics'}).sort({price:-1})
'''
# Cassandra: cluster key ordering (built into table design)
cassandra_index = '''
-- No separate index needed; clustering key IS the index:
PRIMARY KEY (user_id, event_time) WITH CLUSTERING ORDER BY (event_time DESC)
'''
print('PostgreSQL:', postgres_index)
print('MongoDB:', mongo_index)
print('Cassandra:', cassandra_index)SQL vs NoSQL in Interviews: What to Say
When asked 'SQL or NoSQL?' in a system design interview, never give a one-word answer. Instead, follow this structure:
- State the workload: 'This is read-heavy with complex filtering, so…'
- State the requirement: 'We need strong consistency for financial transactions, so…'
- State the choice: 'I'd use PostgreSQL with read replicas'
- State the trade-off: 'The trade-off is that horizontal write scaling requires sharding, which adds complexity'
- Mention an alternative: 'If the writes were higher, we might consider DynamoDB'
# Sample answer structure for 'SQL or NoSQL?'
def answer_storage_question(workload, consistency_need, scale):
print(f'Workload: {workload}')
print(f'Consistency: {consistency_need}')
print(f'Scale: {scale}')
print()
if 'financial' in workload.lower() or consistency_need == 'strong':
choice = 'PostgreSQL (ACID, strong consistency)'
tradeoff = 'Horizontal write scaling requires sharding'
alternative = 'Google Spanner for global transactions'
elif 'event' in workload.lower() or 'log' in workload.lower():
choice = 'Cassandra (high write throughput, time-series)'
tradeoff = 'Eventual consistency; queries limited to partition key'
alternative = 'Kafka + S3 for long-term event archival'
else:
choice = 'DynamoDB (managed, auto-scale, low latency)'
tradeoff = 'Limited query flexibility; cross-item transactions limited'
alternative = 'PostgreSQL if complex queries emerge'
print(f'Choice: {choice}\nTrade-off: {tradeoff}\nAlternative: {alternative}')
answer_storage_question('Social media feed', 'eventual', '10M users')Quick Check
Test your understanding of Data Structures & Algorithms — Coding Interview Prep concepts from this lesson.
Lesson Recap
In this lesson you learned: SQL databases provide ACID transactions and complex queries but scale writes with difficulty, while NoSQL databases trade consistency or query flexibility for extreme scale and availability, the CAP theorem forces distributed systems to choose between consistency and availability during network partitions, and production systems typically use polyglot persistence — matching each workload to the right storage engine. Next up we explore caching layers, CDNs, and load balancing to further scale read-heavy systems.
Frequently asked questions
Is the “Scalable Data Storage: SQL vs NoSQL” lesson free?
Yes — the full text of “Scalable Data Storage: SQL vs NoSQL” is free to read here on the web, and the DSA Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the DSA Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “Scalable Data Storage: SQL vs NoSQL”?
Choose between relational databases, key-value stores, document databases, and wide-column stores based on access patterns, consistency, and scale requirements. You practise DSA Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start DSA Interview Prep?
No prior experience is required. DSA Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Scalable Data Storage: SQL vs NoSQL” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this DSA Interview Prep lesson?
Yes. Every DSA Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- The System Design Interview Framework
- Scalable Data Storage: SQL vs NoSQL
- Caching, CDNs, and Load Balancing
- Design Rate Limiter and Design Twitter Feed