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.
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)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