スケーラブルなデータストレージ：SQLとNoSQL

ストレージの選択はトレードオフ

# 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}')

リレーショナルデータベース（SQL）の長所

# 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のスケーリングにおける課題

# 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')

キーバリューストア：RedisとDynamoDB

# 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')

ドキュメントストア：MongoDB

# 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')

ワイドカラムストア：Cassandra

# 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定理：一貫性、可用性、分断耐性

# 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"]}')

ストレージを選ぶための判断フレームワーク

# 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 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 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とNoSQLの答え方

# 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')

理解度チェック

レッスンのまとめ