AWS vs Azure: Real-World Examples for Every Service — When, Why, and What Breaks

Cosmos DB / DynamoDB wins because:
  - Scales to 20M writes/second without configuration changes
  - Single-item lookups by trip_id: O(1), always 1ms
  - No schema migrations when you add a field to the trip record
  - Partition key = trip_id → perfect distribution, no hot partitions

PostgreSQL would fail because:
  - Cannot handle 20M writes/second on a single instance
  - Sharding PostgreSQL at this scale requires massive engineering effort
  - Schema migrations on a table with billions of rows = hours of downtime

DynamoDB wins because:
  - PK = user_id, SK = watched_timestamp — perfect for "get all items for user X"
  - 250 billion rows in DynamoDB: same 1ms latency as 1,000 rows
  - Automatic scaling — no capacity planning needed
  - Pay per request: cheaper than provisioning for peak

PostgreSQL would fail because:
  - 250 billion rows even in PostgreSQL is manageable with partitioning
    but operational complexity is enormous
  - Any table-wide query (aggregate analytics) would be catastrophically slow
  - Vertical scaling limits eventually hit

DynamoDB wins because:
  - All three access patterns are known upfront — design GSIs for each
  - Time-series data (telemetry) is append-only — no updates, just inserts
  - 2 years × 10,000 devices × 2,880 readings/day = 210 billion records
    DynamoDB handles this without operational overhead
  - DynamoDB TTL automatically expires old telemetry records

PostgreSQL would fail because:
  - TimescaleDB (time-series extension) is needed for this scale
  - Regular PostgreSQL table scans on 210 billion rows = minutes per query
  - Storage cost for 210 billion rows in PostgreSQL is enormous

-- This query is impossible in DynamoDB, trivial in PostgreSQL
SELECT
  o.order_number,
  c.email,
  SUM(li.quantity * li.unit_price) as subtotal,
  d.discount_percentage,
  p.payment_status,
  COUNT(r.id) as refund_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN line_items li ON li.order_id = o.id
LEFT JOIN discount_codes d ON d.id = o.discount_code_id
JOIN payments p ON p.order_id = o.id
LEFT JOIN refunds r ON r.order_id = o.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
  AND o.status = 'unfulfilled'
GROUP BY o.id, c.email, d.discount_percentage, p.payment_status
HAVING SUM(li.quantity * li.unit_price) > 500
ORDER BY subtotal DESC;

PostgreSQL wins because:
  - Complex JOINs across 6 related tables — not possible in DynamoDB
  - Business rules enforced by the database: foreign keys prevent orphan records
  - Transactions: an order refund must atomically update orders, payments,
    inventory, and customer credit — all or nothing
  - Ad-hoc queries: business analysts write SQL directly, no pre-defined access patterns
  - ACID compliance: financial data must never have partial writes

DynamoDB would fail because:
  - No JOINs — you would denormalise everything into one massive item
  - The "ad-hoc reporting" requirement is the killer — 
    DynamoDB cannot answer questions you did not design for at the start
  - Transaction support exists but is limited to 25 items — 
    complex financial operations with many related records hit this limit

-- This is a reporting query. Impossible to design this in DynamoDB upfront.
WITH agent_hourly AS (
  SELECT
    agent_id,
    DATE_TRUNC('hour', call_start) as hour,
    COUNT(*) FILTER (WHERE status = 'answered') as answered,
    COUNT(*) as total,
    ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'answered') / COUNT(*), 2) as answer_rate
  FROM call_logs
  WHERE call_start > NOW() - INTERVAL '30 days'
    AND practice_id = 'p001'
  GROUP BY agent_id, hour
),
practice_avg AS (
  SELECT ROUND(AVG(answer_rate), 2) as avg_rate FROM agent_hourly
),
improvement AS (
  SELECT agent_id,
    AVG(answer_rate) FILTER (WHERE hour > NOW() - INTERVAL '7 days') as recent_rate,
    AVG(answer_rate) FILTER (WHERE hour < NOW() - INTERVAL '7 days') as older_rate
  FROM agent_hourly
  GROUP BY agent_id
)
SELECT i.agent_id, i.recent_rate, i.older_rate,
       i.recent_rate - i.older_rate as improvement,
       p.avg_rate
FROM improvement i
CROSS JOIN practice_avg p
ORDER BY improvement DESC
LIMIT 5;

PostgreSQL wins because:
  - Window functions, CTEs, aggregations, FILTER clauses — all native
  - Business analysts and practice managers write these queries ad-hoc
  - The question was not known when the system was designed
  - Data fits in PostgreSQL at MyBCAT's scale (30 practices, millions of rows)

DynamoDB would fail because:
  - You would need to pre-build a separate aggregation pipeline for every
    possible report — weeks of work per report type
  - DynamoDB cannot GROUP BY across a scan of millions of items efficiently
  - Every new question from the practice manager requires an engineering sprint

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A'
    AND balance >= 500;  -- prevent overdraft
  
  IF NOT FOUND THEN ROLLBACK; END IF;
  
  UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';
  
  INSERT INTO transaction_log (from_account, to_account, amount, timestamp)
  VALUES ('A', 'B', 500, NOW());
COMMIT;

PostgreSQL wins because:
  - ACID transactions: debit and credit are atomic — no partial transfer possible
  - Row-level locking: two transfers from Account A simultaneously 
    cannot both see "balance = £1000" and both proceed
  - Check constraints: balance >= 0 enforced at database level
  - Full audit trail with transaction_log JOIN — complex financial queries

Cosmos DB / DynamoDB:
  - DynamoDB transactions exist but are limited and less expressive
  - For true financial systems: always use ACID-compliant relational database
  - Cosmos DB has full ACID support in its newer versions — valid for financial
    workloads if you use the right consistency level (Strong)

"Do I know every query this data will ever need to answer?"

YES, and the data volume is massive, and latency must be milliseconds
  → Cosmos DB / DynamoDB

NO, or users will write ad-hoc queries, or data is financial
  → PostgreSQL / Azure SQL

"Buy Now" clicked
      ↓
Order record saved (synchronous — must happen before response)
      ↓
Response: "Order confirmed! #12345"
      ↓
Message: "Process order #12345" → Service Bus Queue
      ↓
Each step picks up the message independently:
  Payment processor: deducts card
  Warehouse system: allocates stock
  Email service: sends confirmation
  Analytics: records the sale

If email service is down:
  Message stays in queue, retries after 30 seconds
  Customer already has their confirmation — they do not care
  Email arrives 2 minutes late — acceptable
  No cascade failure

// Sender — Deliveroo order service
await serviceBusClient.CreateSender("order-dispatch")
    .SendMessageAsync(new ServiceBusMessage(JsonSerializer.Serialize(new {
        OrderId = "ord_789",
        RestaurantLocation = "51.5074,-0.1278",
        CustomerLocation = "51.5155,-0.0922"
    })));

// Receiver — rider dispatch service
await using var processor = serviceBusClient.CreateProcessor("order-dispatch");
processor.ProcessMessageAsync += async args => {
    var order = JsonSerializer.Deserialize<Order>(args.Message.Body);
    await DispatchNearestRider(order);
    await args.CompleteMessageAsync(args.Message); // remove from queue on success
};

"Patient admitted" event published to Service Bus Topic
          ↓
┌─────────┬─────────┬─────────┬─────────┬─────────┬─────────┬─────────┐
↓         ↓         ↓         ↓         ↓         ↓         ↓
Ward    Pharmacy  Catering  Billing    Lab    Notification  Audit
(each has its own subscription — processes independently)

Code pushed to GitHub
      ↓
GitHub webhook → Azure Event Grid custom topic
      ↓
Event Grid routing rules:
  Rule 1: branch matches "main" → Event Grid Subscription → Azure DevOps pipeline (full tests + staging deploy)
  Rule 2: branch matches "feature/*" → Event Grid Subscription → Azure DevOps pipeline (unit tests only)
  Rule 3: changed files contains "infrastructure/" → Event Grid Subscription → Terraform Lambda
  Rule 4: ALL events → Event Grid Subscription → Slack notification function
  Rule 5: tag matches "v*" → Event Grid Subscription → Production deployment pipeline

4 million cars → Event Hubs (partitioned stream, 30-day retention)
                      ↓
        ┌─────────────┼────────────────┐
        ↓             ↓                ↓
  App Dashboard   Safety Monitor    ML Pipeline
  Consumer Group  Consumer Group    Consumer Group
  
  (each reads independently, at their own speed, from their own offset)
  
Safety Monitor reads in real time — triggers alert if brake anomaly
ML Pipeline reads 6 hours behind — batch processing for training
Analytics reads once per day — aggregates the full day's data

Stripe webhook → Azure Function (HTTP trigger)
  → Update database
  → Queue email job
  → Queue CRM update
  
Function runs for ~200ms per webhook
Scales from 1 to 5,000 concurrent invocations automatically
Cost: 1 million invocations × 200ms × 256MB = ~$0.40/month

Azure Logic App timer (or EventBridge Scheduler) → triggers Lambda/Function
  → query PostgreSQL for last 7 days of data per practice
  → generate PDF report (pdfkit)
  → upload to S3/Blob Storage
  → send email via SendGrid
  
Runs: once per week, 30 practices, ~5 minutes total
Cost: essentially free (well within free tier for both Lambda and Functions)

AWS Lambda: maximum timeout = 15 minutes → cannot finish
Azure Functions Consumption: maximum timeout = 10 minutes → cannot finish

Solution:
  Lambda/Function receives the upload notification → puts job in queue
  EC2 Spot Instance / Azure VM (Spot) picks up the job → runs for 45 minutes
  Sends completion notification → Lambda/Function updates the database

S3 / Azure Blob Storage wins:
  - Unlimited storage — add 1 million songs tomorrow, no provisioning
  - 99.999999999% (11 nines) durability — a song is never lost
  - CloudFront/Azure CDN in front: song file served from edge node near the listener
  - Presigned URL / SAS token: Spotify's backend generates a 15-minute access URL
    Your browser downloads directly from S3/Blob — Spotify's servers not involved
  
PostgreSQL BLOB would fail:
  - A 700TB table in PostgreSQL is technically possible but operationally a nightmare
  - Binary data in databases bypasses database indexing entirely
  - No CDN integration — every download goes through your server

S3 / Azure Blob workflow:
  1. Call ends on Amazon Connect
  2. Connect uploads recording to S3 automatically
  3. S3 key: recordings/{practiceId}/{date}/{callId}.mp3
  4. Metadata: { practiceId, agentId, patientPhone, duration }
  5. KMS encryption key: per-practice CMK
  6. Object Lock: GOVERNANCE mode, 7-year retention (HIPAA)
  7. Lifecycle rule: 
     - Standard tier (first 30 days — frequently accessed for review)
     - S3 Infrequent Access (30 days to 1 year)
     - S3 Glacier (1-7 years — legal archive, rarely accessed)
  
Cost optimisation:
  10,000 calls × 3MB = 30GB/day
  Standard: $0.023/GB → $0.69/day
  Glacier: $0.004/GB → $0.12/day
  Lifecycle to Glacier after 30 days saves 83% on storage cost

Cognito / Entra External ID setup:
  
  User signs in → enters email + password + MFA code
  Cognito validates credentials
  Issues JWT with custom claims:
  {
    "sub": "user-uuid-123",
    "email": "sarah@besteyecare.com",
    "custom:practice_id": "practice_p001",
    "custom:role": "scheduling_agent",
    "exp": 1745000000
  }
  
  Every Lambda/Function:
    1. Validate JWT signature (Cognito public key)
    2. Read practice_id from claims
    3. Use practice_id as DynamoDB partition key prefix
    4. Agent from Practice A CANNOT access Practice B's data
       — physically cannot form a valid query

Wrong approach (what most teams do initially):
  DB_PASSWORD = "super_secret_123" in .env file
  → Committed to git accidentally
  → Visible to everyone with AWS console access
  → Never rotated because manual rotation requires updating everywhere

Right approach (Key Vault / Secrets Manager):
  
  Terraform creates the secret:
    aws_secretsmanager_secret "db_password"
    
  Each Lambda's IAM role allows:
    secretsmanager:GetSecretValue on this specific secret ARN only
    
  Lambda code:
    @lru_cache  # fetch once per cold start, not per invocation
    def get_db_password():
        return secrets_manager.get_secret_value(SecretId='mybcat/prod/db')['SecretString']
    
  Secrets Manager auto-rotation:
    Every 90 days → generates new password → updates RDS → updates secret value
    Lambda fetches new password on next cold start
    Zero manual work, zero downtime
    
  Audit trail:
    CloudTrail logs every GetSecretValue call:
    "Lambda function mybcat-reports-prod fetched db_password at 14:23:01"
    If someone unexpected fetches the secret → alert fires

Request trace for POST /api/appointments (total: 8,247ms)
│
├── Cognito JWT validation: 12ms
├── Lambda cold start: 2,100ms ← problem #1
│
└── Lambda execution: 6,135ms
    ├── DynamoDB GetItem (slot check): 8ms
    ├── Insurance verification API call: 5,912ms ← problem #2
    └── DynamoDB TransactWrite (booking): 215ms

Root causes identified:
  1. Lambda had no provisioned concurrency — cold start on first request of the day
  2. Insurance verification API (Availity) was degraded — their status page confirms

Fix:
  1. Add provisioned concurrency = 5 for booking Lambda
  2. Add 3-second timeout on insurance API call + circuit breaker
     If Availity times out: use last-known cached eligibility, flag for re-verification
  3. Add CloudWatch alarm: p99 booking latency > 2,000ms → PagerDuty alert

Without monitoring: you never find out until a HIPAA audit

With Azure Monitor + CloudTrail:
  Alert rule:
    DynamoDB:GetItem called by user "john.doe@mybcat.com"
    at time between 00:00 and 06:00
    → Trigger PagerDuty → Security team investigates
  
  Also:
    CloudTrail shows every API call with source IP
    "john.doe accessed 847 patient records from IP 203.45.67.89
     between 02:14 and 03:58 on 2026-04-15"
  
  HIPAA response:
    - Immediately revoke Cognito user
    - Document the access in breach assessment
    - Determine if 847 records constitute a reportable breach
    - Notify patients if threshold met

With Terraform:
  All infrastructure is code in git:
  mybcat-infra/
    modules/
      lambda/       ← reusable Lambda module
      dynamodb/     ← reusable DynamoDB module with HIPAA defaults
      api-gateway/
    environments/
      dev/
        main.tf     ← uses modules, dev-specific values
      prod/
        main.tf     ← same modules, prod-specific values (more memory, provisioned concurrency)

  New engineer spins up a complete dev environment:
    terraform workspace new dev-john
    terraform apply
    ← 10 minutes later: complete copy of the architecture, ready to use

  Accidental deletion:
    terraform plan detects drift → shows "aws_security_group will be created"
    terraform apply → security group restored exactly as it was
    
  SOC2 audit:
    git log shows every infrastructure change with author, timestamp, reason
    "2026-03-15 — added KMS encryption to recordings bucket — PR #245 — HIPAA requirement"

STORAGE DECISION:
  ┌─ Is this structured data with relationships? ─────────── PostgreSQL
  ├─ Is this high-volume with known access patterns? ──────── DynamoDB / Cosmos DB
  ├─ Is this binary data (files, images, audio)? ─────────── S3 / Blob Storage
  ├─ Is this time-series data (telemetry, metrics)? ──────── TimescaleDB or DynamoDB
  └─ Is this session data or cache? ─────────────────────── Redis (ElastiCache)

MESSAGING DECISION:
  ┌─ Must this happen exactly once? ───────────────────────── SQS / Service Bus Queue
  ├─ Must many services react to this event? ──────────────── SNS / Service Bus Topic
  ├─ Must I route events based on their content? ──────────── EventBridge / Event Grid
  └─ Must many consumers read the same stream independently? ─ Kinesis / Event Hubs

COMPUTE DECISION:
  ┌─ Is this event-driven and short-lived? (<15 min) ──────── Lambda / Azure Functions
  ├─ Is this a long-running process? ──────────────────────── EC2 / Azure VM
  ├─ Is this a containerised service? ────────────────────── ECS/Fargate / Container Apps
  └─ Is this a complex multi-step workflow? ───────────────── Step Functions / Durable Functions

AUTHENTICATION DECISION:
  ┌─ Is this a customer-facing app with user accounts? ─────── Cognito / Entra External ID
  ├─ Is this an internal enterprise app? ───────────────────── Cognito / Entra ID (corporate)
  └─ Does a service need to call another service? ──────────── IAM Role / Managed Identity