Skalerbar datalagring: SQL eller NoSQL

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

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