Requirements:
├─ User accounts must have: Name, balance, account number
├─ Transactions must be: Atomic (all-or-nothing)
├─ Relationships critical: "Account → Transactions → User"
├─ Data integrity: Cannot lose $0.01
└─ Choice: PostgreSQL (SQL)

Why SQL:
├─ ACID transactions (Atomic, Consistent, Isolated, Durable)
├─ Data integrity guaranteed
├─ Clear relationships (foreign keys)
└─ Bank of America: 67 million customers on Oracle SQL

Requirements:
├─ Posts can be: Text, photo, video, poll, story
├─ No fixed structure: Posts evolve constantly
├─ Scale: Billions of posts, 100M+ writes/second
├─ Speed matters more than perfect consistency
└─ Choice: Cassandra (NoSQL)

Why NoSQL:
├─ Horizontal scaling (add more servers = more capacity)
├─ Flexible schema (new post types added instantly)
├─ Fast writes (no relationship validation overhead)
└─ Instagram: 2 billion users on Cassandra

SQL:
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP
);
// Adding "phone" field = Alter entire table (slow!)

NoSQL:
{
  "id": 1,
  "name": "Alice",
  "email": "alice@example.com"
}
{
  "id": 2,
  "name": "Bob",
  "email": "bob@example.com",
  "phone": "555-1234"  // Added anytime, no migration!
}

SQL (Strong Relationships):
User → Orders → Order Items → Products
// Query: "Show me all products Bob bought last month"
// SQL excels at: JOINs across multiple tables

NoSQL (Denormalized):
Order Document:
{
  "user": "Bob",
  "items": [
    {"product": "iPhone", "price": 999},
    {"product": "Case", "price": 29}
  ]
}
// All data in one place, no JOINs needed
// Trade-off: Data duplicated across documents

SQL (Vertical Scaling):
├─ More powerful server: 32 GB RAM → 128 GB RAM
├─ Cost: $500/month → $4,000/month
├─ Limit: Single server maximum (256 GB RAM, 64 cores)
└─ Example: PostgreSQL can scale to ~1M queries/second per server

NoSQL (Horizontal Scaling):
├─ More servers: 10 servers → 100 servers
├─ Cost: Linear ($500/month per server)
├─ Limit: Virtually unlimited (add more servers)
└─ Example: Cassandra scales to billions of queries/second

SQL (Strong Consistency):
├─ Every read sees the latest write
├─ Example: Bank transfer appears immediately in both accounts
├─ Trade-off: Slower, may become unavailable during network issues
└─ Guarantee: Your balance is ALWAYS accurate

NoSQL (Eventual Consistency):
├─ Reads may see slightly old data for a few milliseconds
├─ Example: Instagram like count might be 999 or 1000 for 100ms
├─ Trade-off: Faster, stays available during network issues
└─ Guarantee: Data will EVENTUALLY be consistent (usually <1 second)

SQL (MySQL):
├─ User accounts, friend relationships
├─ 2.9 billion users
└─ Critical data requiring transactions

NoSQL (Cassandra):
├─ Messages, photos, activity logs
├─ 4+ petabytes/day
└─ Massive scale, eventual consistency acceptable

Relational (SQL) Databases:
    1. Oracle: 28% market share ($12B revenue/year)
    2. MySQL: 18% (most popular open-source)
    3. Microsoft SQL Server: 15% ($8B revenue/year)
    4. PostgreSQL: 14% (fastest growing, 40%+ YoY)
    5. SQLite: 10% (most deployed, 1 trillion+ databases)

NoSQL Databases:
    1. MongoDB: 35% NoSQL market ($1.3B revenue, 2023)
    2. Redis: 25% (in-memory, caching leader)
    3. Cassandra: 15% (wide-column, scale leader)
    4. DynamoDB: 12% (AWS managed)
    5. Elasticsearch: 8% (search/analytics)

Total Database Market: $80B+ (2024), projected $120B (2027)
Growth Driver: Data volume doubling every 2 years

A = Atomicity: All or nothing (transaction succeeds completely or fails completely)
C = Consistency: Data always valid (constraints enforced)
I = Isolation: Concurrent transactions don't interfere
D = Durability: Once committed, data persists (even if crash)

Example - Bank Transfer (ACID Required):
    BEGIN TRANSACTION;
        UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Deduct from Alice
        UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Add to Bob
    COMMIT;
    
    Scenario 1: Both updates succeed → Transaction commits → Money transferred 
    Scenario 2: Second update fails → Transaction rolls back → No money moved 
    Scenario 3: Power failure mid-transaction → Database recovers → Money intact 
    
    ACID guarantees: Money never disappears or duplicates
    Use case: Banking, payments, financial systems

CAP Theorem Position: ACID chooses Consistency + Availability over Partition Tolerance
    - Strong consistency: All reads see latest write
    - Availability: System responds to requests
    - Partition tolerance: Sacrificed (can't handle network splits well)

B = Basically Available: System always responds (even if stale data)
A = Soft state: State may change without input (eventual consistency)
S = Eventually consistent: System will become consistent over time

Example - Social Media Like (BASE Acceptable):
    User clicks "like" on Instagram photo:
        1. Write to nearest data center (US-East)
        2. Return success immediately (user sees like)
        3. Replicate to other data centers (US-West, Europe, Asia)
        4. Replication takes 100-500ms (eventual consistency)
    
    Scenario: Friend in Asia views photo after 50ms
        - Sees: 99 likes (hasn't propagated yet)
        - After 500ms: Sees 100 likes (eventually consistent)
        - Impact: None (social media tolerates slight delays)
    
    BASE trade-off: Lower consistency for higher availability/performance
    Use case: Social media, content platforms, analytics

CAP Theorem Position: BASE chooses Availability + Partition Tolerance over Consistency
    - Availability: Always responds (even during network issues)
    - Partition tolerance: Handles network splits gracefully
    - Consistency: Sacrificed (eventual, not immediate)

Instagram v1.0 Architecture (2010):
    - Users: 10 million
    - Photos: 1 billion
    - Database: PostgreSQL (single master)
    - Storage: 10 TB
    
PostgreSQL Limitations Hit (2012):
    - Users: 100 million (10x growth in 2 years)
    - Photos: 10 billion (10x growth)
    - Database: PostgreSQL sharded across 50 servers
    - Storage: 100 TB
    
Problems:
    1. Write Bottleneck:
       - 1M photos uploaded per hour
       - Single master can't handle write load
       - Write conflicts between shards
       
    2. Sharding Complexity:
       - 50 PostgreSQL shards (manual management)
       - Rebalancing: Moving photos between shards (days of work)
       - Joins across shards: Impossible (application-level joins)
       
    3. Availability Issues:
       - Single master per shard = single point of failure
       - Failover: 2-5 minutes (unacceptable)
       - User experience: "Photo upload failed, try again"
       
    4. Scaling Limits:
       - Adding shard: Weeks of planning
       - Data migration: Manual scripts
       - Cost: $500K/year in DBA time

Why Instagram Chose Cassandra:

1. Write Performance:
   PostgreSQL: 10,000 writes/second per server
   Cassandra: 100,000+ writes/second per server (10x better)
   
   Why: Log-structured storage (append-only, no random seeks)

2. Linear Scalability:
   PostgreSQL: Adding server = manual shard rebalancing
   Cassandra: Adding server = automatic rebalancing
   
   Instagram today: 1,000+ Cassandra nodes
   Add 10 nodes: 1 hour (automated)
   vs PostgreSQL: 1 week (manual scripts)

3. No Single Point of Failure:
   PostgreSQL: Master fails = 2-5 minute failover
   Cassandra: No master (peer-to-peer) = instant failover
   
   Cassandra replication factor: 3 (every photo on 3 nodes)
   Node fails: Other 2 nodes serve traffic immediately

4. Tunable Consistency:
   PostgreSQL: Strong consistency always (ACID)
   Cassandra: Choose per query (ONE, QUORUM, ALL)
   
   Instagram photo write:
       Consistency level: ONE (fast writes)
       Write to 1 node → return success
       Replicate to 2 other nodes asynchronously
   
   Instagram photo read:
       Consistency level: QUORUM (majority)
       Read from 2 of 3 nodes → return if match
       Tolerates 1 stale node

5. Geographic Distribution:
   PostgreSQL: Cross-region replication complex
   Cassandra: Multi-datacenter built-in
   
   Instagram deployment:
       - US-East: 300 nodes
       - US-West: 300 nodes
       - Europe: 200 nodes
       - Asia: 200 nodes
   
   Photo uploaded in New York:
       - Written to US-East cluster (local)
       - Replicated to US-West (50ms)
       - Replicated to Europe (80ms)
       - Replicated to Asia (120ms)
   
   User in Tokyo: Reads from Asia cluster (10ms latency)

-- Photos table (wide-column design)
CREATE TABLE photos (
    user_id bigint,              -- Partition key (determines which node)
    photo_id timeuuid,           -- Clustering key (sorts within partition)
    image_url text,              -- S3 URL for actual image
    caption text,
    location text,
    filter text,
    likes_count counter,         -- Counter column (increment without read)
    created_at timestamp,
    PRIMARY KEY (user_id, photo_id)
) WITH CLUSTERING ORDER BY (photo_id DESC);  -- Recent photos first

-- How it works:
-- 1. User uploads photo
-- 2. Generate photo_id (timeuuid includes timestamp)
-- 3. INSERT with user_id as partition key
-- 4. Cassandra hashes user_id to determine node
-- 5. All photos for same user stored together (fast retrieval)

-- Query examples:
-- Get recent 50 photos for user:
SELECT * FROM photos WHERE user_id = 12345 LIMIT 50;
-- Cassandra: Single partition read = 1ms (data co-located)

-- Increment likes (no read required):
UPDATE photos SET likes_count = likes_count + 1 
WHERE user_id = 12345 AND photo_id = 'abc-123';
-- Cassandra: Counter increment = 1ms (atomic operation)

Before Cassandra (2012):
    - 100M users, 10B photos
    - 50 PostgreSQL shards (manual management)
    - Write throughput: 500K photos/hour max
    - Availability: 99.5% (outages from failovers)
    - Scaling: Add capacity in weeks
    - DBA team: 5 engineers managing shards

After Cassandra (2024):
    - 2B users, 100B photos (20x growth)
    - 1,000+ Cassandra nodes (automatic management)
    - Write throughput: 4M+ photos/hour (8x better)
    - Availability: 99.99% (no single point of failure)
    - Scaling: Add capacity in hours (automated)
    - DBA team: 2 engineers (Cassandra self-manages)

Performance Improvements:
    - Photo upload latency: 500ms → 50ms (10x faster)
    - Photo feed load: 2 seconds → 300ms (6.7x faster)
    - Write throughput: 500K/hour → 4M/hour (8x increase)
    - Read throughput: 10M/sec → 100M/sec (10x increase)

Cost Efficiency:
    - PostgreSQL: $2M/year (50 shards, managed service)
    - Cassandra: $1.2M/year (1,000 nodes, self-managed)
    - Savings: $800K/year (40% reduction)
    - Why cheaper: Self-managed, commodity hardware, linear scaling

Operational Benefits:
    - Rebalancing: Manual weeks → Automatic hours
    - Adding capacity: 1 week → 1 hour (automated)
    - Failover: 2-5 minutes → <1 second (automatic)
    - DBA time: 80% reduction (self-managing system)

Use Cassandra When:
    Write-heavy workload (>50% writes)
    Time-series data (logs, events, sensor data)
    Need linear scalability (add nodes = add capacity)
    Multi-datacenter required (geographic distribution)
    High availability critical (no downtime tolerance)
    Eventual consistency acceptable (BASE model)
    Simple queries (no joins, no aggregations)
    
    Examples:
        - Instagram photos (100B+ records, write-heavy)
        - Netflix viewing history (petabytes, time-series)
        - Uber trip data (millions of trips/day)
        - IoT sensor data (billions of readings)

Use PostgreSQL When:
    Complex queries (joins, aggregations, sub-queries)
    Strong consistency required (ACID transactions)
    Relational data (foreign keys, constraints)
    OLTP workloads (transactional, not analytical)
    Moderate scale (<10 TB, <100K QPS)
    Need mature ecosystem (ORMs, tools, extensions)
    Read-heavy workload (can use replicas)
    
    Examples:
        - E-commerce orders (ACID transactions)
        - User authentication (strong consistency)
        - Financial systems (transactional integrity)
        - CRM systems (complex reporting)

Growth: 40%+ year-over-year (fastest growing database)
Ranking: #4 overall, #1 open-source RDBMS (ahead of MySQL)
Users: 10,000+ companies using in production
Market share: 14% of total database market
Revenue: Open-source (free), ecosystem $2B+/year (managed services, tools, support)

Why PostgreSQL Dominates:
    - ACID compliance (rock-solid transactions)
    - Advanced features (JSON, full-text search, geospatial)
    - Extensibility (custom types, functions, operators)
    - Performance (10x faster than MySQL for complex queries)
    - Cost: Free, no licensing fees (vs Oracle $50K+/core)

2008 Decision (Spotify Launch):
    Options Considered:
        1. MySQL - Most popular, but limited features
        2. Oracle - Enterprise-grade, but expensive ($50K+/core licensing)
        3. PostgreSQL - Free, feature-rich, ACID compliance
    
    Winning Factors for PostgreSQL:
        Cost: Free (vs Oracle $5M+/year for needed capacity)
        ACID: Transactions critical (playlist updates, subscriptions)
        JSON support: Music metadata (artists, albums, lyrics)
        Full-text search: Song/artist search functionality
        Extensions: PostGIS (geographic listening patterns)
        Performance: Complex queries (recommendation algorithms)
        Community: Active development, fast bug fixes

Data Volume:
    - Users: 600M (user accounts, preferences, subscriptions)
    - Tracks: 100M (metadata: artist, album, duration, lyrics)
    - Playlists: 5B+ user-created playlists
    - Listening history: 500B+ play events (historical data)
    - Daily writes: 2B+ events/day (plays, likes, playlist updates)
    - Database size: 200+ TB (PostgreSQL primary + replicas)

Scaling Strategy - Vertical + Horizontal:
    
    Vertical Scaling (Per Database):
        - Hardware: 96-core CPU, 1.5 TB RAM, 20 TB NVMe SSD
        - Instance: AWS RDS db.r6g.24xlarge ($15K/month)
        - Throughput: 100,000+ queries/second per instance
        - Connections: 5,000 concurrent (using PgBouncer pool)
    
    Horizontal Scaling (Sharding):
        - 100+ PostgreSQL clusters (functional sharding)
        - Shard by domain: Users, Tracks, Playlists, Listening History
        - Each cluster: 1 primary + 5 replicas (read scaling)
        - Total instances: 600+ PostgreSQL servers
        
    Sharding Strategy:
        Users Cluster (50 servers):
            - Primary: Writes (user registration, profile updates)
            - 5 Replicas: Reads (authentication, profile fetching)
            - Data: 50M users per shard (12 shards for 600M users)
            - Shard key: user_id % 12 (deterministic routing)
        
        Tracks Cluster (20 servers):
            - Primary: Writes (new tracks, metadata updates)
            - 5 Replicas: Reads (search, browse, recommendations)
            - Data: All 100M tracks (no sharding needed, fits in memory)
        
        Playlists Cluster (30 servers):
            - Primary: Writes (create playlist, add/remove songs)
            - 5 Replicas: Reads (fetch playlist, playlist search)
            - Data: 5B playlists (sharded by user_id, co-located with user data)
        
        Listening History Cluster (20 servers):
            - Primary: Writes (record play events, 2B/day)
            - 5 Replicas: Reads (recently played, listening stats)
            - Data: Time-series partitioned (monthly partitions)
            - Retention: 2 years hot (PostgreSQL), 5+ years cold (S3 + Redshift)

Problem Without Pooling:
    - Each app server: 100 connections to PostgreSQL
    - 1,000 app servers: 100,000 database connections
    - PostgreSQL: Each connection = 10 MB memory
    - Memory needed: 100,000 × 10 MB = 1 TB just for connections!
    - Impact: Out of memory, database crashes

Solution With PgBouncer:
    - PgBouncer layer between app and database
    - App servers: 100 connections to PgBouncer (lightweight)
    - PgBouncer: 500 connections to PostgreSQL (shared pool)
    - Multiplexing: 1,000 app connections share 500 DB connections
    - Memory: 500 × 10 MB = 5 GB (200x reduction!)

PgBouncer Configuration:
    [databases]
    spotify_users = host=users-db.internal port=5432 dbname=users
    
    [pgbouncer]
    listen_addr = *
    listen_port = 6432
    auth_type = md5
    auth_file = /etc/pgbouncer/userlist.txt
    
    # Pool settings
    pool_mode = transaction        # Share connection per transaction
    max_client_conn = 100000       # Total client connections
    default_pool_size = 500        # Connections per database
    reserve_pool_size = 50         # Emergency connections
    reserve_pool_timeout = 3       # Seconds to wait for connection
    
    # Performance
    server_lifetime = 3600         # Rotate connections hourly
    server_idle_timeout = 600      # Close idle after 10 minutes

Results:
    - Connections: 100,000 app → 500 database (200:1 ratio)
    - Memory: 1 TB → 5 GB (99.5% reduction)
    - Latency overhead: <1ms (PgBouncer is fast)
    - Throughput: Same (no bottleneck)

Read vs Write Pattern:
    - Writes: 10% (user actions: play song, create playlist)
    - Reads: 90% (fetch data: load app, show recommendations)
    
Single Primary Problem:
    - Primary: Handles 100% writes + 100% reads = overloaded
    - CPU: 95% (can't handle more)
    - Latency: Queries slow (300ms avg)

Read Replica Solution:
    - Primary: Handles 100% writes only
    - 5 Replicas: Handle 90% reads (load balanced)
    - Each replica: 18% load (90% ÷ 5 replicas)
    - Primary CPU: 50% (writes only)
    - Query latency: 30ms (10x faster)

Streaming Replication Setup:
    Primary: wal_level = replica
             max_wal_senders = 10
             max_replication_slots = 10
    
    Replica: primary_conninfo = 'host=primary.internal port=5432 user=replicator'
             hot_standby = on
             max_standby_streaming_delay = 30s
    
    Replication lag: <100ms typical (WAL streaming is fast)
    
Load Balancer (HAProxy):
    - Write queries: Route to primary
    - Read queries: Round-robin across 5 replicas
    - Health check: Query each replica every 2 seconds
    - Failover: Remove lagging replica (>1 second lag)

Application Code:
    # Python example using SQLAlchemy
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    
    # Primary for writes
    primary_engine = create_engine('postgresql://primary.internal:5432/spotify')
    
    # Replicas for reads (load balanced)
    replica_engine = create_engine('postgresql://replica-lb.internal:5432/spotify')
    
    # Write operation
    def create_playlist(user_id, name):
        with primary_engine.connect() as conn:
            result = conn.execute(
                "INSERT INTO playlists (user_id, name) VALUES (%s, %s) RETURNING id",
                (user_id, name)
            )
            return result.fetchone()[0]
    
    # Read operation
    def get_user_playlists(user_id):
        with replica_engine.connect() as conn:
            result = conn.execute(
                "SELECT id, name, track_count FROM playlists WHERE user_id = %s",
                (user_id,)
            )
            return result.fetchall()

Listening History Challenge:
    - Volume: 2 billion plays per day
    - Retention: 2 years (1.5 trillion records)
    - Table size: 200 TB (single table)
    - Query: "Show me plays from last 7 days"
    - Without partitioning: Scans 200 TB (very slow)

Partition Strategy (Monthly):
    CREATE TABLE listening_history (
        user_id BIGINT NOT NULL,
        track_id BIGINT NOT NULL,
        played_at TIMESTAMP NOT NULL,
        duration_ms INTEGER,
        PRIMARY KEY (user_id, played_at)
    ) PARTITION BY RANGE (played_at);
    
    -- Create monthly partitions
    CREATE TABLE listening_history_2024_01 PARTITION OF listening_history
        FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
    
    CREATE TABLE listening_history_2024_02 PARTITION OF listening_history
        FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
    
    -- ... 24 partitions for 2 years
    
    CREATE TABLE listening_history_2024_12 PARTITION OF listening_history
        FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

Query Performance:
    Query: Recent 7 days of plays
        SELECT * FROM listening_history 
        WHERE user_id = 12345 
        AND played_at > NOW() - INTERVAL '7 days';
    
    Without partitioning:
        - Scans: 200 TB (entire table)
        - Time: 30+ seconds
    
    With partitioning:
        - Scans: Only current month partition (8 TB)
        - Time: 1 second (30x faster)
        - Partition pruning: PostgreSQL automatically skips irrelevant partitions

Maintenance Benefits:
    - Drop old data: DROP TABLE listening_history_2022_01 (instant vs DELETE)
    - Backup: Backup each partition separately (parallel)
    - Vacuum: Per-partition (doesn't block entire table)
    - Indexes: Per-partition (smaller, faster rebuilds)

Spotify's Critical Indexes:

1. Primary Key Indexes (Automatic):
    - users: PRIMARY KEY (user_id)
    - tracks: PRIMARY KEY (track_id)
    - playlists: PRIMARY KEY (playlist_id)
    - B-tree index created automatically

2. Foreign Key Indexes (Manual - Important!):
    CREATE INDEX idx_playlist_user_id ON playlists(user_id);
    -- Query: Find all playlists for user
    -- Without index: Table scan (5B playlists, 30+ seconds)
    -- With index: Index scan (100 playlists, 10ms)

3. Composite Indexes (Multi-Column):
    CREATE INDEX idx_listening_user_time ON listening_history(user_id, played_at DESC);
    -- Query: Recent plays for user (most common query)
    -- Index covers both WHERE user_id = X AND played_at > Y
    -- Also enables fast ORDER BY played_at DESC

4. Partial Indexes (Filtered):
    CREATE INDEX idx_premium_users ON users(email) WHERE subscription = 'premium';
    -- Only indexes premium users (250M of 600M = smaller index)
    -- Query: Premium user lookup by email (login page)
    -- 60% smaller index = faster, less memory

5. GIN Indexes (JSON, Full-Text):
    CREATE INDEX idx_track_metadata ON tracks USING GIN(metadata);
    -- metadata is JSONB column (artist, album, lyrics, etc.)
    -- Query: Search for track with specific artist/album
    -- Example: WHERE metadata @> '{"artist": "The Beatles"}'

6. Expression Indexes (Computed):
    CREATE INDEX idx_user_email_lower ON users(LOWER(email));
    -- Case-insensitive email lookup (login)
    -- Query: WHERE LOWER(email) = 'user@example.com'
    -- Without expression index: Can't use index (must scan)

Index Maintenance:
    - Rebuild: REINDEX INDEX CONCURRENTLY (no downtime)
    - Monitor: pg_stat_user_indexes (tracks index usage)
    - Remove unused: DROP INDEX if pg_stat shows 0 scans
    - Auto-vacuum: Runs automatically (keeps indexes healthy)

Performance Metrics:
    Query Performance:
        - Simple queries (user profile): <5ms p95
        - Complex queries (recommendation): <50ms p95
        - Search queries (track search): <20ms p95
        - Write operations (play event): <10ms p95
    
    Throughput:
        - Total queries: 500M+/second across all clusters
        - Writes: 25K/second (play events, likes, playlists)
        - Reads: 475K/second (app loads, search, recommendations)
    
    Availability:
        - Uptime: 99.95% (PostgreSQL clusters)
        - Failover: <30 seconds (automatic promotion)
        - Replication lag: <100ms typical (streaming replication)

Scaling Achievements:
    2008 (Launch):
        - Users: 1M
        - Servers: 5 PostgreSQL instances
        - Data: 100 GB
        - Queries: 1K/second
    
    2024 (Current):
        - Users: 600M (600x growth)
        - Servers: 600+ PostgreSQL instances (120x growth)
        - Data: 200 TB (2,000x growth)
        - Queries: 500K/second (500x growth)
    
    Linear scaling: 600x users = 120x servers (efficient!)

Cost Analysis:
    Self-Managed PostgreSQL (Spotify's Choice):
        - Servers: 600 instances on AWS EC2
        - Instance type: r6g.24xlarge ($15K/month each)
        - Total: 600 × $15K = $9M/month = $108M/year
        - Staff: 10 database engineers ($2M/year)
        - Total: $110M/year
    
    AWS RDS Managed (Alternative):
        - Same instances on RDS: 600 × $20K/month = $12M/month = $144M/year
        - Staff: 3 engineers (RDS manages most) ($600K/year)
        - Total: $144.6M/year
    
    Spotify saves: $34.6M/year (24% cheaper self-managed)
    Why: Economy of scale, expertise in-house, full control

Operational Benefits:
    - Automatic failover: <30 seconds (Patroni tool)
    - Read scaling: Add replica in minutes (streaming replication)
    - Connection pooling: 200:1 ratio (PgBouncer)
    - Partitioning: 30x faster queries (monthly partitions)
    - Replication lag: <100ms (real-time reads)

Feature Comparison:

ACID Compliance:
    PostgreSQL: Full ACID (always)
    MySQL InnoDB: Full ACID (default engine)
    Winner: Tie

Complex Queries:
    PostgreSQL: Advanced (CTEs, window functions, LATERAL joins)
    MySQL: Limited (basic joins, subqueries)
    Winner: PostgreSQL (10x faster for Spotify's recommendation queries)

JSON Support:
    PostgreSQL: JSONB (binary, indexed, fast)
    MySQL: JSON (text, limited indexing)
    Winner: PostgreSQL (track metadata stored in JSONB)

Full-Text Search:
    PostgreSQL: Built-in (tsvector, GIN indexes)
    MySQL: Basic FULLTEXT (limited features)
    Winner: PostgreSQL (song/artist search faster)

Replication:
    PostgreSQL: Streaming (real-time, <100ms lag)
    MySQL: Binary log (good, but more lag)
    Winner: PostgreSQL (lower lag critical for Spotify)

Extensions:
    PostgreSQL: PostGIS, pg_stat_statements, timescaledb
    MySQL: Limited plugin system
    Winner: PostgreSQL (geolocation features for Spotify)

Community:
    PostgreSQL: Active, innovative, fast releases
    MySQL: Oracle-owned, slower development
    Winner: PostgreSQL (vibrant ecosystem)

Cost:
    PostgreSQL: Free, permissive license
    MySQL: Free (GPL), but Oracle ownership concerns
    Winner: PostgreSQL (no vendor concerns)

Spotify's Decision: PostgreSQL wins 7 of 8 categories
MySQL advantage: Slightly easier initial setup (not a factor at Spotify's scale)

Use PostgreSQL When:
    Complex queries (joins, CTEs, window functions)
    ACID transactions required (financial, e-commerce)
    JSON/NoSQL hybrid (flexible schema + SQL)
    Full-text search (built-in, no Elasticsearch needed)
    Geospatial data (PostGIS extension)
    Need extensions (time-series, graph, etc.)
    Strong consistency required
    Read-heavy workload (with replicas)
    Moderate writes (<100K writes/second)
    Data <100 TB per cluster
    
    Examples:
        - Spotify: 600M users, music catalog, playlists
        - Uber: Trip data, pricing, driver locations
        - Instagram: User accounts, relationships (not photos!)
        - Reddit: Posts, comments, votes

Don't Use PostgreSQL When:
    Extreme write load (>1M writes/second)
    Need automatic sharding (Cassandra better)
    Simple key-value (Redis/DynamoDB simpler)
    Massive scale (>100 TB, consider Cassandra)
    Eventual consistency acceptable (NoSQL simpler)

Market Share: 35% of NoSQL market ($1.3B revenue, 2023)
Growth: 25%+ year-over-year
Users: 45,000+ companies in production
Fortune 500: 60%+ use MongoDB
Ranking: #1 document database, #5 overall database

Why MongoDB Dominates Document Stores:
    - Flexible schema (JSON documents, no rigid tables)
    - Horizontal scaling (automatic sharding built-in)
    - Developer-friendly (query syntax similar to JavaScript)
    - Rich queries (aggregation pipelines, geospatial)
    - Managed service (MongoDB Atlas - 60%+ of customers)

Oracle Problem (2010-2015):
    
Schema Rigidity:
    - Oracle: Strict schema (columns defined upfront)
    - Product types: Books, Cars, Electronics, Clothing, Jewelry, etc.
    - Each type: Different attributes
    
    Oracle Approach 1 - Single Table (EAV Pattern):
        products:
            | product_id | attribute_name | attribute_value |
            | 1001       | title          | iPhone 15       |
            | 1001       | brand          | Apple           |
            | 1001       | color          | Blue            |
            | 1001       | storage        | 256GB           |
        
        Problems:
            Query complexity: 5-10 JOINs per product
            Performance: 5+ seconds to load single product
            Indexing: Impossible to index attribute_value (generic)
    
    Oracle Approach 2 - Multiple Tables (One per Category):
        electronics_products (50 columns)
        automotive_products (80 columns)
        books (30 columns)
        clothing (40 columns)
        ...200 more tables
        
        Problems:
            Schema changes: Add new attribute = ALTER TABLE (locks table)
            New category: New table + code changes + deploy
            Cross-category search: Query 200+ tables (UNION)
            Maintenance: 200 tables × indexing/backup/optimize
    
    Oracle Approach 3 - Wide Table (Super Schema):
        products:
            | id | title | price | isbn | vin | size | color | ... 500 columns |
        
        Problems:
            Sparse data: Most columns NULL (book has no VIN)
            Storage waste: NULL values use space in Oracle
            Query performance: Scanning 500 columns even for simple query
            Schema evolution: Adding columns = ALTER TABLE (downtime)

Oracle Costs:
    - Licensing: $50,000 per core (200 cores = $10M)
    - Annual support: 22% of license ($2.2M/year)
    - DBA team: 15 engineers managing schema changes
    - Deployment velocity: 2 weeks (schema change approval)

MongoDB Document Model:

Flexible Schema (No Predefined Columns):
    // Electronics product (iPhone)
    {
        "_id": ObjectId("507f1f77bcf86cd799439011"),
        "title": "iPhone 15 Pro Max 256GB",
        "category": "Electronics",
        "price": 1199.00,
        "brand": "Apple",
        "condition": "New",
        "seller_id": 12345,
        "location": "San Francisco, CA",
        "attributes": {
            "color": "Blue Titanium",
            "storage": "256GB",
            "screen_size": "6.7 inches",
            "chip": "A17 Pro",
            "5g": true
        },
        "images": [
            "https://cdn.ebay.com/iphone-front.jpg",
            "https://cdn.ebay.com/iphone-back.jpg"
        ],
        "tags": ["smartphone", "ios", "apple", "5g"],
        "created_at": ISODate("2024-09-20T10:30:00Z"),
        "views": 1250,
        "watchers": 45
    }
    
    // Automotive product (Tesla)
    {
        "_id": ObjectId("507f191e810c19729de860ea"),
        "title": "2023 Tesla Model 3 Long Range",
        "category": "Automotive",
        "price": 45000.00,
        "brand": "Tesla",
        "condition": "Used",
        "seller_id": 67890,
        "location": "Los Angeles, CA",
        "attributes": {
            "year": 2023,
            "make": "Tesla",
            "model": "Model 3",
            "trim": "Long Range",
            "vin": "5YJ3E1EA1PF123456",
            "mileage": 12500,
            "color": "Pearl White Multi-Coat",
            "battery_range": 358,
            "autopilot": true,
            "fsd_capable": true
        },
        "images": [
            "https://cdn.ebay.com/tesla-front.jpg",
            "https://cdn.ebay.com/tesla-interior.jpg"
        ],
        "tags": ["electric vehicle", "tesla", "autopilot"],
        "created_at": ISODate("2024-09-18T14:20:00Z"),
        "views": 3420,
        "watchers": 127
    }
    
    // Book product
    {
        "_id": ObjectId("507f191e810c19729de860eb"),
        "title": "The Three-Body Problem by Liu Cixin",
        "category": "Books",
        "price": 16.99,
        "brand": "Tor Books",
        "condition": "New",
        "seller_id": 11223,
        "location": "New York, NY",
        "attributes": {
            "isbn": "9780765382030",
            "author": "Liu Cixin",
            "publisher": "Tor Books",
            "publication_date": "2014-11-11",
            "pages": 400,
            "language": "English",
            "format": "Paperback",
            "genre": "Science Fiction"
        },
        "images": [
            "https://cdn.ebay.com/three-body-cover.jpg"
        ],
        "tags": ["science fiction", "hugo award", "chinese author"],
        "created_at": ISODate("2024-09-22T09:15:00Z"),
        "views": 890,
        "watchers": 23
    }

Key Benefits:
    No schema changes needed - Add new fields anytime
    Each document: Only stores relevant fields (no NULL waste)
    Nested objects: attributes embedded (no JOIN needed)
    Arrays: images, tags stored natively (no pivot tables)
    Query simplicity: db.products.find({_id: "..."}) returns full product

Scale & Performance:
    - Collections: products, users, transactions, reviews
    - Documents: 1.4B products (live listings)
    - Storage: 500+ TB (MongoDB replica sets)
    - Queries: 10M+ queries/second (read-heavy)
    - Writes: 500K+ writes/second (new listings, bids)
    - Clusters: 50+ MongoDB replica sets (sharded)

Sharding Strategy:
    
    Why Shard:
        - 1.4B products too large for single server
        - Need horizontal scaling (add servers = add capacity)
    
    Shard Key: seller_id (products grouped by seller)
        - Rationale: Sellers manage their own listings (locality)
        - Query pattern: "Show all my listings" (single shard)
        - Balance: Even distribution (millions of sellers)
    
    Architecture:
        mongos (Query Router):
            - 20 mongos instances (load balanced)
            - Routes queries to correct shards
            - Aggregates results from multiple shards
        
        Config Servers (3 servers):
            - Stores metadata: Which shard contains which data
            - Highly available (replica set of 3)
            - Small data (GB), critical for routing
        
        Shards (50 shards, each is replica set):
            Shard 1: seller_id 1-100,000
                - Primary: Writes
                - Secondary 1: Reads (US-West)
                - Secondary 2: Reads (US-East)
                - Data: 10 TB (28M products)
            
            Shard 2: seller_id 100,001-200,000
                - Primary: Writes
                - Secondary 1: Reads
                - Secondary 2: Reads
                - Data: 10 TB (28M products)
            
            ... 48 more shards
            
            Shard 50: seller_id 4,900,001-5,000,000
                - Primary: Writes
                - Secondary 1: Reads
                - Secondary 2: Reads
                - Data: 10 TB (28M products)
        
        Total: 50 shards × 3 replicas = 150 MongoDB servers
        
    Query Examples:
        
        1. Single Shard Query (Fast):
           db.products.find({ seller_id: 50000 })
           
           mongos routes to: Shard 1 only
           Latency: 5ms (single shard, local data)
        
        2. Scatter-Gather Query (Slower):
           db.products.find({ category: "Electronics", price: { $lt: 500 } })
           
           mongos routes to: All 50 shards (category not in shard key)
           Each shard: Returns matching products
           mongos: Merges results, sorts, returns to client
           Latency: 50ms (network overhead, result merging)
        
        3. Aggregation Pipeline (Complex):
           db.products.aggregate([
               { $match: { category: "Electronics" } },
               { $group: { _id: "$brand", avg_price: { $avg: "$price" } } },
               { $sort: { avg_price: -1 } },
               { $limit: 10 }
           ])
           
           Execution:
               - Each shard: Runs aggregation locally
               - Shard 1 result: {Apple: $850, Samsung: $650, ...}
               - Shard 2 result: {Apple: $830, Samsung: $670, ...}
               - mongos: Merges, re-calculates global average
               - Final: {Apple: $845, Samsung: $655, Sony: $600, ...}
           
           Latency: 100ms (CPU-intensive aggregation)

Critical Indexes:

1. _id Index (Automatic):
    - Created automatically on _id field
    - B-tree index for fast lookups
    - Query: db.products.find({ _id: ObjectId("...") })
    - Performance: 1-2ms (index seek)

2. Seller Listings Index:
    db.products.createIndex({ seller_id: 1, created_at: -1 })
    
    Use case: "Show my recent listings" (seller dashboard)
    Query: db.products.find({ seller_id: 12345 }).sort({ created_at: -1 }).limit(50)
    Performance: 5ms (compound index covers query entirely)

3. Category + Price Index:
    db.products.createIndex({ category: 1, price: 1 })
    
    Use case: Browse category by price range
    Query: db.products.find({ category: "Electronics", price: { $gte: 500, $lte: 1000 } })
    Performance: 10ms (index range scan)

4. Text Search Index (Full-Text):
    db.products.createIndex({ 
        title: "text", 
        "attributes.description": "text",
        tags: "text"
    }, { 
        weights: { title: 10, "attributes.description": 5, tags: 3 },
        name: "product_text_search"
    })
    
    Use case: Search for "iphone 15 blue titanium"
    Query: db.products.find({ $text: { $search: "iphone 15 blue titanium" } })
    MongoDB: 
        - Tokenizes search: ["iphone", "15", "blue", "titanium"]
        - Searches text index (inverted index structure)
        - Ranks by relevance score (title matches weight 10x more)
    Performance: 20ms (text search is CPU-intensive)

5. Geospatial Index (Location-Based):
    db.products.createIndex({ location: "2dsphere" })
    
    Use case: "Find products near me" (local pickup)
    Product location: { type: "Point", coordinates: [-118.2437, 34.0522] } // LA
    Query: db.products.find({
        location: {
            $near: {
                $geometry: { type: "Point", coordinates: [-118.25, 34.05] },
                $maxDistance: 50000  // 50km radius
            }
        }
    })
    Performance: 15ms (geospatial index uses R-tree structure)

6. Compound Index on Attributes (Sparse):
    db.products.createIndex({ "attributes.brand": 1, "attributes.condition": 1 }, { sparse: true })
    
    Use case: Filter by brand + condition
    Query: db.products.find({ "attributes.brand": "Apple", "attributes.condition": "New" })
    Performance: 8ms
    
    Sparse index: Only indexes documents with both fields (saves space)
    Note: Not all products have brand (e.g., handmade items)

Phase 1: Pilot (6 months, 2015 Q1-Q2)
    - Scope: 1 million products (0.1% of catalog)
    - Category: Consumer Electronics only
    - Architecture: Single MongoDB replica set
    - Goals: Validate performance, test queries
    - Results:
        Query latency: 500ms (Oracle) → 50ms (MongoDB) = 10x faster
        Schema changes: 2 weeks (Oracle ALTER) → 5 minutes (MongoDB)
        Developer velocity: 3x faster (no schema rigidity)
        Storage: 30% reduction (no NULL waste)

Phase 2: Parallel Run (12 months, 2015 Q3 - 2016 Q2)
    - Scope: 50 million products (5% of catalog)
    - Categories: Electronics, Books, Clothing
    - Architecture: 5 MongoDB shards (replica sets)
    - Strategy: Dual-write (Oracle + MongoDB simultaneously)
    - Validation: Compare results between Oracle and MongoDB
    - Results:
        Consistency: 99.99%+ match between systems
        Performance: MongoDB 5-10x faster for reads
        Availability: 99.95% (MongoDB) vs 99.9% (Oracle)
        Cost: MongoDB 40% cheaper per TB

Phase 3: Full Migration (18 months, 2016 Q3 - 2017 Q4)
    - Scope: All 1 billion products
    - Strategy: Category-by-category migration
    - Downtime: Zero (blue-green deployment)
    - Process:
        1. Migrate category data to MongoDB
        2. Run dual-write for 2 weeks (validation)
        3. Switch reads to MongoDB (writes still dual)
        4. Monitor for 1 week (rollback if issues)
        5. Switch writes to MongoDB only
        6. Decommission Oracle for that category
    - Results:
        Zero downtime during migration
        All categories migrated in 18 months
        Oracle decommissioned Q4 2017

Migration Tooling:
    # Custom Python script (simplified)
    from pymongo import MongoClient
    import cx_Oracle
    
    # Connect to Oracle
    oracle_conn = cx_Oracle.connect('user/pass@oracle_host/db')
    oracle_cursor = oracle_conn.cursor()
    
    # Connect to MongoDB
    mongo_client = MongoClient('mongodb://mongo_host:27017')
    mongo_db = mongo_client['ebay']
    products_collection = mongo_db['products']
    
    # Fetch products from Oracle (batch of 10,000)
    oracle_cursor.execute("""
        SELECT product_id, title, price, seller_id, category, 
               attribute_name, attribute_value
        FROM products p
        LEFT JOIN product_attributes pa ON p.product_id = pa.product_id
        WHERE category = 'Electronics'
        AND rownum <= 10000
    """)
    
    # Transform Oracle rows to MongoDB documents
    products = {}
    for row in oracle_cursor:
        product_id, title, price, seller_id, category, attr_name, attr_value = row
        
        if product_id not in products:
            products[product_id] = {
                "_id": product_id,
                "title": title,
                "price": price,
                "seller_id": seller_id,
                "category": category,
                "attributes": {}
            }
        
        if attr_name and attr_value:
            products[product_id]["attributes"][attr_name] = attr_value
    
    # Bulk insert to MongoDB
    products_collection.insert_many(products.values())
    print(f"Migrated {len(products)} products")

Performance Improvements:
    Product Page Load:
        - Oracle: 500ms average (multiple JOINs)
        - MongoDB: 50ms average (single document fetch)
        - Improvement: 10x faster

    Search Results:
        - Oracle: 2-3 seconds (LIKE queries, 200+ table UNION)
        - MongoDB: 200-300ms (text index search)
        - Improvement: 7-10x faster

    Seller Dashboard:
        - Oracle: 1-2 seconds (listing pagination with JOINs)
        - MongoDB: 100ms (compound index on seller_id + date)
        - Improvement: 10-20x faster

Developer Productivity:
    Schema Changes:
        - Oracle: 2 weeks (DBA review → ALTER TABLE → testing → deploy)
        - MongoDB: 5 minutes (add field to document, deploy code)
        - Improvement: 400x faster

    New Category Launch:
        - Oracle: 1 month (design schema → create tables → migrate → test)
        - MongoDB: 1 day (define fields, start inserting documents)
        - Improvement: 30x faster

    Feature Development:
        - Oracle: 2-3 weeks per feature (schema constraints)
        - MongoDB: 3-5 days per feature (flexible schema)
        - Improvement: 3-5x faster

Cost Savings:
    Licensing:
        - Oracle: $10M license + $2.2M/year support = $12.2M/year
        - MongoDB: $0 (open-source) + $500K/year Atlas (managed) = $500K/year
        - Savings: $11.7M/year (95% reduction)

    Hardware:
        - Oracle: 200 cores × $50K = $10M + support $2.2M
        - MongoDB: 150 servers × commodity hardware = $3M
        - Savings: $9.2M upfront + $2.2M/year ongoing

    DBA Team:
        - Oracle: 15 DBAs managing schema, migrations = $3M/year
        - MongoDB: 5 DBAs (self-managing, less schema work) = $1M/year
        - Savings: $2M/year

    Total Savings: $25M+ over 9 years (2015-2024)

Operational Benefits:
    Availability:
        - Oracle: 99.9% (planned maintenance, failover delays)
        - MongoDB: 99.95% (replica sets, automatic failover)
        - Improvement: 5x fewer outages

    Scaling:
        - Oracle: Vertical (bigger server, limited)
        - MongoDB: Horizontal (add shards, unlimited)
        - Result: Grew from 1B to 1.4B products (40% growth)

    Deployment Velocity:
        - Oracle: 2-week cycles (schema coordination)
        - MongoDB: Daily deployments (schema independence)
        - Result: Ship features 10x faster

Use MongoDB When:
    Flexible schema (product catalog, CMS, user profiles)
    Nested data (JSON documents with arrays, objects)
    Horizontal scaling needed (sharding built-in)
    Rapid development (schema changes frequent)
    Read-heavy workload (document fetch is fast)
    Unstructured/semi-structured data
    Multi-datacenter (MongoDB Atlas global clusters)
    
    Examples:
        - eBay: Product catalog (1.4B listings, varied attributes)
        - New York Times: Article CMS (flexible content structure)
        - Uber: Driver/rider profiles (nested preferences)
        - Adobe: Creative Cloud user data (dynamic fields)

Use PostgreSQL When:
    Complex queries (JOIN multiple entities)
    Transactions required (ACID compliance critical)
    Relational data (foreign keys, referential integrity)
    Structured data (schema stable, predefined)
    Strong consistency (financial, inventory systems)
    Mature tooling needed (ORMs, reporting tools)
    
    Examples:
        - E-commerce orders: ACID transactions, foreign keys
        - Banking: Strong consistency, regulatory compliance
        - ERP systems: Complex reporting, data integrity

Hybrid Approach (Many Companies):
    PostgreSQL: Core transactional data (orders, payments, users)
    MongoDB: Flexible data (product catalog, content, logs, events)
    
    Example - Shopify:
        - PostgreSQL: Store info, checkout, orders (ACID)
        - MongoDB: Product catalog, themes, app data (flexible)
        - Benefit: Right tool for right data

Company: MongoDB Inc. (NASDAQ: MDB)
Market cap: $28 billion (2024)
Revenue: $1.68 billion (fiscal 2024, +31% YoY)
Customers: 47,000+ organizations globally
Atlas (managed): 70%+ of revenue ($1.2B/year)
Downloads: 400 million+ total (since 2009)
Market share: 35% of NoSQL databases (#1 document store)

Growth Drivers:
    - Developer productivity (flexible schema)
    - Time-to-market (rapid prototyping)
    - Scale (horizontal sharding built-in)
    - Cloud-first (MongoDB Atlas fully managed)
    - JSON native (matches modern app development)

Relational (SQL):
    Data stored in rows across multiple tables
    Relationships via foreign keys (JOINs required)
    Fixed schema (ALTER TABLE to add columns)
    Normalized (reduce duplication)
    
    Example - Blog Post (3 tables, 1 JOIN):
        posts table:
            id | title | content | author_id | created_at
            1  | "Hello" | "..." | 101 | 2024-01-01
        
        authors table:
            id | name | email | bio
            101 | "John" | "john@example.com" | "Writer"
        
        comments table:
            id | post_id | author | content | created_at
            1  | 1 | "Jane" | "Great!" | 2024-01-02
            2  | 1 | "Bob" | "Thanks" | 2024-01-03
        
        Query (requires JOIN):
            SELECT posts.*, authors.name, authors.email
            FROM posts
            JOIN authors ON posts.author_id = authors.id
            WHERE posts.id = 1;

Document (MongoDB):
    Data stored in documents (JSON-like)
    Embedded relationships (no JOINs needed)
    Flexible schema (add fields anytime)
    Denormalized (optimize for reads)
    
    Example - Blog Post (1 document, no JOIN):
        {
            "_id": 1,
            "title": "Hello World",
            "content": "Welcome to my blog...",
            "author": {
                "name": "John Doe",
                "email": "john@example.com",
                "bio": "Writer and technologist"
            },
            "comments": [
                {
                    "author": "Jane Smith",
                    "content": "Great post!",
                    "created_at": "2024-01-02T10:30:00Z"
                },
                {
                    "author": "Bob Johnson",
                    "content": "Thanks for sharing!",
                    "created_at": "2024-01-03T14:15:00Z"
                }
            ],
            "tags": ["mongodb", "nosql", "databases"],
            "views": 1250,
            "created_at": "2024-01-01T09:00:00Z"
        }
        
        Query (single document fetch, no JOIN):
            db.posts.findOne({ _id: 1 })
        
        Performance:
            SQL: 2 JOINs = 3 table scans = 15-50ms
            MongoDB: 1 document = 1 lookup = 1-5ms (3-10x faster)

eBay's Original Architecture (Oracle Database):
    Products stored in rigid schema:
        products table:
            id | title | description | price | category_id | brand | ...
        
        Problem with Electronics:
            laptop: needs processor, RAM, storage, screen size
            phone: needs processor, camera, battery, carrier
            camera: needs megapixels, lens, sensor, video resolution
        
        Solution: EAV (Entity-Attribute-Value) anti-pattern
            attributes table:
                product_id | attribute_name | attribute_value
                1001 | "processor" | "Intel i7"
                1001 | "ram" | "16GB"
                1001 | "storage" | "512GB SSD"
                1002 | "megapixels" | "24MP"
                1002 | "lens" | "50mm f/1.8"
        
        Problems:
            1. Performance: Each product = 10-30 JOINs (slow!)
            2. Queries: WHERE attribute_name = 'ram' AND attribute_value = '16GB'
               (Can't index efficiently, table scans)
            3. Schema: Add new attribute type = ALTER tables
            4. Complexity: 50+ tables for product variations
            5. Developer productivity: 2 weeks to add new category

eBay Search Performance (Oracle EAV):
    Query: Find laptops with 16GB RAM and i7 processor
    Execution:
        1. Join products with attributes (processor)
        2. Join products with attributes (RAM)
        3. Filter both conditions
        4. Join with images, sellers, shipping
    
    Tables scanned: 8 tables, 30+ JOINs
    Query time: 500ms - 2 seconds (unacceptable)
    Database load: High (complex JOINs)

Why eBay Chose MongoDB:
    
    1. Flexible Schema:
       - Each product: Custom attributes (no fixed schema)
       - Electronics: processor, RAM, storage fields
       - Clothing: size, color, material fields
       - Books: author, ISBN, publisher fields
       - No schema changes needed (just add fields)
    
    2. Performance:
       - Document model: 1 lookup vs 30 JOINs
       - Embedded data: All product info in 1 document
       - Indexes: Multiple attribute indexes (fast filters)
       - Query time: 5-20ms (50-100x faster than Oracle)
    
    3. Developer Productivity:
       - New category: Add fields to documents (no migrations)
       - Time to market: 2 weeks → 2 days (10x faster)
       - No ORM impedance mismatch (JSON native)
    
    4. Scalability:
       - Sharding: Automatic distribution across servers
       - Replication: Automatic failover (replica sets)
       - Horizontal scaling: Add servers = add capacity

eBay's MongoDB Document Structure:
    {
        "_id": "item-12345",
        "title": "Dell XPS 13 Laptop - 13.4\" FHD+ Display",
        "description": "High-performance ultrabook...",
        "category": {
            "primary": "Electronics",
            "path": ["Electronics", "Computers", "Laptops"],
            "leaf": "Ultrabooks"
        },
        "price": {
            "amount": 1199.99,
            "currency": "USD",
            "original": 1499.99,
            "discount_percent": 20
        },
        "seller": {
            "id": "seller-789",
            "username": "TechDeals",
            "rating": 4.8,
            "feedback_count": 15420,
            "verified": true
        },
        "attributes": {
            "processor": "Intel Core i7-1165G7",
            "ram": "16GB LPDDR4x",
            "storage": "512GB NVMe SSD",
            "screen": {
                "size": "13.4 inches",
                "resolution": "1920x1200",
                "type": "InfinityEdge FHD+"
            },
            "weight": "2.8 lbs",
            "battery": "Up to 12 hours",
            "ports": ["2x Thunderbolt 4", "1x USB-C", "1x microSD"],
            "wifi": "Wi-Fi 6",
            "warranty": "1 year"
        },
        "shipping": {
            "free_shipping": true,
            "estimated_days": "3-5",
            "expedited_available": true,
            "ships_from": "California, USA"
        },
        "images": [
            "https://cdn.ebay.com/item-12345-img1.jpg",
            "https://cdn.ebay.com/item-12345-img2.jpg",
            "https://cdn.ebay.com/item-12345-img3.jpg"
        ],
        "views": 1250,
        "watchers": 43,
        "bids": 0,
        "quantity": 5,
        "condition": "New",
        "listing_type": "Buy It Now",
        "ends_at": "2024-03-15T23:59:59Z",
        "created_at": "2024-03-01T10:00:00Z"
    }

Query Examples (MongoDB):
    
    1. Find laptops with 16GB RAM and i7 processor:
       db.products.find({
           "category.path": "Laptops",
           "attributes.ram": "16GB LPDDR4x",
           "attributes.processor": { $regex: "i7" }
       })
       
       Performance: 10-20ms (single index scan)
       vs Oracle: 500ms-2s (30 JOINs)
    
    2. Find products under $500 with free shipping:
       db.products.find({
           "price.amount": { $lt: 500 },
           "shipping.free_shipping": true
       })
       
       Index: Compound index on (price.amount, shipping.free_shipping)
       Performance: 5ms
    
    3. Get seller's feedback and active listings:
       db.products.find({
           "seller.id": "seller-789",
           "ends_at": { $gt: new Date() }
       }).sort({ "ends_at": 1 })
       
       Performance: 8ms (seller index)

Performance: 50-100x faster queries (500ms → 5-20ms)
Development: 10x faster (2 weeks → 2 days per category)
Cost: 16.6% savings ($10.1M → $8.42M/year)
Availability: 99.99% uptime (<5 second failover)
Scale: 250M products, 550K ops/sec, 50 TB data

Company: Redis Ltd (NASDAQ: REDIS)
Market cap: $8.5 billion (2024)
Revenue: $325 million (fiscal 2023, +54% YoY)
Open-source: Redis (BSD license, free)
Enterprise: Redis Enterprise (managed, $$$)
Users: 1+ million companies globally
Downloads: 3+ billion total (Docker pulls)
Market share: 25% of NoSQL databases (#1 in-memory)

Performance Characteristics:
    - Latency: Sub-millisecond (<1ms typical)
    - Throughput: 1M+ operations/second (single instance)
    - Data structures: 10+ native types (strings, hashes, lists, sets, sorted sets)
    - Persistence: Optional (RDB snapshots, AOF logs)
    - Replication: Master-replica (async, fast)
    - Clustering: Automatic sharding (16,384 hash slots)

Speed Comparison (1M operations):
    Disk (SSD): 100-500 IOPS = 2,000-10,000 seconds
    PostgreSQL: 10K-50K QPS = 20-100 seconds
    Redis: 1M+ QPS = 1 second
    
    Redis advantage: 100-1000x faster than disk databases

Memory Trade-off:
    Disk: Cheap ($0.10/GB SSD)
    RAM: Expensive ($8/GB typical)
    
    Strategy: Use Redis for hot data (frequently accessed)
              Use PostgreSQL/MongoDB for cold data (rarely accessed)

Use Case: E-commerce product page
    Hot data (cache in Redis): Product details, price, inventory (accessed every view)
    Cold data (keep in PostgreSQL): Order history, reviews (accessed occasionally)

Twitter Timeline (2009 - MySQL Only):

User requests timeline:
    1. Fetch user's following list (500 people)
       Query: SELECT following_id FROM followers WHERE user_id = 12345
       Result: 500 user IDs
    
    2. Fetch recent tweets from all 500 people
       Query: SELECT * FROM tweets 
              WHERE user_id IN (500 IDs) 
              ORDER BY created_at DESC 
              LIMIT 100
       Result: Scan 500 users' tweets (50K tweets total)
       Sort by time, return top 100
    
    3. Fetch tweet metadata (replies, retweets, likes)
       Query: Multiple JOINs for each tweet
    
    Problem:
        - Database: Scans 50K tweets per timeline request
        - Latency: 2-5 seconds (unacceptable)
        - Load: 10B timeline views/day × 50K tweets = 500 trillion scans/day!
        - MySQL: Can't handle load, frequent outages
        - "Fail Whale": Infamous error message (2008-2010)

Timeline Load Calculation:
    Without cache:
        - Timeline requests: 10 billion/day
        - Tweets scanned per request: 50,000
        - Total scans: 10B × 50K = 500 trillion/day
        - MySQL capacity: 10K QPS = 864M queries/day
        - Needed capacity: 500T ÷ 864M = 578,000x MySQL capacity!
        - Result: Impossible without caching

Twitter's Fan-out Architecture with Redis:

When user tweets (Fan-out on Write):
    1. User posts tweet
       "Hello world!" from @user123 (10M followers)
    
    2. Twitter writes to MySQL (permanent storage)
       INSERT INTO tweets VALUES (tweet_id, user_id, content, created_at)
    
    3. Twitter fans out to followers' timelines (Redis)
       For each of 10M followers:
           Redis LPUSH timeline:follower_id tweet_id
       
       Fan-out workers: 1,000 parallel workers
       Time: 10M followers ÷ 1,000 workers = 10,000 per worker = 10 seconds
       
       Why acceptable: Async process, user doesn't wait

When user views timeline (Fan-out on Read):
    1. User requests timeline
       Redis LRANGE timeline:12345 0 99
       
       Returns: [tweet_id_1, tweet_id_2, ..., tweet_id_100]
       Time: <1ms (Redis in-memory list operation)
    
    2. Fetch tweet content (batch query)
       Redis MGET tweet:1 tweet:2 ... tweet:100
       
       Returns: Tweet objects (text, author, timestamp)
       Time: 2ms (Redis hash operations)
    
    3. Return to user
       Total latency: 3ms (vs 2-5 seconds MySQL)
       Performance improvement: 666-1666x faster!

Redis Data Structures Used:

1. Timeline Lists (per user):
   Key: timeline:12345
   Type: List (ordered, FIFO)
   Value: [tweet_id_1, tweet_id_2, ..., tweet_id_100]
   
   Operations:
       LPUSH timeline:12345 tweet_id_999  # Add to front
       LRANGE timeline:12345 0 99         # Get 100 recent
       LTRIM timeline:12345 0 999         # Keep only 1000 tweets
   
   Memory: 1,000 tweet IDs × 8 bytes = 8 KB per user
   Total: 550M users × 8 KB = 4.4 TB (all timelines)

2. Tweet Content (per tweet):
   Key: tweet:999
   Type: Hash (key-value pairs)
   Value: {
       "user_id": 12345,
       "username": "user123",
       "text": "Hello world!",
       "created_at": "2024-01-15T10:30:00Z",
       "retweets": 150,
       "likes": 1250
   }
   
   Operations:
       HGETALL tweet:999         # Get entire tweet
       HINCRBY tweet:999 likes 1 # Increment likes
   
   Memory: 500 bytes per tweet
   Recent: 1 billion tweets × 500 bytes = 500 GB

3. User Profile Cache:
   Key: user:12345
   Type: Hash
   Value: {
       "username": "user123",
       "display_name": "John Doe",
       "followers": 10000000,
       "following": 500,
       "bio": "...",
       "avatar": "https://..."
   }
   
   Memory: 2 KB per user
   Total: 550M users × 2 KB = 1.1 TB

Total Redis Memory: 4.4 TB + 0.5 TB + 1.1 TB = 6 TB

Deployment:
    Redis Cluster: 1,000+ nodes
    Replication: Each shard has 1 master + 2 replicas (3x redundancy)
    Total instances: 3,000+ Redis instances
    Memory per node: 256 GB RAM
    Total memory: 750 TB (3,000 × 256 GB)
    Utilization: 6 TB data ÷ 750 TB = 0.8% (massive headroom for spikes)

Cluster Configuration:
    Hash slots: 16,384 (divided among masters)
    Masters: 1,000 nodes (16 slots each average)
    Sharding: Consistent hashing on user_id
    
    Example:
        timeline:12345 → CRC16(12345) % 16384 = slot 8192
        Slot 8192 → Master node #512
        Read operation → Can use replica (load balancing)

Read vs Write Pattern:
    Writes: 500M tweets/day = 5,787 tweets/sec
    Reads: 10B timeline views/day = 115,740 reads/sec
    
    Ratio: 20:1 read-heavy (typical caching workload)
    
    Optimization: Read replicas
        Master: Handles writes (5,787/sec across 1,000 nodes = 6/sec each)
        Replicas: Handle reads (115K/sec across 2,000 replicas = 58/sec each)
        
        Load distribution: Master 0.6%, Replicas 99.4%

Persistence Strategy:
    RDB Snapshots: Daily (full dump to disk)
        - Size: 6 TB per snapshot
        - Time: 10 minutes (parallel across nodes)
        - Purpose: Disaster recovery
        - Retention: 7 days
    
    AOF (Append-Only File): Disabled
        - Reason: Timelines can be rebuilt from MySQL
        - Trade-off: Faster writes, acceptable data loss (rebuild from MySQL)
    
    Replication: Synchronous (within cluster)
        - Master write → Replicate to 2 replicas
        - Lag: <10ms (in-memory replication is fast)

Eviction Policy:
    Policy: allkeys-lru (Least Recently Used)
    
    When memory full:
        1. Identify least recently accessed keys
        2. Evict oldest keys first
        3. Make room for new data
    
    Example:
        User hasn't checked timeline in 30 days
        Key: timeline:inactive_user evicted
        Next access: Rebuild from MySQL (cold cache, slower but rare)
    
    Eviction rate: <1% (750 TB capacity, 6 TB used = no pressure)

Throughput:
    Total operations: 1M+ ops/second (across cluster)
    Per node: 1,000 ops/second average (low utilization)
    Peak capacity: 100M+ ops/second (1,000 nodes × 100K each)
    Headroom: 100x (ready for 100x traffic spike)

Latency (P95):
    Timeline fetch: <1ms (LRANGE operation)
    Tweet content: <2ms (MGET batch operation)
    User profile: <1ms (HGETALL operation)
    Write (tweet): <3ms (LPUSH to 10M followers via fan-out)

Availability:
    Uptime: 99.99% (Redis cluster)
    Failover: <1 second (automatic promotion)
        - Master fails → Replica promoted automatically
        - Clients reconnect (automatic retry)
        - No data loss (synchronous replication)

Cache Hit Rate:
    Timeline cache: 95% (most users check timeline regularly)
    Tweet cache: 90% (recent tweets cached, old tweets in MySQL)
    User profile: 98% (popular users always cached)
    
    Miss handling:
        1. Check Redis (1ms)
        2. If miss, query MySQL (100ms)
        3. Populate Redis cache (1ms)
        4. Return to user
        Total: 102ms (acceptable for cache miss)

Memory Efficiency:
    Used: 6 TB
    Allocated: 750 TB (3,000 nodes × 256 GB)
    Waste: 744 TB (99% unused!)
    
    Why over-provision:
        - Traffic spikes (World Cup, elections)
        - Marketing events (Super Bowl ads)
        - Black Friday (shopping tweets)
        - Safety margin (avoid evictions)

Before Redis (2009):
    Timeline latency: 2-5 seconds (MySQL scans)
    Throughput: 10K requests/sec max (MySQL capacity)
    Outages: Frequent "Fail Whale" (database overload)
    User experience: Slow, often down

After Redis (2010-2024):
    Timeline latency: <3ms (Redis in-memory)
    Throughput: 1M+ requests/sec (Redis cluster)
    Outages: Rare (Redis handles load)
    User experience: Fast, reliable

Performance Improvements:
    Latency: 2-5s → 3ms (666-1666x faster)
    Throughput: 10K → 1M+ QPS (100x increase)
    Availability: 95% → 99.99% (massive improvement)
    Database load: 99% reduction (Redis absorbs reads)

Cost Analysis:
    MySQL-only (Hypothetical):
        - Needed: 578,000 MySQL servers (impossible!)
        - Cost: $578M/year (unrealistic)
        - Complexity: Unmanageable
    
    Redis + MySQL (Actual):
        - Redis: 3,000 instances @ $2K/month = $6M/month = $72M/year
        - MySQL: 100 instances (writes only) @ $5K/month = $6M/year
        - Total: $78M/year
        - Engineers: 20 engineers @ $200K = $4M/year
        - Grand total: $82M/year
    
    Value: Enabled Twitter's growth from 10M to 550M users
           Without Redis, Twitter couldn't exist at this scale

Business Impact:
    User growth: 10M (2009) → 550M (2024) = 55x
    Enabled features: Real-time updates, trending topics, notifications
    Revenue: $5.1 billion (2022, before X rebrand)
    Redis cost: $82M = 1.6% of revenue (high ROI)

1. Strings (Simple key-value):
   Use case: Session tokens, counters, flags
   
   SET user:12345:token "abc-def-ghi-jkl"
   GET user:12345:token
   INCR page:views:count
   EXPIRE user:12345:token 3600  # Auto-delete after 1 hour

2. Hashes (Object storage):
   Use case: User profiles, tweet objects
   
   HSET user:12345 name "John" email "john@example.com" followers 10000
   HGET user:12345 followers
   HINCRBY user:12345 followers 1

3. Lists (Ordered collections):
   Use case: Timelines, message queues
   
   LPUSH timeline:12345 tweet:999  # Add to front
   LRANGE timeline:12345 0 99      # Get 100 items
   LTRIM timeline:12345 0 999      # Keep only 1000 items

4. Sets (Unique collections):
   Use case: Followers, tags, unique visitors
   
   SADD followers:12345 user:111 user:222 user:333
   SISMEMBER followers:12345 user:111  # Check if member
   SCARD followers:12345  # Count members
   SINTER followers:12345 followers:67890  # Common followers

5. Sorted Sets (Scored collections):
   Use case: Leaderboards, trending topics, priority queues
   
   ZADD leaderboard 100 "player1" 95 "player2" 85 "player3"
   ZRANGE leaderboard 0 9 WITHSCORES  # Top 10
   ZINCRBY leaderboard 5 "player1"    # Add 5 points
   ZRANK leaderboard "player1"        # Get rank

6. Bitmaps (Space-efficient flags):
   Use case: Daily active users, feature flags
   
   SETBIT daily_active:2024-01-15 12345 1  # User 12345 active
   GETBIT daily_active:2024-01-15 12345    # Check if active
   BITCOUNT daily_active:2024-01-15        # Count active users
   
   Memory: 550M users = 550M bits = 69 MB (vs 4.4 GB strings!)

7. HyperLogLog (Cardinality estimation):
   Use case: Unique visitors, distinct values
   
   PFADD unique_visitors user:12345 user:67890
   PFCOUNT unique_visitors  # Approximate count
   
   Memory: 12 KB per HyperLogLog (regardless of cardinality!)
   Accuracy: 0.81% standard error (good enough for estimates)

8. Streams (Event logs):
   Use case: Activity feeds, notifications, event sourcing
   
   XADD notifications * user_id 12345 type "like" tweet_id 999
   XREAD COUNT 10 STREAMS notifications 0
   
   Features: Consumer groups, acknowledgment, persistence

9. Geospatial (Location data):
   Use case: Nearby users, location-based search
   
   GEOADD drivers 13.361389 38.115556 "driver:1"  # Palermo
   GEORADIUS drivers 15 37 100 km  # Find drivers within 100km

10. Pub/Sub (Real-time messaging):
    Use case: Live updates, chat, notifications
    
    PUBLISH notifications "New tweet from @user123"
    SUBSCRIBE notifications
    
    Pattern: Fire-and-forget (not persisted)

Feature Comparison:

Data Structures:
    Memcached: Only strings (key-value)
    Redis: 10+ types (strings, hashes, lists, sets, sorted sets, etc.)
    Winner: Redis (timelines need lists, leaderboards need sorted sets)

Persistence:
    Memcached: None (RAM only, data lost on restart)
    Redis: RDB snapshots + AOF logs (survives restarts)
    Winner: Redis (cache warm after restart)

Replication:
    Memcached: None (client-side sharding only)
    Redis: Master-replica (built-in, automatic failover)
    Winner: Redis (high availability)

Clustering:
    Memcached: Client-side (consistent hashing)
    Redis: Redis Cluster (automatic sharding, 16K slots)
    Winner: Redis (easier management)

Performance:
    Memcached: 1M+ ops/sec (slightly faster for simple GET/SET)
    Redis: 1M+ ops/sec (similar, slight overhead for features)
    Winner: Tie (both very fast)

Memory Efficiency:
    Memcached: Slab allocation (can waste memory)
    Redis: jemalloc (efficient allocation)
    Winner: Redis (better memory usage)

Atomic Operations:
    Memcached: Limited (incr/decr only)
    Redis: Extensive (HINCRBY, ZINCRBY, SETBIT, etc.)
    Winner: Redis (atomic counters without read-modify-write)

Pub/Sub:
    Memcached: None
    Redis: Built-in (PUBLISH/SUBSCRIBE)
    Winner: Redis (real-time notifications)

Lua Scripting:
    Memcached: None
    Redis: Full Lua support (EVAL command)
    Winner: Redis (complex operations in single request)

Twitter's Decision: Redis wins 9 of 10 categories
Memcached advantage: Slightly simpler (not a factor at Twitter's scale)

Company: Amazon Web Services (AWS)
Launch: January 2012 (12+ years in production)
Revenue: Part of AWS ($90B total 2023, DynamoDB ~$8B estimated)
Customers: Millions of AWS customers using DynamoDB
Scale: Trillions of requests per day (Amazon-wide)
Performance: Single-digit millisecond latency at any scale
Market share: 12% of NoSQL databases (#4 overall, #1 managed)

Key Characteristics:
    - Fully managed (no servers, no operations)
    - Serverless (pay per request, auto-scales)
    - Global tables (multi-region replication)
    - ACID transactions (since 2018)
    - Encryption at rest/transit (automatic)
    - Point-in-time recovery (35 days)
    - Integration: Native AWS (Lambda, API Gateway, S3)

Self-Managed (Cassandra, MongoDB):
    Operations:
        Full control (configuration, tuning)
        Manual scaling (add nodes, rebalance)
        Manual backups (schedule, test, monitor)
        Manual security (patching, encryption, IAM)
        Manual monitoring (metrics, alerts, dashboards)
        On-call required (24/7 database emergencies)
    
    Cost:
        - Compute: $500K/year (servers)
        - Staff: 3 DBAs × $200K = $600K/year
        - Total: $1.1M/year

DynamoDB (Managed):
    Operations:
        Zero operations (AWS manages everything)
        Auto-scaling (capacity adjusts automatically)
        Automatic backups (point-in-time recovery)
        Automatic security (encryption, patching, IAM)
        Built-in monitoring (CloudWatch metrics)
        No on-call (AWS handles database issues)
    
    Cost:
        - Pay per request: $1.25 per million writes
        - Staff: 0 DBAs (AWS manages)
        - Total: $800K/year (typical workload)
    
    Savings: $300K/year + zero operational burden

Trade-off: Less control, AWS-only, eventual consistency default
When it matters: When operational simplicity > customization

Functional Requirements:
    1. Add/remove items to cart
    2. Update quantities
    3. Store cart state (persist across sessions)
    4. Share cart across devices (web, mobile, Alexa)
    5. Cart abandonment tracking (marketing)

Non-Functional Requirements:
    1. High availability: 99.99%+ (no downtime during purchase)
    2. Low latency: <10ms P95 (fast page loads)
    3. Global access: Serve users worldwide (multi-region)
    4. Scalability: Handle Prime Day (100x normal traffic)
    5. Durability: Never lose cart data (even during failures)
    6. Consistency: Eventual is OK (cart can be slightly stale)

Why These Requirements Matter:
    - High availability: $1 cart abandonment = $100 lost sale (1% conversion)
    - Low latency: 100ms delay = 1% revenue loss (Amazon study)
    - Prime Day: 100x traffic spike = need auto-scaling
    - Multi-region: Europe/Asia users need local access (low latency)

Traditional Database Challenges:

PostgreSQL/MySQL:
    ACID transactions (strong consistency)
    Complex queries (JOINs, aggregations)
    Scaling: Sharding complex (application-level)
    Availability: Single master = downtime during failover
    Latency: 10-50ms (disk-based, network hops)
    Operations: Manual backups, scaling, monitoring
    
    Problem for shopping cart:
        - Need 99.99% availability (PostgreSQL 99.9% typical)
        - 0.09% downtime = 786 hours/year unavailable
        - At $1M/hour revenue = $786M lost annually!

Cassandra:
    High availability (masterless, no single point)
    Linear scalability (add nodes = add capacity)
    Low latency (<10ms reads)
    Operations: 10+ node cluster to manage
    No managed service on AWS (self-host)
    Requires DBA team (3+ engineers)
    
    Problem: Operational burden
        - 3 DBAs × $200K = $600K/year
        - On-call rotations (24/7 monitoring)
        - Capacity planning (predict Prime Day load)

DynamoDB:
    High availability: 99.99%+ (AWS SLA, multi-AZ)
    Scalability: Automatic (no capacity planning)
    Low latency: <10ms P95 (consistent)
    Global: Multi-region replication (built-in)
    Zero operations: Fully managed (no DBAs)
    Pay per request: No upfront provisioning
    Eventual consistency: Default (acceptable for cart)
    Limited queries: No JOINs (design for key-value)
    
    Perfect fit: All requirements met, zero operations

DynamoDB Table Design:

Table: ShoppingCarts
Partition Key: user_id (distributes across partitions)
Sort Key: item_id (multiple items per user)

Item Structure:
{
    "user_id": "user-12345",           // Partition key
    "item_id": "item-67890",           // Sort key
    "product_name": "Kindle Paperwhite",
    "asin": "B08KTZ8249",              // Amazon product ID
    "quantity": 2,
    "price": 139.99,
    "currency": "USD",
    "added_at": "2024-01-15T10:30:00Z",
    "last_modified": "2024-01-15T11:45:00Z",
    "image_url": "https://m.media-amazon.com/...",
    "seller_id": "seller-456",
    "prime_eligible": true,
    "in_stock": true,
    "delivery_date": "2024-01-18",
    "ttl": 1738368000                  // Auto-delete after 30 days (epoch)
}

Key Design Decisions:

1. Partition Key = user_id:
   - All cart items for same user co-located (fast queries)
   - User's cart = single partition read (<5ms)
   - DynamoDB distributes users across partitions (even load)

2. Sort Key = item_id:
   - Multiple items per user (1:N relationship)
   - Query pattern: Get all items for user
   - DynamoDB query: O(1) to find partition, O(log N) to scan items

3. TTL (Time To Live):
   - Automatically delete abandoned carts after 30 days
   - Free deletion (DynamoDB handles, no code needed)
   - Reduces storage costs (90% of carts abandoned)

4. Denormalized Design:
   - Product name, price, image stored in cart (no JOIN)
   - Trade-off: Data duplication vs fast reads
   - Why: Cart reads 100x more than product changes
   - Price change: Update cart items (background job)

1. Add Item to Cart:
   API: PutItem
   
   aws dynamodb put-item \
     --table-name ShoppingCarts \
     --item '{
       "user_id": {"S": "user-12345"},
       "item_id": {"S": "item-67890"},
       "product_name": {"S": "Kindle Paperwhite"},
       "quantity": {"N": "1"},
       "price": {"N": "139.99"},
       "added_at": {"S": "2024-01-15T10:30:00Z"},
       "ttl": {"N": "1738368000"}
     }'
   
   Latency: <5ms P95
   Cost: 1 write capacity unit (WCU) = $0.00000125

2. Get User's Cart (All Items):
   API: Query (not Scan - important!)
   
   aws dynamodb query \
     --table-name ShoppingCarts \
     --key-condition-expression "user_id = :uid" \
     --expression-attribute-values '{":uid": {"S": "user-12345"}}'
   
   Returns: All items for user (up to 1 MB per query)
   Latency: <5ms P95 (single partition read)
   Cost: 1 read capacity unit (RCU) per 4 KB = $0.00000025

3. Update Quantity:
   API: UpdateItem (atomic operation)
   
   aws dynamodb update-item \
     --table-name ShoppingCarts \
     --key '{"user_id": {"S": "user-12345"}, "item_id": {"S": "item-67890"}}' \
     --update-expression "SET quantity = :q, last_modified = :t" \
     --expression-attribute-values '{
       ":q": {"N": "3"},
       ":t": {"S": "2024-01-15T11:45:00Z"}
     }'
   
   Atomic: No read-modify-write needed (DynamoDB handles)
   Latency: <5ms P95
   Cost: 1 WCU = $0.00000125

4. Remove Item from Cart:
   API: DeleteItem
   
   aws dynamodb delete-item \
     --table-name ShoppingCarts \
     --key '{"user_id": {"S": "user-12345"}, "item_id": {"S": "item-67890"}}'
   
   Latency: <5ms P95
   Cost: 1 WCU = $0.00000125

5. Clear Cart (After Checkout):
   API: BatchWriteItem (up to 25 items per batch)
   
   # Delete all items for user (batch operation)
   aws dynamodb batch-write-item \
     --request-items '{
       "ShoppingCarts": [
         {"DeleteRequest": {"Key": {"user_id": {"S": "user-12345"}, "item_id": {"S": "item-1"}}}},
         {"DeleteRequest": {"Key": {"user_id": {"S": "user-12345"}, "item_id": {"S": "item-2"}}}},
         ...
       ]
     }'
   
   Latency: <10ms P95 (parallel deletes)
   Cost: N WCUs (one per item)

On-Demand Mode (Amazon.com Uses This):
    Pricing: Pay per request
        - Write: $1.25 per million writes
        - Read: $0.25 per million reads
    
    Scaling: Automatic (no provisioning)
        - DynamoDB scales to any load
        - No capacity planning needed
        - Handles Prime Day 100x spike automatically
    
    When to use:
        Unpredictable traffic (Prime Day, Black Friday)
        Don't want capacity planning
        Prefer simplicity over cost optimization
    
    Amazon.com Shopping Cart Usage:
        - Reads: 50 billion/month (cart views)
        - Writes: 5 billion/month (add/update/remove)
        - Read cost: 50B × $0.00000025 = $12,500/month
        - Write cost: 5B × $0.00000125 = $6,250/month
        - Total: $18,750/month = $225K/year
        
        + Storage: 10 TB × $0.25/GB = $2,500/month = $30K/year
        + Backups: Continuous (35 days) = $5K/month = $60K/year
        
        Grand Total: $315K/year (shopping cart database)

Provisioned Mode (Cost Optimization):
    Pricing: Pay for capacity (regardless of usage)
        - Write: $0.00065 per WCU per hour
        - Read: $0.00013 per RCU per hour
    
    Scaling: Manual or auto-scaling (predict capacity)
    
    When to use:
        Predictable traffic (consistent load)
        Want cost optimization (30-50% cheaper)
        Can handle capacity planning
    
    Example: 50K WCU + 250K RCU (steady-state)
        Write cost: 50K × $0.00065 × 730 hours = $23,725/month
        Read cost: 250K × $0.00013 × 730 hours = $23,725/month
        Total: $47,450/month = $569K/year
        
        Savings vs on-demand: $569K - $225K = -$344K (MORE expensive!)
        
    Why: Provisioned only cheaper if consistent load
         Amazon.com has spiky traffic (Prime Day)
         On-demand better for unpredictable workloads

Amazon's Multi-Region Architecture:

Regions:
    - us-east-1 (Virginia) - North America users
    - eu-west-1 (Ireland) - Europe users
    - ap-northeast-1 (Tokyo) - Asia users

Global Table: ShoppingCarts (replicated across 3 regions)

How it works:
    1. User in Tokyo adds item to cart
       Write to ap-northeast-1 (local, <5ms)
    
    2. DynamoDB replicates to other regions
       ap-northeast-1 → us-east-1 (async, 100-500ms)
       ap-northeast-1 → eu-west-1 (async, 150-600ms)
    
    3. User switches to laptop in New York
       Read from us-east-1 (local, <5ms)
       Cart already replicated (appears instantly)

Consistency Model:
    - Last-writer-wins (conflict resolution)
    - Eventual consistency (typical 1 second lag)
    
    Example conflict:
        Mobile app (Tokyo): Sets quantity = 3 at 10:00:00.000
        Web app (Virginia): Sets quantity = 5 at 10:00:00.100
        
        Resolution: Virginia wins (newer timestamp)
        Result: quantity = 5 in all regions (after replication)
    
    Why acceptable for shopping cart:
        - Conflicts rare (same user, different devices, same second)
        - When happens: User likely intended latest change
        - Worst case: User sees old quantity briefly (refreshes, sees correct)

Cost: 1.25× base cost (writes replicated to 3 regions)
    On-demand writes: $1.25 × 1.25 = $1.56 per million
    Benefit: Low latency globally (<10ms anywhere)
    Amazon's decision: Worth the cost (better UX = more sales)

Amazon.com Shopping Cart Metrics (Estimated):

Traffic:
    - Monthly cart views: 50 billion (1.6B per day)
    - Add to cart: 3 billion/month (100M per day)
    - Update cart: 1.5 billion/month (50M per day)
    - Remove from cart: 500 million/month (16M per day)
    - Total writes: 5 billion/month (166M per day)
    - Read:Write ratio: 10:1 (typical e-commerce)

Latency (P95):
    - Read cart: <5ms (single partition query)
    - Write cart: <8ms (write + replication)
    - Global table: +2ms (cross-region)
    - Total user experience: <10ms (feels instant)

Throughput:
    - Peak (Prime Day): 100x normal = 16B cart views/day
    - DynamoDB scales automatically (no manual intervention)
    - Auto-scaling lag: <1 minute (handles spike)
    
    Comparison to PostgreSQL:
        - PostgreSQL: Manual scaling, hours to add capacity
        - DynamoDB: Automatic scaling, seconds to adjust
        - Prime Day surprise spike: PostgreSQL down, DynamoDB fine

Availability:
    - DynamoDB SLA: 99.99% (multi-AZ)
    - Actual: 99.995%+ (Amazon-wide monitoring)
    - Downtime: <5 minutes/year (vs 52 minutes SLA)
    
    Revenue impact:
        - 5 minutes/year downtime = $83K lost (vs $867K with 99.9% SLA)
        - Each additional nine: $867K → $83K → $8.3K
        - Worth the cost: Better availability = fewer lost sales

Durability:
    - DynamoDB: 11 nines (99.999999999%)
    - Multiple AZ replication (3 copies)
    - Point-in-time recovery (35 days)
    - Lost cart: Virtually impossible (1 in 100 billion)

Feature Comparison:

Operations:
    DynamoDB: Zero (fully managed)
    MongoDB Atlas: Low (managed, but some tuning)
    Cassandra: High (self-managed, 10+ node cluster)
    
    Winner: DynamoDB (zero ops = zero on-call)

Scalability:
    DynamoDB: Automatic (scales to any load)
    MongoDB: Manual sharding (add shards, rebalance)
    Cassandra: Manual (add nodes, rebalance)
    
    Winner: DynamoDB (auto-scaling, no planning)

Latency:
    DynamoDB: <10ms P95 (single-digit, consistent)
    MongoDB: <10ms P95 (similar, well-tuned)
    Cassandra: <5ms P95 (faster, but more ops)
    
    Winner: Tie (all provide low latency)

Query Flexibility:
    DynamoDB: Limited (key-value, no JOINs)
    MongoDB: Flexible (aggregations, complex queries)
    Cassandra: Limited (CQL, no JOINs, partition-key focused)
    
    Winner: MongoDB (most flexible queries)

Global Distribution:
    DynamoDB: Built-in (Global Tables, 1 click)
    MongoDB Atlas: Built-in (Global Clusters, configured)
    Cassandra: Built-in (multi-datacenter, manual setup)
    
    Winner: DynamoDB (easiest setup)

Cost (1 TB, 1M requests/sec):
    DynamoDB: $500K/year (on-demand, fully managed)
    MongoDB Atlas: $400K/year (managed, some ops)
    Cassandra: $300K/year (self-managed, 3 DBAs)
    
    Total Cost of Ownership:
        DynamoDB: $500K (database only)
        MongoDB: $400K + $200K (1 DBA) = $600K
        Cassandra: $300K + $600K (3 DBAs) = $900K
    
    Winner: DynamoDB (lowest TCO when including labor)

When to Choose Each:
    DynamoDB: High availability, auto-scaling, zero ops, AWS-native
    MongoDB: Flexible queries, complex aggregations, document model
    Cassandra: Maximum performance, massive scale (>100 TB), multi-cloud

Before DynamoDB (Oracle, 2000s):
    Database: Oracle Enterprise
    Sharding: Manual (application-level)
    Scaling: Weeks of planning (add hardware, migrate data)
    Availability: 99.9% (annual planned downtime)
    Prime Day: Manual capacity increases (often under-provisioned)
    Cost: $10M+/year (licenses, hardware, DBAs)

After DynamoDB (2012-Present):
    Database: DynamoDB (fully managed)
    Sharding: Automatic (DynamoDB handles)
    Scaling: Minutes (auto-scaling)
    Availability: 99.99%+ (no planned downtime)
    Prime Day: Automatic scaling (100x spike handled)
    Cost: $315K/year (shopping cart only, pay per request)

Business Impact:
    - Prime Day 2023: 375M items ordered (no database issues)
    - Revenue: $575 billion (2023, enabled by reliable infrastructure)
    - Shopping cart abandonment: 70% (industry average, not DB-related)
    - Database contribution: Invisible (zero outages = no complaints)

Developer Productivity:
    - API integration: AWS SDK (Python, Java, Node.js)
    - No schema migrations: Schemaless (add fields anytime)
    - No capacity planning: Auto-scaling (DynamoDB handles)
    - No backup management: Point-in-time recovery (automatic)
    - No monitoring setup: CloudWatch metrics (built-in)
    
    Engineer time: 0 hours/month (vs 160 hours/month self-managed)
    Value: Engineers focus on features, not database operations

START: What type of data and access patterns?

Question 1: What's your primary access pattern?
    A) Key-value lookups (get item by ID) → Go to Question 2
    B) Complex queries (JOINs, aggregations) → Go to Question 3
    C) Time-series data (logs, metrics, events) → Go to Question 4
    D) Graph relationships (social network, recommendations) → Go to Question 5

Question 2: Key-Value Access
    Need ACID transactions?
        YES → PostgreSQL (simple tables, great for OLTP)
        NO → Go to sub-question:
            
    Need sub-millisecond latency?
        YES → Redis (in-memory, 1M+ ops/sec)
        NO → Go to sub-question:
            
    Fully managed with auto-scaling?
        YES → DynamoDB (zero ops, perfect for AWS)
        NO → Cassandra (self-managed, multi-cloud)
    
    Examples:
        - Session storage → Redis (fast, TTL support)
        - Shopping cart → DynamoDB (managed, available)
        - User profiles → PostgreSQL (structured, ACID)

Question 3: Complex Queries
    Need strong consistency (ACID)?
        YES → PostgreSQL (best SQL database, mature)
        NO → Go to sub-question:
            
    Schema frequently changes?
        YES → MongoDB (flexible, no migrations)
        NO → PostgreSQL (structured schema better)
    
    Need JSON/document storage?
        YES → MongoDB or PostgreSQL JSONB
        NO → PostgreSQL (pure relational)
    
    Examples:
        - Financial transactions → PostgreSQL (ACID critical)
        - Product catalog → MongoDB (varying attributes)
        - Analytics → PostgreSQL or Redshift (complex JOINs)

Question 4: Time-Series Data
    Volume per day?
        <1 TB → PostgreSQL with TimescaleDB extension
        1-10 TB → Cassandra (write-optimized)
        >10 TB → Specialized (InfluxDB, TimescaleDB, Druid)
    
    Examples:
        - Application logs → Cassandra (high write volume)
        - IoT sensors → TimescaleDB or Cassandra
        - Metrics → InfluxDB or Prometheus

Question 5: Graph Relationships
    Traversal depth?
        1-2 hops → PostgreSQL (recursive CTEs work)
        3+ hops → Neo4j (graph database specialized)
    
    Examples:
        - Friend recommendations → Neo4j (deep traversals)
        - Organization hierarchy → PostgreSQL (shallow)
        - Social network → Neo4j (complex relationships)

Scenario 1: E-commerce Startup (0 to 1M users)

Requirements:
    - Users: 1M (growing)
    - Products: 100K (fixed schema)
    - Orders: 10K/day
    - Budget: $10K/month
    - Team: 3 engineers (full-stack, no DBAs)

Decision:
    Users & Orders → PostgreSQL on RDS
        Why: ACID transactions critical (orders)
             Structured data (users have fixed fields)
             Managed RDS (no DBA needed)
             Cost: $500/month (db.t3.large)
    
    Product Catalog → PostgreSQL (same database)
        Why: 100K products fit easily (< 1 GB)
             JOINs with orders (same database)
             Cost: Included above
    
    Session Storage → Redis on ElastiCache
        Why: Fast login checks (<1ms)
             Managed (no operations)
             Cost: $50/month (cache.t3.micro)
    
    Total: $550/month (well under budget)
    
    Alternative (NOT chosen):
        MongoDB: Overkill (schema is fixed)
        Cassandra: Over-engineered (not petabyte scale)
        DynamoDB: Vendor lock-in (want multi-cloud future)

Scenario 2: Social Media App (10M to 100M users)

Requirements:
    - Users: 100M (growing fast)
    - Posts: 10B (user-generated content)
    - Timeline views: 50B/day
    - Budget: $500K/month
    - Team: 20 engineers, 2 DBAs

Decision:
    User Accounts → PostgreSQL (sharded by user_id)
        Why: ACID for auth, payments
             Structured data (fixed schema)
             Sharding: 10 PostgreSQL clusters (10M users each)
             Cost: $100K/month (managed RDS, 10 clusters)
    
    Posts & Content → Cassandra
        Why: 10B posts = massive scale
             Write-heavy (users posting constantly)
             No JOINs needed (denormalized)
             Cost: $200K/month (50-node cluster)
    
    Timeline Cache → Redis Cluster
        Why: 50B views/day = sub-ms latency required
             Fan-out pattern (Twitter model)
             Cost: $150K/month (500-node cluster)
    
    Analytics → Redshift (separate warehouse)
        Why: Complex queries (user growth, engagement)
             Not real-time (hourly/daily reports)
             Cost: $50K/month
    
    Total: $500K/month (at budget)

Scenario 3: IoT Platform (1M devices, 1TB data/day)

Requirements:
    - Devices: 1M (sensors reporting)
    - Events: 10B/day (time-series data)
    - Data volume: 1 TB/day (growing)
    - Queries: Recent data only (last 30 days)
    - Budget: $200K/month

Decision:
    Time-Series Data → Cassandra
        Why: 1 TB/day = 30 TB hot data
             Write-optimized (10B writes/day)
             Time-based partitioning (auto-expire old data)
             Cost: $150K/month (100-node cluster)
    
    Device Metadata → PostgreSQL
        Why: 1M devices = manageable (< 1 GB)
             Relational (devices → customers → accounts)
             ACID for billing
             Cost: $5K/month (single instance)
    
    Real-time Aggregations → Redis
        Why: Dashboard needs current metrics
             Counter operations (INCR, HINCRBY)
             Cost: $20K/month (20-node cluster)
    
    Cold Storage → S3 + Athena
        Why: Data older than 30 days (rarely queried)
             $0.023/GB storage = $700/month (30 TB)
             Query on-demand (Athena)
    
    Total: $175K/month (under budget)

Scenario: 1 TB data, 100K requests/sec, 99.99% availability

Self-Managed PostgreSQL:
    Hardware: 10 servers (sharded) × $2K/month = $20K/month
    Staff: 2 DBAs × $200K/year ÷ 12 = $33K/month
    Backups: S3 storage = $500/month
    Monitoring: Datadog = $1K/month
    Total: $54.5K/month = $654K/year

Self-Managed Cassandra:
    Hardware: 30 nodes × $2K/month = $60K/month
    Staff: 3 DBAs × $200K/year ÷ 12 = $50K/month
    Backups: S3 storage = $1K/month
    Monitoring: Datadog = $2K/month
    Total: $113K/month = $1.356M/year

AWS RDS PostgreSQL (Managed):
    Database: db.r6g.4xlarge × 10 = $30K/month
    Multi-AZ: 2x for HA = $60K/month
    Backups: Included (automated)
    Monitoring: CloudWatch included
    Staff: 0 DBAs (managed)
    Total: $60K/month = $720K/year

MongoDB Atlas (Managed):
    Compute: M60 × 3 (replica set) = $40K/month
    Sharding: 10 shards × $40K = $400K/month
    Backups: Included
    Staff: 1 engineer × $200K/year ÷ 12 = $17K/month
    Total: $417K/month = $5M/year (expensive!)

DynamoDB (Fully Managed):
    On-demand: $1.25 per 1M writes × 100K/sec = $300K/month
    Storage: 1 TB × $0.25/GB = $250/month
    Backups: Continuous = $2K/month
    Staff: 0 (fully managed)
    Total: $302K/month = $3.6M/year

Redis Enterprise (Managed):
    Compute: 100 GB RAM × 10 nodes = $100K/month
    Replication: 2x for HA = $200K/month
    Backups: Included
    Staff: 0 (fully managed)
    Total: $200K/month = $2.4M/year

Cost Ranking (1 TB, 100K QPS):
    1. Self-Managed PostgreSQL: $654K/year (cheapest, most ops)
    2. RDS PostgreSQL: $720K/year (best value, managed)
    3. Self-Managed Cassandra: $1.356M/year (more ops)
    4. Redis Enterprise: $2.4M/year (in-memory expensive)
    5. DynamoDB: $3.6M/year (pay-per-request high at this scale)
    6. MongoDB Atlas: $5M/year (most expensive)

Key Insight: "Managed" doesn't always mean cheaper
    - DynamoDB expensive at high sustained load (better for spiky)
    - PostgreSQL cheapest (mature, efficient, SQL standard)
    - MongoDB expensive (fewer companies, less competition)
    - Redis expensive (RAM costs more than disk)
    - Trade-off: Operations cost vs infrastructure cost

When to pay more:
    Small team (no DBAs) → Choose managed
    Unpredictable load (spiky) → Choose auto-scaling (DynamoDB)
    Rapid growth → Choose scalable (Cassandra, DynamoDB)
    Mission-critical → Choose managed (99.99% SLA)

CAP Theorem: Pick 2 of 3
    - Consistency: All nodes see same data at same time
    - Availability: System always responds to requests
    - Partition tolerance: System works despite network failures

Real-World Trade-offs:

CP (Consistency + Partition tolerance, sacrifice Availability):
    PostgreSQL, MySQL (single master)
    
    Scenario: Master-replica replication
        Network partition: Master and replica can't communicate
        Decision: Only master serves traffic (consistency maintained)
        Result: Replica unavailable (availability sacrificed)
    
    When to choose:
        - Banking: Consistency critical (can't show wrong balance)
        - Inventory: Can't sell items twice (stock must be accurate)
        - Reservations: Double-booking unacceptable
    
    Example - Bank Transfer:
        User transfers $100 (balance check required)
        Network split: Can't check balance on replica
        Choice: Deny transaction (protect consistency)
        User impact: "Service temporarily unavailable" (frustrating but safe)

AP (Availability + Partition tolerance, sacrifice Consistency):
    Cassandra, DynamoDB (multi-master)
    
    Scenario: Multi-datacenter replication
        Network partition: US and Europe can't communicate
        Decision: Both datacenters serve traffic (availability maintained)
        Result: Temporarily inconsistent data (consistency sacrificed)
    
    When to choose:
        - Social media: Likes can be eventually consistent
        - Shopping cart: Slight staleness acceptable
        - Content: Blog posts don't need instant sync
    
    Example - Social Media Like:
        User likes post in US datacenter
        Network split: Europe datacenter doesn't see like yet
        Choice: Show success to user (write succeeded locally)
        User impact: Friend in Europe sees like 5 seconds later (acceptable)

CA (Consistency + Availability, sacrifice Partition tolerance):
    Single-datacenter databases (traditional setup)
    
    Scenario: Single datacenter, no network partitions
        All servers in same rack/datacenter (low latency network)
        Network reliable (99.999% uptime within datacenter)
        Both consistency and availability achievable
    
    Problem: Not partition-tolerant
        Datacenter fails: Entire system down
        Network issue: System unavailable
    
    When to choose:
        - Small scale: <10K users, single region
        - Controlled environment: On-premises, reliable network
        - Legacy: Existing architecture, no multi-datacenter need
    
    Reality: Most companies need Partition tolerance
        Cloud: Multi-AZ required (network partitions possible)
        Scale: Multiple datacenters (geo-distribution)
        Modern: Distributed systems standard (not optional)

ACID (Atomicity, Consistency, Isolation, Durability):
    Use cases:
        Financial transactions (money can't vanish)
        Inventory management (can't oversell)
        Booking systems (no double-booking)
        User authentication (password changes immediate)
        E-commerce orders (payment + inventory + shipping atomic)
    
    Databases: PostgreSQL, MySQL, Oracle, SQL Server
    
    Trade-off: Availability and performance for correctness
    
    Example - E-commerce Order:
        BEGIN TRANSACTION;
            -- Deduct inventory
            UPDATE products SET stock = stock - 1 WHERE id = 123;
            -- Charge payment
            INSERT INTO payments VALUES (user_id, amount, 'charged');
            -- Create shipment
            INSERT INTO shipments VALUES (order_id, address);
        COMMIT;
        
        Guarantee: All succeed or all fail (no partial orders)

BASE (Basically Available, Soft state, Eventually consistent):
    Use cases:
        Social media (likes, comments can lag)
        Analytics (dashboards can be stale)
        Content delivery (articles sync eventually)
        Search indexes (slight delay acceptable)
        Caching (cache can be outdated briefly)
    
    Databases: Cassandra, DynamoDB, MongoDB, Redis
    
    Trade-off: Correctness for availability and performance
    
    Example - Social Media Post:
        User posts "Hello World!"
        Write to US datacenter (immediate)
        Replicate to Europe (100ms delay)
        Replicate to Asia (300ms delay)
        
        Result: Eventually all users see post (not instant)

Hybrid Approach (Best of Both Worlds):
    Use ACID where needed, BASE where acceptable
    
    Example - E-commerce Platform:
        Orders → PostgreSQL (ACID, can't lose orders)
        Product catalog → MongoDB (BASE, descriptions can lag)
        Shopping cart → Redis (BASE, cart can be stale)
        Session → Redis (BASE, re-login acceptable)
        Analytics → Cassandra (BASE, metrics can lag)
    
    Result: Critical data protected, performance optimized

1. Use EXPLAIN to Understand Query Plans:
   
   Bad Query (Full Table Scan):
       SELECT * FROM users WHERE email = 'user@example.com';
       
       EXPLAIN output:
           Seq Scan on users (cost=0.00..1750.00 rows=1 width=100)
           Filter: (email = 'user@example.com'::text)
       
       Problem: Scans all 100K users (slow)
       Time: 500ms

   Good Query (Index Scan):
       CREATE INDEX idx_users_email ON users(email);
       SELECT * FROM users WHERE email = 'user@example.com';
       
       EXPLAIN output:
           Index Scan using idx_users_email on users (cost=0.29..8.31 rows=1 width=100)
           Index Cond: (email = 'user@example.com'::text)
       
       Improvement: Uses index (fast lookup)
       Time: 5ms (100x faster)

2. Avoid SELECT * (Request Only Needed Columns):
   
   Bad: SELECT * FROM orders WHERE user_id = 123;
   Good: SELECT order_id, total, status FROM orders WHERE user_id = 123;
   
   Why better:
       - Less data transferred (50 bytes vs 500 bytes)
       - Index-only scan possible (no table access)
       - Network bandwidth saved (10x less)

3. Use Proper JOIN Order:
   
   Bad: SELECT * FROM orders o 
        JOIN users u ON o.user_id = u.id
        WHERE o.created_at > '2024-01-01';
   
   Query plan: Scan all orders, JOIN all users, FILTER dates
   Rows processed: 10M orders × 1M users = 10 trillion comparisons!
   
   Good: SELECT * FROM orders o
         WHERE o.created_at > '2024-01-01'
         JOIN users u ON o.user_id = u.id;
   
   Query plan: FILTER dates first (100K orders), then JOIN
   Rows processed: 100K orders × 1M users = 100M comparisons
   Improvement: 100,000x fewer comparisons!

4. Batch Operations (Not 1-by-1):
   
   Bad: 
       for user_id in user_ids:
           db.execute("INSERT INTO logs VALUES (%s, %s)", (user_id, event))
       
       Problem: 1,000 users = 1,000 database round trips
       Time: 1,000 × 5ms = 5,000ms (5 seconds!)
   
   Good:
       db.execute_batch("INSERT INTO logs VALUES (%s, %s)", data)
       
       Improvement: 1 database round trip
       Time: 50ms (100x faster)

5. Use Connection Pooling:
   
   Bad (New Connection Per Request):
       def handle_request():
           conn = psycopg2.connect("postgresql://...")
           result = conn.execute("SELECT ...")
           conn.close()
       
       Problem: Connection overhead (50-100ms per connect)
   
   Good (Connection Pool):
       pool = psycopg2.pool.SimpleConnectionPool(10, 50)
       
       def handle_request():
           conn = pool.getconn()  # Reuse existing (1ms)
           result = conn.execute("SELECT ...")
           pool.putconn(conn)
       
       Improvement: 50-100x faster (no connection overhead)

1. Index Columns Used in WHERE, JOIN, ORDER BY:
   
   Query: SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;
   
   Index needed: (user_id, created_at)
   CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
   
   Why composite: Both filtering and sorting use index
   Performance: 5ms (vs 500ms without index)

2. Don't Over-Index (Indexes Have Cost):
   
   Problem: Too many indexes
       - Slower writes (update all indexes on INSERT/UPDATE)
       - Wasted storage (indexes can be 2x table size)
       - Slower vacuuming (more structures to clean)
   
   Rule: Only index queries that run frequently
   Monitor: pg_stat_user_indexes (shows unused indexes)
   
   Delete unused: DROP INDEX IF EXISTS idx_unused;

3. Partial Indexes (Index Subset of Rows):
   
   Full index: CREATE INDEX idx_orders_status ON orders(status);
   Problem: Indexes completed orders (never queried)
   
   Partial index: CREATE INDEX idx_orders_active 
                  ON orders(status) WHERE status != 'completed';
   
   Benefit: 80% smaller index (only active orders)
   Result: Faster queries, less storage, faster writes

4. Expression Indexes (Computed Values):
   
   Query: SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
   Problem: Can't use index on email (LOWER function applied)
   
   Solution: CREATE INDEX idx_users_email_lower ON users(LOWER(email));
   
   Now: Index used for case-insensitive lookups
   Performance: 5ms (vs 500ms table scan)

5. Covering Indexes (Include All Needed Columns):
   
   Query: SELECT order_id, total FROM orders WHERE user_id = 123;
   
   Basic index: CREATE INDEX idx_orders_user ON orders(user_id);
   Problem: Index finds rows, but must fetch total from table (random I/O)
   
   Covering index: CREATE INDEX idx_orders_user_total 
                   ON orders(user_id) INCLUDE (total);
   
   Benefit: Index contains all needed data (no table access)
   Result: 2x faster (sequential I/O only)

Key Metrics to Monitor:

1. Query Performance:
   - Slow query log (queries >100ms)
   - P95/P99 latency (tail latencies matter)
   - Queries per second (throughput)
   - Cache hit ratio (>90% good)
   
   Alert: P95 latency >500ms

2. Resource Utilization:
   - CPU: >80% = add capacity
   - Memory: >85% = risk of OOM
   - Disk: >80% = provision more storage
   - IOPS: Near limit = upgrade tier
   
   Alert: CPU >85% for 10 minutes

3. Replication Lag:
   - Lag: Time behind primary (milliseconds)
   - Target: <1 second (ideally <100ms)
   - Impact: Stale reads if high
   
   Alert: Lag >5 seconds

4. Connection Pool:
   - Active connections: Current usage
   - Max connections: Hard limit (don't hit!)
   - Queue depth: Waiting connections
   
   Alert: >80% connections used

5. Deadlocks & Errors:
   - Deadlock count (transactions waiting on each other)
   - Error rate (failed queries)
   - Transaction rollbacks
   
   Alert: >10 deadlocks/minute

PostgreSQL Monitoring Queries:

-- Find slow queries
SELECT 
    calls,
    mean_exec_time,
    total_exec_time,
    query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Check index usage
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find missing indexes
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 100 AND idx_scan < seq_scan
ORDER BY seq_tup_read DESC;

-- Check table bloat
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

Backup Types:

1. Full Backup:
   PostgreSQL: pg_dump --format=custom mydb > backup.dump
   Time: 1 hour (100 GB database)
   Frequency: Daily (off-peak hours)
   Retention: 30 days
   Storage: S3 ($0.023/GB = $2.30/day)

2. Incremental Backup:
   PostgreSQL: WAL archiving (continuous)
   Size: 10 GB/day (changes only)
   Frequency: Continuous (real-time)
   Benefit: Point-in-time recovery (restore to any second)

3. Snapshot Backup:
   AWS RDS: Automated snapshots (EBS)
   Time: Instant (copy-on-write)
   Frequency: Hourly
   Retention: 35 days
   Restore time: 10 minutes (new RDS instance)

Recovery Time Objective (RTO):
    How long can business tolerate downtime?
    
    Tier 1 (Critical): RTO <5 minutes
        Solution: Hot standby (streaming replication)
        Cost: 2x infrastructure (primary + standby)
    
    Tier 2 (Important): RTO <1 hour
        Solution: Warm standby (periodic snapshots)
        Cost: 1.2x infrastructure (snapshots + storage)
    
    Tier 3 (Normal): RTO <24 hours
        Solution: Cold backup (daily dumps)
        Cost: Storage only ($2.30/day)

Recovery Point Objective (RPO):
    How much data can business afford to lose?
    
    RPO 0 (Zero data loss):
        Solution: Synchronous replication
        Trade-off: Higher latency (wait for replica ACK)
        Use case: Financial systems
    
    RPO 5 minutes:
        Solution: Asynchronous replication + WAL archiving
        Trade-off: May lose last 5 minutes if disaster
        Use case: E-commerce (acceptable)
    
    RPO 24 hours:
        Solution: Daily backups
        Trade-off: May lose full day of data
        Use case: Analytics (can rebuild)

1. Master-Replica (Read Scaling):
   
   Architecture:
       Primary (Master): Handles all writes
       Replica 1: Handles reads (load balanced)
       Replica 2: Handles reads (load balanced)
       Replica 3: Handles reads (load balanced)
   
   Failover: Replica promoted to primary (2-5 minutes)
   Availability: 99.9% (single point of failure)
   Use case: Read-heavy workload (90% reads)

2. Multi-Master (Write Scaling):
   
   Architecture:
       Master 1 (US): Handles US writes
       Master 2 (EU): Handles EU writes
       Master 3 (Asia): Handles Asia writes
       Bidirectional replication between all
   
   Conflict resolution: Last-writer-wins
   Availability: 99.99% (no single point of failure)
   Use case: Global applications (DynamoDB, Cassandra)

3. Automatic Failover (Patroni, Stolon):
   
   Components:
       - etcd/Consul: Consensus (leader election)
       - Patroni: Monitors PostgreSQL health
       - HAProxy: Routes traffic to current primary
   
   Failover process:
       1. Primary fails (health check timeout)
       2. etcd detects failure (3 second check)
       3. Patroni promotes replica (5 seconds)
       4. HAProxy updates routing (1 second)
       Total: <10 seconds (vs 2-5 minutes manual)
   
   Availability: 99.99%+ (automatic, tested)

4. Read-Write Split (Application Level):
   
   Code example (Python):
       primary_conn = psycopg2.connect(primary_url)
       replica_conn = psycopg2.connect(replica_url)
       
       # Write operations
       primary_conn.execute("INSERT INTO users ...")
       
       # Read operations
       replica_conn.execute("SELECT * FROM users ...")
   
   Benefit: Offload reads from primary (90% reduction)
   Challenge: Replication lag (reads may be stale)
   Solution: Use primary for critical reads (user auth)

Why C is Correct:

MongoDB for Products & Reviews:
    Flexible schema: Electronics {voltage, warranty} vs Clothing {size, material}
    Text search: Built-in full-text indexes for review search
    Scalability: Horizontal scaling via sharding (10M reviews handled)
    Performance: Document model natural fit (product = single document)
    
    Example product document:
    {
      "_id": "prod_123",
      "name": "iPhone 15 Pro",
      "category": "electronics",
      "specs": {
        "storage": "256GB",
        "color": "Titanium",
        "warranty": "1 year"
      },
      "reviews": [
        {"user": "user_456", "rating": 5, "text": "Excellent phone!"}
      ]
    }
    
    Why not PostgreSQL for products:
        - Schema changes require migrations (ALTER TABLE)
        - JSONB possible but MongoDB optimized for documents
        - Sharding complex (application-level logic needed)

Redis for Shopping Cart:
    Sub-10ms latency: In-memory, 1M+ ops/sec possible
    TTL support: Auto-expire abandoned carts (30 days)
    Session affinity: Key-value perfect for cart_id lookups
    Atomic operations: HINCRBY for quantity updates
    
    Redis structure:
    HSET cart:user_123 item_456 '{"qty": 2, "price": 999.99}'
    HSET cart:user_123 item_789 '{"qty": 1, "price": 49.99}'
    EXPIRE cart:user_123 2592000  # 30 days TTL
    
    Why not DynamoDB for cart:
        - More expensive at high sustained load ($1.25 per 1M writes)
        - Redis faster (<1ms vs DynamoDB <10ms)
        - Redis atomic operations simpler (HINCRBY vs UpdateExpression)

PostgreSQL for Orders:
    ACID transactions: Payment + inventory + shipment atomic
    Audit trail: Write-ahead log (WAL) provides complete history
    Complex queries: Daily reports need JOINs (orders + users + products)
    Mature: Battle-tested for financial transactions
    
    Order transaction:
    BEGIN;
        -- Create order
        INSERT INTO orders (user_id, total) VALUES (123, 1049.98);
        
        -- Deduct inventory
        UPDATE products SET stock = stock - 2 WHERE id = 456;
        UPDATE products SET stock = stock - 1 WHERE id = 789;
        
        -- Record payment
        INSERT INTO payments (order_id, amount, status) 
        VALUES (currval('orders_id_seq'), 1049.98, 'charged');
    COMMIT;
    
    Why not MongoDB for orders:
        - ACID transactions added 2018 (PostgreSQL since 1980s)
        - PostgreSQL SQL standard (easier analytics)
        - Referential integrity (foreign keys) enforce data quality

Cost Comparison (100K users, 1M requests/day):

Option C (Hybrid):
    MongoDB Atlas: M30 × 3 replicas = $1,800/month
    Redis ElastiCache: cache.r6g.large = $200/month
    RDS PostgreSQL: db.t3.large = $500/month
    Total: $2,500/month = $30K/year
    
Option A (PostgreSQL only):
    RDS: db.r6g.2xlarge (handle all load) = $2,000/month
    Read replicas: 3 × $2,000 = $6,000/month
    Total: $8,000/month = $96K/year
    
    Problem: Not scalable to 10M users (vertical scaling limit)
    
Option B (DynamoDB only):
    Products: 500K items × $0.25/GB = $50/month
    Cart: 10M reads/day × $0.00000025 = $75/month
    Orders: 50K writes/day × $0.00000125 = $2/month
    Total: $127/month = $1,524/year
    
    Problem: Complex queries impossible (no JOINs)
    Analytics: Need export to Redshift ($500/month extra)
    Real total: $7,524/year (still need analytics solution)
    
Option D (Cassandra only):
    Cassandra: 10-node cluster × $500/month = $5,000/month
    DBA: 1 engineer × $200K/year = $16,667/month
    Total: $21,667/month = $260K/year
    
    Problem: Over-engineered (not petabyte scale)

Scalability Path (100K → 10M users):
    MongoDB: Shard by category (electronics, clothing, etc.)
    Redis: Redis Cluster (16K slots, auto-distribution)
    PostgreSQL: Horizontal sharding by user_id (10 clusters)
    
    Architecture at 10M users:
        MongoDB: 10 shards × M60 = $20K/month
        Redis: 10-node cluster × cache.r6g.2xlarge = $5K/month
        PostgreSQL: 10 shards × db.r6g.xlarge = $10K/month
        Total: $35K/month = $420K/year
        
    Still scales linearly (add more shards as needed)

Why Others Wrong:

A) PostgreSQL only:
     Schema rigidity: Product attributes vary by category
     Vertical scaling: Single instance limits (64 vCPU max)
     Sharding complex: Application-level routing needed
     Slower: Disk-based vs Redis in-memory for cart

B) DynamoDB only:
     No JOINs: Analytics impossible (can't join orders + users)
     Limited queries: Can't do "products WHERE price < $50 AND rating > 4"
     Vendor lock-in: AWS-only, no multi-cloud future
     Complex transactions: Multi-item transactions cumbersome

D) Cassandra only:
     No JOINs: Analytics impossible (same as DynamoDB)
     Over-engineered: 10-node minimum (overkill for 100K users)
     Operations: Requires DBA team ($200K+/year)
     No ACID: Eventual consistency not suitable for orders

Failure scenario 1 - Primary fails:
    1. Azure detects failure (15 second health check)
    2. Witness node confirms (quorum)
    3. Replica promoted to primary (30 seconds)
    4. DNS updated to new primary (15 seconds)
    Total: 60 seconds downtime

Failure scenario 2 - Entire zone fails:
    Same process: Replica in different zone promoted
    Still: 60 seconds downtime

Failure scenario 3 - Region fails:
    Problem: Both primary and replica down
    Solution: Restore from geo-backup (RTO: 1 hour)
    Impact: Rare (Azure region outage ~once/year)

Cost per nine: $24K for 99.9% → 99.99%
Worth it? Depends on revenue impact

If downtime costs $10K/hour:
    99.9%: 8.7 hours × $10K = $87K lost/year
    99.99%: 0.87 hours × $10K = $8.7K lost/year
    Savings: $78.3K/year - $24K cost = $54.3K net benefit 

Architecture:
    Primary (Zone 1): Writes
    Replica 1 (Zone 2): Reads
    Replica 2 (Zone 3): Reads

Failure process:
    1. Primary fails (detected by monitoring)
    2. On-call paged (5 minutes response time)
    3. Engineer promotes replica (2 minutes)
    4. DNS updated manually (2 minutes)
    5. Application restarted (1 minute)
    Total: 10 minutes downtime (too slow)

Cost: $1,500 + (2 × $1,500) = $4,500/month (more expensive!)

Architecture:
    Primary: East US 2
    Replica: West US 2 (geo-replicated)

Latency impact:
    Synchronous: 50ms cross-region (unacceptable for writes)
    Asynchronous: 100-500ms lag (data loss risk)

Cost: $1,500 + $1,500 (replica) = $3,000/month

When to use: Disaster recovery (region failure), not HA

Cost:
    Cosmos DB: 500 GB, 10K RU/s = $12,000/month
    Increase: $10,500/month vs Business Critical
    Annual: $126K/year extra (5× more expensive!)

When to use: Multi-region active-active, <10ms global reads

Bad: timestamp#device_id
    Problem: Hot-spotting (all writes go to latest tablet)
    Example: 2024-01-15T10:30:00#device_001
             2024-01-15T10:30:00#device_002
             2024-01-15T10:30:00#device_003
    Result: Latest tablet overloaded, others idle

Good: device_id#timestamp (reverse!)
    Benefit: Writes distributed across all tablets
    Example: device_001#2024-01-15T10:30:00
             device_002#2024-01-15T10:30:00
             device_003#2024-01-15T10:30:00
    Result: Each device hashes to different tablet (even load)

Best: salted device_id#timestamp
    device_id_salted = hash(device_id) % 100 + "#" + device_id
    Example: 42#device_001#2024-01-15T10:30:00
    Benefit: 100 salt values distribute load even if few devices

For 100K devices @ 6 samples/min:
    Total: 10K writes/second
    Nodes: 1 node sufficient (1M capacity)
    Cost: $0.65/hour/node × 730 hours = $474/month

Execution: 
    1. Bigtable scans row key range (device_001#2024-01-15*)
    2. Returns 8,640 rows (1 day @ 6 samples/min)
    3. Latency: <100ms (sequential read, cached)

Dashboard query (100 devices, 24 hours):
    100 devices × 8,640 rows = 864K rows
    Latency: 500ms (parallel tablet scans)
    Acceptable: (<1 second requirement)

Warm data (8-30 days): SSD (Bigtable doesn't have tiers)
    Volume: 864 GB/day × 23 days = 20 TB
    Cost: 20,000 GB × $0.17 = $3,400/month

Cold data (31-365 days): Export to Cloud Storage
    Volume: 864 GB/day × 335 days = 289 TB
    Cost: 289,000 GB × $0.023 (Standard) = $6,647/month
    
    Alternative: Nearline ($0.01/GB) = $2,890/month

TTL: Bigtable automatic garbage collection
    Column family: retention = 30 days (automatic deletion)
    Warm→Cold: Cloud Function exports daily (automated)

Cost:
    Nodes: 3 minimum (HA) × $0.90 × 730 = $1,971/month
    Storage: 26 TB × $0.30 = $7,800/month
    Total: $9,771/month = $117K/year

Savings: Bigtable $7,784 vs Spanner $9,771 = $2K/month cheaper

When to use Spanner: Financial transactions (ACID required)

Capacity:
    PostgreSQL: ~10K writes/second per instance (sufficient now)
    Problem: What if 1M devices? Need 10 instances (sharding)

Cost:
    Instance: db-n1-highmem-16 = $1,200/month
    Storage: 26 TB × $0.17 = $4,420/month
    Total: $5,620/month = $67.4K/year

Cheaper: But doesn't scale horizontally (future problem)

When to use: <100K writes/sec, complex queries needed

Cost:
    Writes: 864 GB/day × $0.05 = $43/day = $1,290/month
    Storage: 26 TB × $0.02 = $520/month (compressed)
    Queries: 100 queries/day × 1 GB scanned × $0.005 = $15/month
    Total: $1,825/month = $21.9K/year

Cheaper: But not designed for operational queries

Problem: Dashboard queries scan full day (expensive at scale)
Use case: Analytical queries (aggregate across all devices)

Analytical (batch): BigQuery
    - Historical trends (1 year)
    - Aggregations (all devices)
    - Machine learning (prediction models)

Hybrid Approach:
    Write → Bigtable (operational, 30 days)
    Export → BigQuery (analytical, 1+ year)
    Total: $7,784 + $1,825 = $9,609/month
    
    Benefit: Fast operational queries + cheap analytics

**Key Takeaway:** Cloud Bigtable ideal for high-volume time-series (1M writes/sec/node, <10ms P99 latency) with proper row key design (device_id#timestamp distributes writes evenly, prevents hot-spotting). Cost $7,784/month vs Cloud Spanner $9,771 (stronger consistency unnecessary) vs PostgreSQL $5,620 (doesn't scale horizontally) vs BigQuery $1,825 (analytics only, not operational). Row key design critical: timestamp-first causes hot-spotting (all writes to latest tablet), device-first distributes evenly, salted device-first optimal (hash distributes load). Data lifecycle: 7 days hot in Bigtable SSD ($1,020/month), 23 days warm in Bigtable ($3,400/month), 335 days cold in Cloud Storage Nearline ($2,890/month), auto-delete after 1 year. Hybrid pattern common: Bigtable for operational queries (real-time dashboards), export to BigQuery for analytics (historical trends, ML). Real-world: IoT platforms use Bigtable for writes, BigQuery for analysis.

---

**Question 4: Database Migration Strategy (AWS SAA-C03 Style)**

**Scenario:**
Your company is migrating a monolithic application from on-premises to AWS. Current state:
- Oracle Enterprise Edition (license cost: $500K/year)
- Database size: 5 TB (2 TB data + 3 TB indexes)
- Workload: 50% OLTP (transactions), 50% OLAP (analytics/reports)
- Peak: 10K transactions/second
- Reports: Complex JOINs across 20+ tables (some take 10+ minutes)
- Downtime acceptable: 4-hour maintenance window on weekends

**Question:**
Which migration strategy minimizes cost while maintaining performance?

A) Migrate to Amazon RDS for Oracle (license included), use read replicas for reports  
B) Migrate to Aurora PostgreSQL, separate OLAP workload to Redshift  
C) Migrate to DynamoDB with on-demand capacity, use DynamoDB Streams to Redshift  
D) Keep Oracle on EC2, use AWS Database Migration Service for zero-downtime migration  

**Correct Answer: B**

**Detailed Explanation:**

Typical findings:
    Tables: 95% compatible (minor syntax changes)
    Stored procedures: 70% compatible (PL/SQL → PL/pgSQL)
    Triggers: 80% compatible (rewrite needed)
     Oracle-specific: Materialized views, sequences, synonyms

Effort: 4-6 weeks (rewrite incompatible code)

Steps:
    1. Create Aurora PostgreSQL cluster (compatible)
    2. Setup DMS replication instance (dms.c5.4xlarge)
    3. Create source endpoint (Oracle on-premises)
    4. Create target endpoint (Aurora PostgreSQL)
    5. Start full load + CDC (change data capture)

Timeline:
    Full load: 5 TB @ 100 MB/s = 14 hours
    CDC lag: <1 minute (real-time replication)
    Validation: 1 week (compare checksums)

Cutover:
    1. Stop application (4-hour window)
    2. Wait for CDC sync (5 minutes)
    3. Switch DNS to Aurora (1 minute)
    4. Start application (5 minutes)
    Total downtime: 11 minutes (within 4-hour window )

OLAP (50% workload) → Redshift
    Reports: Daily sales, customer analytics, forecasting
    Latency: 1-10 seconds (complex JOINs acceptable)
    Method: Aurora → S3 → Redshift (hourly snapshots)

Data Flow:
    Application writes → Aurora (real-time)
    Lambda (hourly) → Export Aurora snapshot → S3
    Redshift COPY → Load from S3 (hourly refresh)
    
    Result: Reports don't impact OLTP performance 

Savings: $1M - $110K = $890K/year (89% reduction!) 

Cost:
    RDS Oracle: db.r6i.8xlarge = $8,000/month
    Read replica: $8,000/month
    Storage: 5 TB × $0.115 = $575/month
    Total: $16,575/month = $199K/year

Savings: Only $801K vs $890K (option B better)

When to use: Oracle features required (no alternative)

Migration effort:
    Code rewrite: 6-12 months (entire data layer)
    Testing: 3-6 months (regression, performance)
    Risk: High (new database, new patterns)

When to use: New greenfield application (not migration)

Cost:
    EC2: r6i.8xlarge × 2 = $4,000/month
    EBS: 5 TB io2 × $0.125 = $625/month
    License: $500K/year = $41,667/month
    Staff: 2 DBAs = $33,333/month
    Total: $79,625/month = $955K/year

Savings: Only $45K (vs $890K with option B)

When to use: Oracle required, want AWS infrastructure

**Key Takeaway:** Migrating Oracle to Aurora PostgreSQL + Redshift provides 89% cost savings ($1M → $110K/year) by eliminating license fees ($500K/year) and reducing operations (0 DBAs vs 2). AWS Schema Conversion Tool identifies 95% compatible tables, 70% compatible stored procedures requiring 4-6 weeks rewrite effort. Database Migration Service enables continuous replication (full load 14 hours for 5TB, CDC <1 minute lag, total cutover 11 minutes within 4-hour window). Workload separation improves performance: OLTP on Aurora (<5ms vs 10ms Oracle) handles 10K TPS, OLAP on Redshift isolates complex reports (20-table JOINs 30 seconds vs 10 minutes, 20x faster + no OLTP impact). RDS Oracle costs $199K/year (saves $801K but still expensive licensing), DynamoDB requires complete rewrite (6-12 months, high risk), Oracle on EC2 only saves $45K (still need license + DBAs). Real-world Capital One migrated 100+ Oracle databases to Aurora, saved $100M+/year, improved performance 30-40%, increased availability 99.95% → 99.99%. Migration risks mitigated via: DMS validation (checksums prevent data loss), load testing (ensure performance), parallel running (1-week rollback window).

---

**Question 5: Caching Strategy for Performance (Multi-Cloud Scenario)**

**Scenario:**
Your API handles 100K requests/second with this database workload:
- Database: PostgreSQL (primary + 5 read replicas)
- Query pattern: 80% reads, 20% writes
- Top 10 queries: Account for 60% of total reads (hot data)
- Current latency: P50 = 50ms, P95 = 200ms, P99 = 500ms
- Goal: Reduce P95 to <50ms, reduce database load 70%

**Question:**
Which caching strategy provides the best performance improvement?

A) Application-level cache (in-memory dictionary) with 5-minute TTL  
B) Redis cluster with cache-aside pattern and intelligent cache warming  
C) PostgreSQL query result caching (pg_stat_statements) with materialized views  
D) CDN caching (CloudFront/Cloudflare) with query string parameters  

**Correct Answer: B**

**Detailed Explanation:**

Flow:
    1. Request arrives: GET /user/123
    2. Check Redis: GET user:123
    3a. Cache hit: Return immediately (1ms)
    3b. Cache miss: Query PostgreSQL (50ms)
    4. Populate Redis: SET user:123 {data} EX 300
    5. Return response

if cached:
    # Cache hit (1ms)
    return json.loads(cached)

# Step 2: Cache miss - query database
conn = pg_pool.getconn()
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
user = cursor.fetchone()
pg_pool.putconn(conn)

if user:
    # Step 3: Populate cache (5-minute TTL)
    r.setex(cache_key, 300, json.dumps(user))

return user

Time: 30 seconds (100 queries × 0.3s)
Benefit: Zero cold-start latency

Code:
def warm_cache():
    # Get hot users (top 1000 by access count)
    hot_users = get_hot_users_from_metrics()
    
    for user_id in hot_users:
        cache_key = f"user:{user_id}"
        ttl = r.ttl(cache_key)
        
        # Refresh if TTL < 60 seconds
        if ttl < 60:
            user = fetch_user_from_db(user_id)
            r.setex(cache_key, 300, json.dumps(user))

Benefit: Hot data never expires (always fresh)

Latency breakdown:
    Cache hit (90%): 1ms (Redis in-memory)
    Cache miss (10%): 50ms (PostgreSQL query)
    Weighted average: (0.9 × 1) + (0.1 × 50) = 5.9ms

Results:
    P50: 1ms (vs 50ms = 50x faster)
    P95: 2ms (vs 200ms = 100x faster )
    P99: 50ms (vs 500ms = 10x faster )

Slot distribution:
    Master 1: Slots 0-5460 (user:1 to user:300K)
    Master 2: Slots 5461-10922 (user:300K to user:600K)
    Master 3: Slots 10923-16383 (user:600K to user:1M)

Current usage:
    Keys: 10M (user profiles)
    Memory: 30 GB (10M × 3 KB)
    Headroom: 270 GB available (90% free)

Net: -$1,200 (Redis) + $3,000 (PostgreSQL) = $1,800/month saved
ROI: $21.6K/year savings + faster performance 

Example problem:
    Server 1 cache: user:123 = {name: "Alice"}
    User updates name: "Alice" → "Alicia"
    Server 1 cleared (invalidated)
    Server 2 cache: Still {name: "Alice"} (stale!)

Result: Users see inconsistent data (bad UX)

When to use: Single-server applications only

Materialized view example:
    CREATE MATERIALIZED VIEW user_stats AS
    SELECT user_id, COUNT(*) as order_count
    FROM orders GROUP BY user_id;
    
    REFRESH MATERIALIZED VIEW user_stats;  -- Manual!

Problem: When to refresh?
    Too frequent: Database load (defeats purpose)
    Too infrequent: Stale data (bad UX)

When to use: Complex aggregations (not simple lookups)

Example problem:
    API: GET /api/user/profile (returns logged-in user)
    CDN caches: Response for user 123
    User 456: Gets user 123's profile! (privacy breach)

Solution: Vary: Cookie header (but defeats caching)

When to use: Public content (blog posts, product pages)

Pros: Simple, automatic cleanup
Cons: Stale data possible (up to 5 minutes)

When to use: Data changes infrequently

Pros: Always fresh data
Cons: Requires invalidation logic everywhere

When to use: Data must be real-time (banking, inventory)

Pros: Cache always fresh
Cons: Slower writes (2 operations)

When to use: Write-heavy workloads

**Key Takeaway:** Redis cluster with cache-aside pattern reduces P95 latency 50ms → 2ms (100x faster), database load 80K → 8K QPS (90% reduction exceeding 70% goal), and saves $21.6K/year by downsizing PostgreSQL 5 → 2 replicas. Cache-aside flow: Check Redis first (1ms cache hit), query PostgreSQL on miss (50ms), populate cache with 5-minute TTL. Intelligent cache warming prevents cold-start: Pre-populate top 100 users on deployment (30 seconds), continuously refresh hot data before expiry (background job every 5 minutes). Redis cluster 6 nodes (3 masters + 3 replicas) provides 300GB capacity, 1M ops/sec throughput, <10 second automatic failover. Application-level cache causes inconsistency (each server separate cache), PostgreSQL caching still hits database (10ms vs 1ms Redis), CDN caching wrong for user-specific APIs (privacy breach risk). Cache invalidation: TTL for reads (simple, 5-minute staleness acceptable), event-based for writes (delete key on update, ensures freshness), hybrid best practice. Cost: $1,200/month Redis - $3,000/month PostgreSQL savings = $1,800/month net savings. Monitor hit rate >90% excellent, memory <80% avoid evictions, P99 latency <5ms network overhead.

---


**Question 7: Read Replica Lag Problem (Azure AZ-305 Style)**

**Scenario:**
Your e-commerce application uses Azure Database for PostgreSQL with read replicas:
- Primary: Handles all writes (orders, payments, inventory updates)
- Read replica 1: Product listings, search queries
- Read replica 2: User dashboards, order history
- Problem: Users report seeing outdated order status (replication lag 5-30 seconds)
- Business impact: "Order placed" but status shows "Processing" on dashboard

**Question:**
What is the BEST solution to ensure users see their own writes immediately while maintaining read scaling?

A) Upgrade to Business Critical tier with synchronous replication  
B) Implement session affinity to route same user to primary for 60 seconds after write  
C) Use application-level read-after-write consistency (check primary for recent writes)  
D) Increase replica count to 5 to reduce load and decrease replication lag  

**Correct Answer: C**

**Detailed Explanation:**

if recent_write:
    # Use primary for reads within 60 seconds of write
    conn = primary_connection_pool.getconn()
    cursor = conn.execute("SELECT * FROM orders WHERE id = %s", (order_id,))
    order = cursor.fetchone()
    primary_connection_pool.putconn(conn)
    return order
else:
    # Use replica for normal reads (faster, offload primary)
    conn = replica_connection_pool.getconn()
    cursor = conn.execute("SELECT * FROM orders WHERE id = %s", (order_id,))
    order = cursor.fetchone()
    replica_connection_pool.putconn(conn)
    return order

# Track recent write in Redis (60-second TTL)
redis_key = f"user_writes:{user_id}"
redis_client.sadd(redis_key, str(order_id))
redis_client.expire(redis_key, 60)  # Expire after 60 seconds

return order_id

 Pros: Zero replication lag (immediate consistency)
 Cons: 
    - Slower writes: 2× latency (10ms → 20-30ms)
    - More expensive: $3,500/month vs $1,500/month
    - Overkill: Only 10% of reads need immediate consistency

Cost: $24K/year extra for feature needed 10% of time

When to use: All reads require strong consistency (banking)

 Defeats purpose: Read replicas unused (no load offloading)
 Hot primary: All recent users on primary (overload)
 Uneven load: Primary 90%, replicas 10% (inverse of goal)

Example:
    1,000 orders/minute = 1,000 users on primary
    Those users: Dashboard queries, search, browsing (all primary)
    Result: Primary overloaded, replicas idle 

When to use: Never (defeats replication purpose)

Lag causes:
    1. Primary CPU 90% → Slow WAL generation
    2. Network congestion → Slow WAL transfer
    3. Replica CPU 90% → Slow WAL application

More replicas: Doesn't address any of these 

When to use: Need more read capacity (not for lag)

Write:
    UPDATE orders SET status = 'shipped', version = version + 1
    WHERE id = 123 AND version = 5

Read:
    SELECT * FROM orders WHERE id = 123
    If version < expected: Retry from primary

Trade-off: Extra roundtrip if stale

Read:
    Get last_write from Redis: 11:00:00.100
    Query replica: WHERE updated_at <= last_write - 60 seconds
    Else: Query primary

Trade-off: Clock skew risk (NTP required)

Read:
    1. Check Redis: GET order:123
    2. If hit: Return (1ms, guaranteed fresh)
    3. If miss: Query replica (replication caught up)

Benefit: Fastest (Redis in-memory)
Trade-off: Data duplication

**Key Takeaway:** Application-level read-after-write consistency solves replication lag (5-30 seconds) by routing recent writes to primary for 60 seconds, then using replicas after lag resolved. Implementation: Track writes in Redis (user_writes:user_123 contains order IDs, 60-second TTL), check_recent_writes() determines routing, 90% reads use replica (offload primary), 10% reads use primary (user's own recent writes). Performance: P95 10.5ms weighted average, primary load reduced 90%, zero stale reads for user's own data, costs $50/month Redis vs $24K/year Business Critical upgrade. Session affinity defeats purpose (routes ALL queries to primary for 60 seconds, replicas idle), more replicas doesn't reduce lag (lag from primary CPU/network/replica CPU, not replica count), synchronous replication adds 10-20ms write latency ($24K/year for feature needed 10% of time). Alternative patterns: Write-through cache fastest (Redis 1ms, guaranteed fresh), version numbers enable optimistic locking (retry if stale), timestamps track last-write (clock skew risk). Real-world Amazon uses DynamoDB write-through cache for 0-60 seconds (sub-10ms, always fresh), Aurora replicas after 60 seconds (offload 85% queries), zero stale order complaints. Best for: Any user-facing application with read replicas where users must see their own writes immediately (orders, posts, comments, profile updates).

---

**Question 8: Multi-Region Database Strategy (GCP Professional Architect)**

**Scenario:**
Your SaaS application is expanding globally:
- Current: Single region (us-central1), 10M users (mostly US)
- Expansion: Europe (5M new users), Asia (3M new users)
- Requirements:
  - GDPR compliance (EU data must stay in EU)
  - Low latency (<50ms reads globally)
  - Disaster recovery (survive region failure)
  - Strong consistency for financial transactions

**Question:**
Which multi-region database architecture meets all requirements?

A) Cloud Spanner global database with multi-region configuration  
B) Cloud SQL PostgreSQL in each region with cross-region read replicas  
C) Firestore multi-region with ACID transactions enabled  
D) Cloud Bigtable replicated across 3 regions with eventual consistency  

**Correct Answer: A**

**Detailed Explanation:**

Replication: Synchronous (Paxos consensus)
Consistency: Linearizable (strongest possible)
Latency: Cross-region writes 100-500ms, local reads <10ms

-- Partition directive (data placement)
ALTER TABLE users ADD COLUMN region_partition STRING;

-- Force EU users to EU region
INSERT INTO users (user_id, region, email)
VALUES (123456, 'EU', 'user@eu.example.com')
PARTITION BY region;

# EU user query (from europe-west1)
SELECT * FROM users WHERE region = 'EU' AND user_id = 456
→ Reads local replica (europe-west1): <10ms 

# US user query from Europe (cross-region)
SELECT * FROM users WHERE region = 'US' AND user_id = 123
→ Reads from us-central1: 100ms (acceptable for admin queries)

    -- Add to EU account
    UPDATE accounts SET balance = balance + 100
    WHERE user_id = 456 AND region = 'EU';
COMMIT;

Downtime: 10-30 seconds (automatic, no manual intervention)
Data loss: Zero (synchronous replication )

Expensive: But meets all requirements

 No multi-region writes: Europe writes → us-central1 (100ms+ latency)
 Eventual consistency: Replicas lag 100ms-5 seconds
 Manual failover: Primary fails → Promote replica (5-10 minutes)
 GDPR risk: All data written to us-central1 first (audit issue)

Example problem:
    EU user writes: POST /api/orders (from europe-west1)
    Network: europe → us-central1 (100ms round-trip)
    User experience: Slow (100ms+ latency) 

When to use: Primary region dominates (90%+ users), others read-only

Transaction limit problem:
    Transfer money: Touch 2 documents (accounts) 
    Batch process: Update 10,000 orders (exceeds limit)

Query limitation:
    Firestore: Get user orders WHERE status = 'shipped'
    Can't: JOIN orders with products (get product details)
    Workaround: Denormalize (duplicate product data in orders)

When to use: Mobile/web apps, simple queries, document model fits

Consistency problem:
    Write in us-central1: SET balance:user_123 = 1000
    Read from europe-west1: GET balance:user_123 = 900 (stale!)
    Replication lag: 100ms-1 second
    Financial impact: User sees wrong balance 

When to use: Time-series, logs, IoT (eventual consistency acceptable)

Why C is Correct:

Root Cause Analysis:

Symptoms indicate: NOT a resource bottleneck
    CPU 40%: Plenty of capacity (not CPU-bound)
    Memory 60%: Not memory pressure
    IOPS 20%: Not I/O bound
    Connections: Only 200/500 (not connection exhaustion)

Likely cause: Inefficient queries (missing indexes, bad plans)

Step 1: Identify Slow Queries

Enable slow query log:
    ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 1 second
    SELECT pg_reload_conf();

Query pg_stat_statements (built-in extension):
    SELECT 
        calls,
        mean_exec_time,
        total_exec_time,
        query
    FROM pg_stat_statements
    ORDER BY mean_exec_time DESC
    LIMIT 10;

Example output:
    calls | mean_exec_time | total_exec_time | query
    ------|----------------|-----------------|-------
    5000  | 3500ms         | 17,500,000ms    | SELECT * FROM orders WHERE user_id = $1
    2000  | 2800ms         | 5,600,000ms     | SELECT * FROM products WHERE category = $1
    1000  | 2200ms         | 2,200,000ms     | SELECT COUNT(*) FROM users WHERE created_at > $1

Insight: Top 3 queries account for 25,300 seconds = 7 hours of total DB time!

Step 2: Analyze Query Plans

Query 1: User's orders
    SELECT * FROM orders WHERE user_id = 123;
    
    EXPLAIN (ANALYZE, BUFFERS):
        Seq Scan on orders (cost=0.00..1750000.00 rows=500 width=100)
        Filter: (user_id = 123)
        Planning Time: 0.5ms
        Execution Time: 3500ms
        Buffers: shared hit=2000 read=100000
    
    Problem: Sequential scan (reads entire table!)
    Solution: Index on user_id
        CREATE INDEX idx_orders_user_id ON orders(user_id);
    
    After index:
        Index Scan using idx_orders_user_id on orders (cost=0.42..25.44 rows=500)
        Execution Time: 5ms (700× faster! )

Query 2: Products by category
    SELECT * FROM products WHERE category = 'electronics';
    
    EXPLAIN (ANALYZE, BUFFERS):
        Seq Scan on products (cost=0.00..500000.00 rows=50000 width=200)
        Filter: (category = 'electronics'::text)
        Execution Time: 2800ms
    
    Problem: Sequential scan + low selectivity (50K of 1M products)
    Solution: Index on category
        CREATE INDEX idx_products_category ON products(category);
    
    After index:
        Bitmap Index Scan on idx_products_category
        Execution Time: 50ms (56× faster! )

Query 3: User count since date
    SELECT COUNT(*) FROM users WHERE created_at > '2024-01-01';
    
    EXPLAIN (ANALYZE, BUFFERS):
        Aggregate (cost=500000.00..500000.01 rows=1)
        -> Seq Scan on users (cost=0.00..480000.00 rows=8000000)
            Filter: (created_at > '2024-01-01'::date)
        Execution Time: 2200ms
    
    Problem: Sequential scan to count
    Solution: Index on created_at
        CREATE INDEX idx_users_created_at ON users(created_at);
    
    After index:
        Aggregate (cost=280000.00..280000.01 rows=1)
        -> Index Scan using idx_users_created_at on users
        Execution Time: 100ms (22× faster! )

Step 3: Create Missing Indexes

-- Index user_id for order lookups
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Index category for product filtering
CREATE INDEX CONCURRENTLY idx_products_category ON products(category);

-- Index created_at for date range queries
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);

-- Composite index for common query pattern
CREATE INDEX CONCURRENTLY idx_orders_user_status 
ON orders(user_id, status) INCLUDE (total);

Note: CONCURRENTLY = no table lock (production-safe)

Step 4: Verify Improvement

Query pg_stat_statements again:
    calls | mean_exec_time | total_exec_time | query
    ------|----------------|-----------------|-------
    5000  | 5ms            | 25,000ms        | SELECT * FROM orders WHERE user_id = $1
    2000  | 50ms           | 100,000ms       | SELECT * FROM products WHERE category = $1
    1000  | 100ms          | 100,000ms       | SELECT COUNT(*) FROM users WHERE created_at > $1

Improvement:
    Before: 25,300,000ms total execution time
    After: 225,000ms total execution time
    Speedup: 112× faster! 

Impact on application:
    - P95 latency: 1000ms → 50ms (20× faster)
    - Database CPU: 40% → 10% (freed capacity)
    - Throughput: 1,000 QPS → 10,000 QPS (10× more capacity)

Why Others Wrong:

A) Provision more IOPS:
    Current: 20% of 10K IOPS = 2K IOPS used
    Problem: Not I/O bound (plenty of unused IOPS)
    
     Doesn't help: Sequential scans CPU/memory bound (not I/O)
     Wastes money: $5K/month for 50K IOPS (unused)
    
    When to help: IOPS >80% utilization

B) Add read replicas:
     Doesn't help slow queries: Replicas run same slow queries
     Replication lag: Slow queries on replica too (3.5 seconds each)
     Doesn't address root cause: Missing indexes (not capacity)
    
    Example:
        Primary: SELECT ... (3.5 seconds, no index)
        Replica: SELECT ... (3.5 seconds, same no index)
        Result: Still slow! 
    
    When to help: Fast queries, need more read capacity

C) Add missing indexes: CORRECT
    Addresses root cause: Inefficient query plans
    Immediate impact: 100× faster queries
    Low cost: Index storage <<< new hardware
    Production-safe: CREATE INDEX CONCURRENTLY (no locks)

D) Upgrade instance (16 → 32 vCPU):
     Wasteful: CPU only 40% (not constrained)
     Expensive: $2K/month → $4K/month (2× cost)
     Doesn't help: Sequential scans still slow (linear with table size)
    
    Math:
        Current: 3.5 second query (40% CPU)
        After upgrade: 1.75 second query (20% CPU)
        Improvement: 2× faster (but still slow!)
    
    vs indexes:
        After indexes: 0.005 second query (1% CPU)
        Improvement: 700× faster 
    
    When to help: CPU >85% with optimized queries

Additional Optimization Techniques:

1. Partial Indexes (reduce index size):
    -- Only index active orders (not completed)
    CREATE INDEX idx_orders_active 
    ON orders(user_id) 
    WHERE status != 'completed';
    
    Benefit: 80% smaller index (faster, less storage)

2. Covering Indexes (avoid table lookups):
    -- Include frequently accessed columns
    CREATE INDEX idx_orders_user_summary
    ON orders(user_id) INCLUDE (total, status, created_at);
    
    Benefit: Index-only scan (no table access, 2× faster)

3. Query Rewrite (better SQL):
    Bad: SELECT * FROM orders WHERE user_id IN (
             SELECT user_id FROM users WHERE premium = true
         );
    
    Good: SELECT o.* FROM orders o
          JOIN users u ON o.user_id = u.user_id
          WHERE u.premium = true;
    
    Improvement: Join more efficient than subquery (PostgreSQL optimizer)

4. Connection Pooling (reduce overhead):
    Bad: New connection per request (50-100ms overhead)
    Good: PgBouncer pool (1ms to get connection)
    
    Implementation:
        Application → PgBouncer (port 6432) → PostgreSQL
        Pool size: 100 connections
        Result: 50× faster connection acquisition

5. Vacuuming (table maintenance):
    Problem: Dead tuples accumulate (slow queries)
    Solution: Auto-vacuum (PostgreSQL default)
    
    Check bloat:
        SELECT schemaname, tablename, 
               pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
        FROM pg_tables
        ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
    
    Manual vacuum: VACUUM ANALYZE orders;

Real-World Example - Reddit Database Optimization:

Problem (2018):
    - Query latency: P95 = 2 seconds
    - Database CPU: 60% (not saturated)
    - User complaints: "Reddit is slow"

Investigation:
    - Analyzed pg_stat_statements
    - Found: 50 queries missing indexes
    - Most common: Post lookups by subreddit

Solution:
    - Added 50 indexes (took 1 week to create)
    - Rewrote 10 inefficient queries
    - Implemented connection pooling (PgBouncer)

Results:
    - P95 latency: 2 seconds → 50ms (40× faster)
    - Database CPU: 60% → 15% (freed capacity)
    - Throughput: 10K QPS → 50K QPS (5× increase)
    - Cost: Zero (no hardware changes)

Quote (from Reddit Engineering blog):
    "We realized CPU wasn't the bottleneck - our queries were.
     Adding indexes and connection pooling reduced P95
     latency from 2 seconds to 50ms, enabling 5× more
     throughput on the same hardware."

Why C is Correct:

Architecture: Streaming Replication + S3 Glacier

Component 1: Hot Standby (RTO + RPO)
    Primary: us-east-1 (RDS PostgreSQL)
    Standby: us-west-2 (streaming replication)
    
    Replication:
        Method: Asynchronous streaming (continuous)
        Lag: <1 minute typical (well within 15-minute RPO)
        Bandwidth: 10 GB/hour average (240 GB/day for 20 TB)
    
    Failover process:
        1. Primary region fails (detected in 60 seconds)
        2. Promote standby: pg_ctl promote (30 seconds)
        3. DNS update: Route53 failover (30 seconds)
        4. Application reconnects: Connection pool (1 minute)
        Total: 2.5 minutes (well within 4-hour RTO )

Component 2: Long-Term Retention (Compliance)
    Daily backup process:
        1. Standby database: pg_basebackup (full backup, no impact on primary)
        2. Compress: gzip (20 TB → 5 TB compressed)
        3. Upload: S3 Glacier Deep Archive (us-east-1)
        4. Immutable: Object Lock (WORM = write once, read many)
        5. Retention: 7 years (regulatory requirement)
    
    Backup schedule:
        Full: Daily (2 AM when traffic low)
        Incremental: WAL files (continuous, every 60 seconds)
        Retention: 7 years Glacier + 35 days S3 Standard
    
    Encryption:
        At rest: S3 SSE-KMS (AES-256)
        In transit: TLS 1.3 (encryption during upload)
        Keys: AWS KMS (customer managed, rotated annually)

Detailed Implementation:

1. Setup Streaming Replication:
   Primary (us-east-1):
       # postgresql.conf
       wal_level = replica
       max_wal_senders = 10
       max_replication_slots = 10
       archive_mode = on
       archive_command = 'aws s3 cp %p s3://backup-bucket/wal/%f'
   
   Standby (us-west-2):
       # recovery.conf
       standby_mode = on
       primary_conninfo = 'host=primary.us-east-1 port=5432 user=replicator'
       restore_command = 'aws s3 cp s3://backup-bucket/wal/%f %p'

2. Continuous WAL Archiving:
   Script (runs every minute):
       #!/bin/bash
       # Archive WAL files to S3
       for wal in /var/lib/postgresql/15/main/pg_wal/*.ready; do
           filename=$(basename $wal .ready)
           aws s3 cp /var/lib/postgresql/15/main/pg_wal/$filename \
                     s3://backup-bucket/wal/$filename \
                     --storage-class STANDARD
           # Move to Glacier after 35 days (lifecycle policy)
       done

3. Daily Full Backup:
   Script (runs 2 AM daily):
       #!/bin/bash
       DATE=$(date +%Y-%m-%d)
       
       # Base backup from standby (no primary impact)
       pg_basebackup -h standby.us-west-2 \
                     -D /backup/base-$DATE \
                     -Ft -z -P
       
       # Upload to S3 Glacier Deep Archive
       aws s3 cp /backup/base-$DATE.tar.gz \
                 s3://backup-bucket/daily/$DATE.tar.gz \
                 --storage-class DEEP_ARCHIVE
       
       # Enable Object Lock (immutable)
       aws s3api put-object-retention \
           --bucket backup-bucket \
           --key daily/$DATE.tar.gz \
           --retention Mode=COMPLIANCE,RetainUntilDate=2031-01-01

4. Recovery Procedure:

   Scenario 1: Point-in-Time Recovery (user error at 10:30 AM)
       Goal: Restore database to 10:29 AM (before error)
       
       Steps:
           1. Download latest base backup (from 2 AM):
              aws s3 cp s3://backup-bucket/daily/2024-01-15.tar.gz /restore/
           
           2. Extract base backup:
              tar -xzf 2024-01-15.tar.gz -C /var/lib/postgresql/15/main/
           
           3. Download WAL files (2 AM → 10:29 AM):
              aws s3 sync s3://backup-bucket/wal/ /restore/wal/
           
           4. Configure recovery target:
              # recovery.conf
              restore_command = 'cp /restore/wal/%f %p'
              recovery_target_time = '2024-01-15 10:29:00'
           
           5. Start PostgreSQL (replay WAL files):
              pg_ctl start
           
           6. Verify data (check restored):
              psql -c "SELECT COUNT(*) FROM orders WHERE created_at < '2024-01-15 10:29:00'"
       
       Time: 2 hours (well within 4-hour RTO )
   
   Scenario 2: Regional Disaster (us-east-1 complete failure)
       Goal: Failover to us-west-2 standby
       
       Steps:
           1. Detect failure (monitoring alert)
           2. Promote standby: pg_ctl promote
           3. Update DNS: Route53 health check (automatic)
           4. Application reconnects (connection pool retry)
       
       Time: 2.5 minutes (well within 4-hour RTO )
       Data loss: <1 minute replication lag (within 15-minute RPO )

Cost Breakdown:

Primary Database (us-east-1):
    Instance: db.r6g.8xlarge = $3,000/month
    Storage: 20 TB × $0.115/GB = $2,300/month
    Subtotal: $5,300/month

Standby Database (us-west-2):
    Instance: db.r6g.8xlarge = $3,000/month
    Storage: 20 TB × $0.115/GB = $2,300/month
    Data transfer: 240 GB/day × $0.02/GB = $144/month
    Subtotal: $5,444/month

Backup Storage:
    S3 Standard (35 days WAL): 240 GB/day × 35 days = 8.4 TB
        Cost: 8,400 GB × $0.023/GB = $193/month
    
    Glacier Deep Archive (7 years daily backups):
        Daily: 5 TB compressed × 365 days/year × 7 years = 12,775 TB
        Cost: 12,775,000 GB × $0.00099/GB = $12,647/month
    
    Subtotal: $12,840/month

Total: $5,300 + $5,444 + $12,840 = $23,584/month = $283K/year

Why Others Wrong:

A) RDS automated backups + manual snapshots:
    RDS automated backups:
        Retention: 35 days maximum (not 7 years )
        RPO: Hourly (not 15 minutes )
        RTO: 30-60 minutes (good )
    
    Manual snapshots every 15 minutes:
        Cost: 20 TB × 96 snapshots/day × $0.05/GB = $96,000/day!
        Storage: Exponential growth (snapshots don't expire automatically)
        Management: Complex (automate snapshot creation/deletion)
    
    Problem: Can't meet 7-year retention (35-day limit)
    Workaround: Export to S3 Glacier (but RDS doesn't support direct export)
    
    When to use: <35 day retention requirements

B) Continuous WAL + Glacier Deep Archive:
     Meets 7-year retention (Glacier )
     Meets 15-minute RPO (continuous WAL )
     Slow RTO: Restore 20 TB from Glacier (12-48 hours )
    
    Recovery process:
        1. Request Glacier restore (12 hours minimum)
        2. Download 20 TB base backup (4 hours at 1 GB/s)
        3. Download WAL files (1 hour)
        4. PostgreSQL replay (2 hours for 20 TB)
        Total: 19 hours (exceeds 4-hour RTO )
    
    When to use: Long-term archival only (not hot standby)

D) Veeam + tape backup:
     Meets 7-year retention (tape )
     Slow RTO: Tape restore (24+ hours )
     Complex: Requires tape library, robotic arms, off-site storage
     Expensive: Tape hardware ($100K+), maintenance ($20K/year)
     Encryption: Manual key management (not AWS KMS)
    
    Tape restore process:
        1. Retrieve tape from off-site storage (4 hours)
        2. Load tape into drive (30 minutes)
        3. Restore data (10 hours at 2 TB/hour)
        4. Verify backup (2 hours)
        Total: 16.5 hours (exceeds 4-hour RTO )
    
    When to use: On-premises, regulations require tape (banking, healthcare)

Comparison Table:

| Solution | RTO | RPO | Retention | Cost/Month | Best For |
|----------|-----|-----|-----------|------------|----------|
| C) Replication + Glacier | 2.5 min | <1 min | 7 years | $23,584 | All requirements |
| A) RDS automated | 30-60 min | 1 hour | 35 days | $5,300 | Short retention |
| B) WAL + Glacier only | 12-48 hours | 15 min | 7 years | $18,140 | Archival only |
| D) Veeam + tape | 16+ hours | 1 hour | 7 years | $35,000+ | On-premises |

Advanced: Testing Disaster Recovery

Regular DR Drills (Quarterly):
    1. Failover test: Promote standby to primary
    2. Restore test: Point-in-time recovery from Glacier
    3. Verify test: Data integrity checks (checksums)
    4. Performance test: Query performance on restored
    5. Document: Actual RTO/RPO vs targets
    
    Example drill results:
        Target RTO: 4 hours
        Actual RTO: 2 hours, 15 minutes 
        Target RPO: 15 minutes
        Actual RPO: 45 seconds 

Monitoring & Alerting:
    - Replication lag >5 minutes → PagerDuty alert
    - Backup failure → Email + Slack notification
    - Glacier retrieval >12 hours → Executive escalation
    - WAL archiving lag >1 minute → Warning alert

Real-World Example - Capital One Backup Strategy:

Implementation:
    - Multi-region Aurora with Global Database
    - Streaming replication (us-east-1 → us-west-2)
    - Daily snapshots to S3 Glacier (7-year retention)
    - Immutable backups (Object Lock COMPLIANCE mode)

Results:
    - RTO: <1 minute (Aurora automatic failover)
    - RPO: <1 second (synchronous replication)
    - Compliance: SOC 2, PCI-DSS, GDPR compliant
    - Cost: $500K/year (2,000 databases total)

Quote (from Capital One Engineering blog):
    "We use Aurora Global Database for sub-second RPO
     and <1 minute RTO. Daily snapshots to Glacier with
     Object Lock meet our 7-year compliance requirements.
     We test DR quarterly and consistently hit our targets."

Before:
    50 instances × 10 connections = 500 connections (exceeds 150 limit )

After:
    50 instances × 10 connections → PgBouncer → PostgreSQL (100 active)
    PgBouncer multiplexes: 500 client connections → 100 server connections 

# /etc/pgbouncer/pgbouncer.ini

[databases]
production = host=postgres.azure.com port=5432 dbname=production

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Pool configuration
pool_mode = transaction
max_client_conn = 10000  # Arbitrary limit (handle all clients)
default_pool_size = 100   # PostgreSQL connections per database
reserve_pool_size = 25    # Extra connections for spikes
reserve_pool_timeout = 3  # Wait 3 seconds for connection

# Connection limits per user
max_db_connections = 150  # Match PostgreSQL max_connections
max_user_connections = 100

# Timeouts
server_idle_timeout = 600     # Close idle server conn after 10 min
server_lifetime = 3600        # Recycle connections every hour
server_connect_timeout = 15
query_timeout = 60
query_wait_timeout = 120

# Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

# Direct connection (uses PostgreSQL port 5432)
conn_string = "postgresql://user:pass@postgres.azure.com:5432/production"

# Through PgBouncer (port 6432)
conn_string = "postgresql://user:pass@pgbouncer.azure.com:6432/production"

With PgBouncer (transaction mode):
    500 client connections → PgBouncer
    Typical transaction: 50ms (query + commit)
    Utilization: 5% per connection (50ms active, 950ms idle)
    Active server connections: 500 × 0.05 = 25 concurrent
    PgBouncer pool: 100 connections (25 active, 75 idle)
    PostgreSQL sees: 100 connections (within 150 limit )

-- Connect to PgBouncer admin console
psql -h pgbouncer.azure.com -p 6432 -U pgbouncer pgbouncer

-- Show pool status
SHOW POOLS;

-- Output:
 database    | user     | cl_active | cl_waiting | sv_active | sv_idle | sv_used | maxwait
-------------|----------|-----------|------------|-----------|---------|---------|--------
 production  | app_user | 45        | 0          | 25        | 75      | 100     | 0

-- Explanation:
-- cl_active: 45 client connections active
-- cl_waiting: 0 clients waiting (good, no queue)
-- sv_active: 25 server connections to PostgreSQL active
-- sv_idle: 75 server connections idle (available)
-- maxwait: 0 seconds (no queuing)

-- Show stats
SHOW STATS;

-- Output:
 database    | total_xact_count | total_query_count | total_wait_time
-------------|------------------|-------------------|----------------
 production  | 1,000,000        | 5,000,000         | 0

-- total_wait_time: 0 = never waited for connection (healthy )

Cost:
    Before: $500/month (db.m5.xlarge)
    After: $2,000/month (db.m5.4xlarge)
    Increase: $1,500/month = $18K/year

vs PgBouncer:
    Cost: $140/month = $1,680/year
    Savings: $16,320/year 

 Wastes resources: CPU 40% (not CPU-bound)
 Doesn't solve scaling: 100 instances × 10 = 1,000 connections (still exceeds 600)
 Expensive: 4× cost increase for connection limit only

When to use: Actually CPU/memory constrained (>80% utilization)

Helps but limited: 400 < 500 (still not enough at scale)
 Doesn't scale infinitely: 100 instances = 1,000 connections
 More expensive: Flexible Server 20% more than Single Server

Cost:
    Single Server: $500/month
    Flexible Server: $600/month
    Increase: $100/month = $1,200/year

Comparison: Still need PgBouncer eventually (better to start now)

When to use: Small scale (<10 instances), want managed service

HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(10);  // 10 connections per instance
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);

 Reduces connections per instance (good practice)
 Doesn't solve global limit: Still 50 × 10 = 500 connections
 Each instance unaware of others (no coordination)

Problem:
    Instance 1: Opens 10 connections (total: 10/150)
    Instance 2: Opens 10 connections (total: 20/150)
    ...
    Instance 15: Opens 10 connections (total: 150/150)
    Instance 16: "too many connections" 

When to use: Complement to PgBouncer (both together best practice)

Benefits:
    - HikariCP: Fast local pool (1ms acquisition)
    - PgBouncer: Global multiplexing (500 → 100 connections)
    - PostgreSQL: Low connection count (optimal performance)

Benefit: Handles write load + complex queries
    - Writes: 3K/sec → 6K/sec capacity (2× headroom)
    - Reports: 90% CPU → 45% CPU (not blocking writes )
    - Buffer pool: 80% → 40% (less cache eviction)

┌─────────────────────────────────────────────────────┐
│ Application Layer                                    │
│ ┌────────────────┐  ┌────────────────┐             │
│ │ Write Router   │  │ Read Router    │             │
│ │ (Send to       │  │ (Load balance  │             │
│ │  Primary)      │  │  across 3)     │             │
│ └────────┬───────┘  └───────┬────────┘             │
└──────────┼──────────────────┼──────────────────────┘
           │                  │
           │                  │
           │      ┌───────────┴───────────┐
           │      │                       │
           ▼      ▼                       ▼
    ┌──────────────────┐      ┌──────────────────┐
    │ Primary (Write)  │      │ Replica 1 (Read) │
    │ db.r6g.4xlarge   │─────▶│ db.r6g.2xlarge   │
    │ 16 vCPU, 128 GB  │      │ 8 vCPU, 64 GB    │
    │ 3K writes/sec    │      │ 3.5K reads/sec   │
    │ CPU: 35%         │      │ CPU: 30%         │
    └──────────┬───────┘      └──────────────────┘
               │                       
               │              ┌──────────────────┐
               └─────────────▶│ Replica 2 (Read) │
                              │ db.r6g.2xlarge   │
                              │ 8 vCPU, 64 GB    │
                              │ 3.5K reads/sec   │
                              │ CPU: 30%         │
                              └──────────────────┘

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import random

# Database connections
PRIMARY_URL = "postgresql://primary.rds.amazonaws.com:5432/db"
REPLICA_URLS = [
    "postgresql://replica1.rds.amazonaws.com:5432/db",
    "postgresql://replica2.rds.amazonaws.com:5432/db",
]

# Connection pools
primary_engine = create_engine(PRIMARY_URL, pool_size=50)
replica_engines = [create_engine(url, pool_size=50) for url in REPLICA_URLS]

class DatabaseRouter:
    @staticmethod
    def get_write_engine():
        """Always use primary for writes"""
        return primary_engine
    
    @staticmethod
    def get_read_engine():
        """Load balance reads across replicas"""
        return random.choice(replica_engines)

# Usage in application
def create_order(user_id, items):
    """Write operation -> Primary"""
    engine = DatabaseRouter.get_write_engine()
    Session = sessionmaker(bind=engine)
    session = Session()
    
    order = Order(user_id=user_id, items=items)
    session.add(order)
    session.commit()
    return order.id

def get_order_history(user_id):
    """Read operation -> Replica"""
    engine = DatabaseRouter.get_read_engine()
    Session = sessionmaker(bind=engine)
    session = Session()
    
    orders = session.query(Order).filter_by(user_id=user_id).all()
    return orders

Option 2: Shard database (4 shards)
    Read capacity: 27K × 4 = 108K QPS
    Write capacity: 6K × 4 = 24K writes/second
    Cost: $5,725 × 4 = $22,900/month
    Complexity: High (requires application changes)

 Handles writes: 2× CPU/memory
 Doesn't help reads: Single instance bottleneck
 Limited scalability: Max db.r6g.16xlarge (64 vCPU, $8K/month)
 Eventual limit: Can't scale beyond largest instance

Cost:
    db.r6g.4xlarge: $2,000/month = $24K/year
    
Problem at 2× growth:
    Traffic: 10K → 20K QPS
    Single instance: 20K QPS / 1 = 20K QPS load
    db.r6g.4xlarge: ~15K QPS capacity
    Result: Still overloaded 

When to use: Write-heavy workload (>50% writes)

 Handles reads: 4× read capacity
 Doesn't help writes: Primary still bottleneck
 Doesn't help CPU: Primary 90% peak (reports still slow)
 Doesn't help memory: Primary 80% (cache evictions)

Cost:
    Primary: $1,000/month
    3 replicas: 3 × $1,000 = $3,000/month
    Total: $4,000/month = $48K/year

Problem:
    Reports: Generate on primary (90% CPU, blocks writes )
    Memory: 80% (cache thrashing, slow queries)

When to use: 90%+ read workload, writes <1K/sec

 Ultimate scalability: Linear (4× capacity)
 High complexity: Application routing logic
 Cross-shard queries: Expensive (fan-out)
 Overkill now: Current load manageable with replicas

Implementation complexity:
    - Routing layer: Calculate shard from user_id
    - Cross-shard JOINs: Impossible (denormalize)
    - Transactions: Limited to single shard
    - Migrations: Complex (rebalance shards)

Cost:
    4 shards × $1,575/month = $6,300/month = $75.6K/year
    
When to use: >100K writes/second, petabyte-scale data

Reality: Premature optimization (wait until needed)

#!/bin/bash
# Create test environment from production snapshot

# 1. Create snapshot of production
aws rds create-db-snapshot \
    --db-instance-identifier prod-postgres \
    --db-snapshot-identifier dr-test-2024-q1

# 2. Wait for snapshot completion (30 minutes)
aws rds wait db-snapshot-completed \
    --db-snapshot-identifier dr-test-2024-q1

# 3. Restore snapshot to test instance
aws rds restore-db-instance-from-db-snapshot \
    --db-instance-identifier test-primary \
    --db-snapshot-identifier dr-test-2024-q1 \
    --db-instance-class db.r6g.xlarge \
    --vpc-security-group-ids sg-test123 \
    --db-subnet-group-name test-subnet-group

# 4. Create read replica in DR region (test DR instance)
aws rds create-db-instance-read-replica \
    --db-instance-identifier test-dr \
    --source-db-instance-identifier test-primary \
    --db-instance-class db.r6g.xlarge \
    --region us-west-2

# Check replication lag
aws rds describe-db-instances \
    --db-instance-identifier test-dr \
    --region us-west-2 \
    --query 'DBInstances[0].ReplicaLag'

# Expected: <60 seconds (within 5-minute RPO )

# Stop test primary (simulates region failure)
aws rds stop-db-instance \
    --db-instance-identifier test-primary \
    --region us-east-1

# Record timestamp: 10:00:00 AM

# Start timer
START_TIME=$(date +%s)

# Promote replica to standalone instance
aws rds promote-read-replica \
    --db-instance-identifier test-dr \
    --region us-west-2 \
    --backup-retention-period 7

# Wait for promotion completion
aws rds wait db-instance-available \
    --db-instance-identifier test-dr \
    --region us-west-2

# End timer
END_TIME=$(date +%s)
RTO=$((END_TIME - START_TIME))

echo "Actual RTO: $RTO seconds"
# Target: <3600 seconds (1 hour)

-- Connect to promoted DR instance
psql -h test-dr.us-west-2.rds.amazonaws.com -U admin -d production

-- Check last transaction timestamp
SELECT MAX(created_at) as last_transaction
FROM orders;

-- Compare to primary failure time (10:00:00 AM)
-- Expected: Within 5 minutes (RPO requirement)

-- Example result:
-- last_transaction: 2024-01-15 09:59:32
-- Failure time:     2024-01-15 10:00:00
-- Data loss:        28 seconds (within 5-minute RPO )

-- Verify record counts (data completeness)
SELECT 
    (SELECT COUNT(*) FROM users) as user_count,
    (SELECT COUNT(*) FROM orders) as order_count,
    (SELECT COUNT(*) FROM products) as product_count;

-- Compare to baseline (taken before drill)
-- Expected: Difference within replication lag window

# Test application can connect to DR database
import psycopg2
import time

def test_dr_connectivity():
    try:
        # Update connection to DR endpoint
        conn = psycopg2.connect(
            host="test-dr.us-west-2.rds.amazonaws.com",
            database="production",
            user="app_user",
            password="...",
            connect_timeout=10
        )
        
        cursor = conn.cursor()
        cursor.execute("SELECT 1")
        result = cursor.fetchone()
        
        print(f"DR database accessible: {result}")
        return True
    
    except Exception as e:
        print(f"DR connectivity failed: {e}")
        return False

# Measure time to first successful query
start = time.time()
while time.time() - start < 3600:  # 1-hour timeout
    if test_dr_connectivity():
        elapsed = time.time() - start
        print(f"Time to first query: {elapsed:.2f} seconds")
        break
    time.sleep(10)  # Retry every 10 seconds

-- Run sample queries (verify performance acceptable)

-- Query 1: User lookup (should be <10ms)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE user_id = 12345;

-- Query 2: Order history (should be <50ms)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 20;

-- Query 3: Analytics (should be <1 second)
EXPLAIN (ANALYZE, BUFFERS)
SELECT DATE(created_at), COUNT(*), SUM(total)
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at);

-- Compare latencies to production baseline
-- Expected: Within 10% (DR region slightly higher latency acceptable)

# Disaster Recovery Drill Report
Date: 2024-01-15
Quarter: Q1 2024
Executed by: DevOps Team

## Objectives
- Verify RTO <1 hour (target: 3600 seconds)
- Verify RPO <5 minutes (target: 300 seconds)
- Validate DR procedure documentation
- Train team on failover process

## Results Summary
| Metric | Target | Actual | Status |
|--------|--------|--------|--------|
| RTO | <3600s | 847s (14 min) | PASS |
| RPO | <300s | 28s | PASS |
| Data integrity | 100% | 100% | PASS |
| Application connectivity | <5 min | 2 min | PASS |

## Timeline
| Time | Event | Duration |
|------|-------|----------|
| 10:00:00 | Simulated primary failure | - |
| 10:00:15 | Started promotion process | 15s |
| 10:14:07 | DR instance available | 847s (14min) |
| 10:16:00 | Application reconnected | 113s (2min) |
| 10:20:00 | Performance validated | 240s (4min) |

## Data Loss Analysis
- Last transaction on primary: 09:59:32
- Primary failure time: 10:00:00
- Replication lag: 28 seconds
- Records lost: 0 (all replicated )

## Issues Identified
1. DNS propagation: Manual update required (should automate)
2. Connection pool: Cached old endpoint (tuned timeout to 30s)
3. Monitoring alerts: Delayed 5 minutes (adjusted thresholds)

## Action Items
- [ ] Automate DNS failover (Route53 health checks)
- [ ] Reduce connection pool timeout (60s → 30s)
- [ ] Update runbook with lessons learned
- [ ] Schedule next drill: April 15, 2024

## Compliance
- Audit requirement: SATISFIED
- Evidence: Drill recording, logs, screenshots
- Retention: 7 years (regulatory)

#!/usr/bin/env python3
"""
Automated DR drill script
Runs quarterly, documents results
"""

import boto3
import time
import psycopg2
from datetime import datetime

class DRDrill:
    def __init__(self):
        self.rds = boto3.client('rds')
        self.metrics = {}
    
    def create_test_environment(self, snapshot_id):
        """Phase 1: Setup test environment"""
        print("Creating test environment from snapshot...")
        
        # Restore snapshot
        self.rds.restore_db_instance_from_db_snapshot(
            DBInstanceIdentifier='test-primary',
            DBSnapshotIdentifier=snapshot_id,
            DBInstanceClass='db.r6g.xlarge'
        )
        
        # Wait for availability
        waiter = self.rds.get_waiter('db_instance_available')
        waiter.wait(DBInstanceIdentifier='test-primary')
        
        # Create replica in DR region
        self.rds.create_db_instance_read_replica(
            DBInstanceIdentifier='test-dr',
            SourceDBInstanceIdentifier='test-primary',
            DBInstanceClass='db.r6g.xlarge',
            SourceRegion='us-east-1'
        )
        
        print("Test environment ready ")
    
    def simulate_failure(self):
        """Phase 2: Simulate primary failure"""
        print("Simulating primary region failure...")
        
        self.failure_time = datetime.utcnow()
        self.metrics['failure_time'] = self.failure_time.isoformat()
        
        # Stop test primary
        self.rds.stop_db_instance(
            DBInstanceIdentifier='test-primary'
        )
        
        print(f"Primary stopped at {self.failure_time}")
    
    def promote_replica(self):
        """Phase 3: Promote DR replica"""
        print("Promoting DR replica...")
        
        start_time = time.time()
        
        # Promote replica
        self.rds.promote_read_replica(
            DBInstanceIdentifier='test-dr'
        )
        
        # Wait for promotion
        waiter = self.rds.get_waiter('db_instance_available')
        waiter.wait(DBInstanceIdentifier='test-dr')
        
        end_time = time.time()
        rto = end_time - start_time
        
        self.metrics['rto_seconds'] = rto
        print(f"Promotion complete in {rto:.2f} seconds")
        
        return rto < 3600  # Pass if <1 hour
    
    def verify_data(self):
        """Phase 4: Verify data integrity"""
        print("Verifying data integrity...")
        
        conn = psycopg2.connect(
            host='test-dr.us-west-2.rds.amazonaws.com',
            database='production',
            user='admin',
            password='...'
        )
        
        cursor = conn.cursor()
        
        # Check last transaction time (RPO)
        cursor.execute("SELECT MAX(created_at) FROM orders")
        last_transaction = cursor.fetchone()[0]
        
        rpo = (self.failure_time - last_transaction).total_seconds()
        self.metrics['rpo_seconds'] = rpo
        
        print(f"Data loss: {rpo:.2f} seconds")
        
        return rpo < 300  # Pass if <5 minutes
    
    def generate_report(self):
        """Phase 5: Generate drill report"""
        report = f"""
        DR Drill Report - {datetime.utcnow().isoformat()}
        
        Results:
        - RTO: {self.metrics['rto_seconds']:.2f}s (target: 3600s)
        - RPO: {self.metrics['rpo_seconds']:.2f}s (target: 300s)
        - Status: {'PASS' if self.passed else 'FAIL'}
        
        Details saved to: dr_drill_{datetime.utcnow().strftime('%Y%m%d')}.json
        """
        
        print(report)
        
        # Save to S3 for audit trail
        # boto3.client('s3').put_object(...)

if __name__ == '__main__':
    drill = DRDrill()
    drill.create_test_environment('snap-prod-20240115')
    drill.simulate_failure()
    rto_pass = drill.promote_replica()
    rpo_pass = drill.verify_data()
    drill.passed = rto_pass and rpo_pass
    drill.generate_report()

Problems:
    - Switching production DNS: 5-10 minute propagation
    - Application reconnection: Connection pool cached endpoints
    - Unknown issues: DR never handled real traffic
    - Switchback: Another 5-10 minutes (20 minutes total downtime )

When to use: Never (too risky for testing)

What it tests: Data integrity only (not DR process)
What it misses: Promotion time, application connectivity, performance

When to use: Continuous data validation (not DR drill)

What it tests: Backup integrity (important but different)
What it misses: Entire DR process

When to use: Backup verification (complementary to DR drills)