Design an Ecommerce Platform (Amazon / Flipkart)

Why MongoDB or DynamoDB for Product Catalog (Not MySQL)?

Balancing consistency guarantees, latency SLAs, and partition tolerance across product catalogs, inventory management, distributed transactions, and search infrastructure. The first comparison evaluates document databases against relational schemas for highly variable product category attributes.

Product data is inherently heterogeneous:
  - Apparel attributes: size, color, fabric, fit
  - Electronics attributes: RAM, CPU, screen size, GPU, battery life
  - Book attributes: ISBN, author, publisher, page count, language
  
Relational Database Approaches (MySQL / PostgreSQL):
  Option A: Single wide table with 200+ nullable columns
    Result: Sparse data layout, high storage overhead, and less efficient indexing.
  Option B: Entity Attribute Value (EAV) pattern
    Result: Poor query latency, weak schema-level type enforcement, and multiple JOIN operations per attribute lookup.
  Option C: Native JSON column
    Result: Better schema flexibility, but query optimization and secondary indexing become harder to manage at very large catalog scale.
  
Document Database Approach (MongoDB / DynamoDB):
  - Each product record encapsulates only its relevant attributes without NULL padding.
  - Category expansions do not require relational table schema migrations, although new indexes may still need separate rollout work.
  - Rich queries over nested attribute structures: db.products.find({"attributes.RAM": "16GB"})
  - Secondary indexes support high cardinality filtered searches.

  // Apparel document structure:
  {
    "product_id": "prod_102",
    "title": "Running Crewneck",
    "category": "clothing",
    "attributes": { "size": ["S", "M", "L", "XL"], "color": "Navy", "fabric": "Cotton" }
  }
  
  // Electronics document structure:
  {
    "product_id": "prod_504",
    "title": "Developer Laptop Pro",
    "category": "electronics",
    "attributes": { "RAM": "32GB", "CPU": "M3 Max", "screen": "16-inch", "GPU": "30-core" }
  }

Workloads Requiring Relational Databases:
  - Orders: ACID transaction guarantees spanning orders, line items, and payment references.
  - Inventory: Strict consistency with CHECK (quantity >= 0) constraints.
  - User accounts: Relational integrity across users, delivery addresses, and saved payment profiles.
  
Recommendation:
  Use PostgreSQL or MySQL for transactional order and inventory domains, and MongoDB or DynamoDB for the product catalog.

Inventory Management: The Hardest Problem

Analyzing concurrency control mechanisms to prevent overselling under high checkout contention:

-- Concurrency challenge:
-- 100 users click "Buy Now" simultaneously for the last remaining item in stock.
-- Without concurrency controls, 100 orders are created and 99 customers are disappointed.

-- Approach 1: Pessimistic Row Locking (SELECT FOR UPDATE)
BEGIN TRANSACTION;
SELECT available_quantity FROM inventory WHERE product_id = 'prod_123' AND seller_id = 'seller_456' FOR UPDATE;
-- Verify: available_quantity >= 1
UPDATE inventory SET available_quantity = available_quantity - 1 WHERE product_id = 'prod_123' AND seller_id = 'seller_456';
COMMIT;

-- Trade-offs:
-- Guaranteed correctness under high concurrency.
-- Lock contention serializes 100 concurrent requests on a single row.
-- For multi item orders, update inventory rows in a deterministic product order to reduce deadlock risk.
-- Risk of database connection pool exhaustion under sustained contention.

-- Approach 2: Atomic Conditional Decrement (Optimistic) [Recommended for standard operations]
UPDATE inventory
SET available_quantity = available_quantity - 1
WHERE product_id = 'prod_123' AND seller_id = 'seller_456' AND available_quantity >= 1;

-- Execution logic:
-- If rows_affected = 1: Stock successfully reserved, proceed to payment authorization.
-- If rows_affected = 0: Insufficient stock, reject checkout with an out of stock response.
-- Trade-offs:
-- No explicit application held row lock, eliminating lock wait bottlenecks.
-- Higher throughput for this single row conditional update. Larger multi item transactions can still encounter contention, so use a deterministic update order.
-- Every reservation request still incurs a relational database write.

-- Approach 3: Redis In-Memory Admission Control [Recommended for high velocity flash sales]
-- Setup: Preload available inventory:
SET flash:stock:seller_456:prod_123 1000

-- Use one Lua script so check and decrement are atomic.
-- If stock is available, decrement and return the remaining count.
-- If stock is exhausted, return -1 without changing the counter.
local stock = redis.call('GET', KEYS[1])
if not stock or tonumber(stock) <= 0 then
  return -1
end
return redis.call('DECR', KEYS[1])

-- Application flow:
-- Returned value >= 0: Admit the request and issue a provisional reservation token.
-- Durable Inventory Service then confirms the token with the primary database.
-- Durable reservation failure: invalidate the token and reconcile the Redis counter.
-- Returned value -1: Reject the request as sold out.

-- Trade-offs:
-- Sub-millisecond admission latency, sustaining over 100,000 requests per second at the Redis gate.
-- Redis memory state and database state can temporarily drift during failures, so Redis is not the final source of truth.
-- Requires durable reservation processing, idempotent requests, and periodic reconciliation to synchronize Redis state with the primary database.

-- Recommendation:
-- Use Approach 2 for standard catalog purchasing.
-- Use Approach 3 for limited-quantity flash sales and high velocity promotional drops.

Saga Pattern vs 2PC for Distributed Transactions

Compares distributed coordination patterns across autonomous microservice databases.

Order placement spans multiple distributed services:
  1. Inventory Service (reserve stock)
  2. Order Service (create the order in PAYMENT_PENDING)
  3. Payment Service (authorize payment)
  4. Inventory Service (commit the reservation)
  5. Order Service (move the order to PAYMENT_AUTHORIZED and start fulfillment)
  6. Payment Service (capture payment after shipment)
  7. Notification Service (send confirmation)

Two Phase Commit (2PC):
  Coordinator asks all participants: "Can you commit?"
  All respond YES: Coordinator broadcasts "COMMIT"
  Any responds NO: Coordinator broadcasts "ABORT"

  Limitations:
  - Blocking: If the coordinator crashes after the prepare phase, participants can hold locks until recovery.
  - High latency caused by synchronous multi phase coordination.
  - Tight coupling across heterogeneous microservice databases.

Saga Pattern (Recommended):
  Each step commits locally and defines an explicit compensating action for a later failure.

  Step 1: Reserve Inventory                  <-- Compensate: Release Inventory
  Step 2: Create Order (PENDING)             <-- Compensate: Cancel Order
  Step 3: Authorize Payment                  <-- Compensate: Void Authorization
  Step 4: Commit Inventory + Start Fulfillment <-- Compensate: Void Authorization + release inventory if required
  Step 5: Capture Payment after Shipment     <-- Compensate: Refund if capture must be reversed
  Step 6: Send Notification                  <-- No compensation required

  Failure Scenario:
  If Step 3 fails:
    1. Cancel the pending order if required.
    2. Release the inventory reservation.
    3. Return a descriptive payment failure response to the user.

Coordination Models:
  Orchestration (Recommended for order workflows):
    Order Service acts as the centralized orchestrator.
    It invokes each service sequentially and executes compensating transactions upon failure.
    Benefits: Explicit state machine, clear operational tracing, centralized error handling.
    Trade-off: The orchestrator coordinates all steps and requires durable execution state.

  Choreography (Event-driven):
    Services publish events to Kafka, and downstream services react autonomously.
    Benefits: Loosely coupled service architecture.
    Trade-offs: Cross service workflows become harder to trace, and compensations across event handlers become more complex.

Search: Elasticsearch vs Database Full Text Search

Compares native relational text indexing with dedicated distributed search clusters.

MySQL FULLTEXT Index:
  Strengths:
    - Simple operational model without additional cluster infrastructure.
  Limitations:
    - Limited relevance scoring and ranking customizability.
    - Not designed to sustain low latency faceted aggregations across brand, price range, and rating at this catalog scale.
    - Lacks fuzzy matching for typographical errors (for example, matching "iphne" to "iPhone").
    - No autocomplete prefix suggesters.
    - Query latency becomes harder to keep predictable at 500M SKUs under high concurrent search traffic.

Elasticsearch (Recommended):
  Strengths:
    - BM25 relevance scoring tuned with term frequency and document length normalization.
    - High performance multi dimensional faceted search using bucket aggregations.
    - Fuzzy matching, synonym dictionaries, and language-specific stemming.
    - Low latency autocomplete using prefix completion suggesters.
    - Horizontal scalability through shard distribution across worker nodes.
    - Near real time indexing with typical document visibility within 1 to 5 seconds from the database write.
  Trade-offs:
    - Additional infrastructure footprint and cluster management overhead.
    - Not a primary source of truth, requiring continuous synchronization from transactional databases.
  
  Synchronization Architecture:
    Catalog database changes stream through Kafka using Change Data Capture or an equivalent change stream.
    Elasticsearch consumer workers ingest events and index documents asynchronously.
    Typical end-to-end sync latency is 1 to 5 seconds under normal load.

Cart Design: Redis vs Database vs Client Side

Compares session storage tiers for shopping cart persistence, latency, and recovery.

Client Side Storage (localStorage):
  Strengths:
    - Zero backend server compute or storage load.
    - Fully operational during offline periods.
  Limitations:
    - Cart is lost upon device switch, browser cache clearance, or private browsing sessions.
    - No cross device synchronization.
    - Vulnerable to client tampering, requiring mandatory price revalidation.
  Best for: Unauthenticated guest browsing before session creation.

Relational Database (PostgreSQL or MySQL):
  Strengths:
    - Persistent across sessions and devices.
    - Strict ACID transaction guarantees.
  Limitations:
    - Incurs a database write for every cart item addition, modification, or removal.
    - Higher resource overhead for ephemeral sessions where many carts are abandoned.
  Best for: Carts requiring stronger durability or recovery guarantees than the session cache provides.

Redis Hash (Recommended):
  Strengths:
    - Sub-millisecond read and write latency.
    - Append Only File (AOF) persistence can preserve state across process restarts when configured durably.
    - Automatic expiration using TTL (auto-expires abandoned carts after 30 days).
    - Native hash data structure: HSET cart:{user_id} {product_id} {quantity}
  Limitations:
    - In-memory storage still requires replication and careful cluster sizing. A separate durable cart store is only needed when business requirements exceed Redis recovery guarantees.
  Best for: Authenticated users at high scale.

Hybrid Architecture (Industry Standard):
  1. Guest users: Cart state persists in browser localStorage.
  2. Authentication: Guest cart merges into Redis session store upon user login.
  3. Logged-in browsing: Redis serves as the primary low latency cart store.
  4. Checkout initiation: Line item prices, discounts, and inventory stock are re-validated against the primary database.

Why Separate Read and Write Databases (CQRS)?

Isolates read heavy catalog browsing from write heavy order processing to maintain predictable conversion performance.

Without CQRS:
  A single database handles both catalog browsing (reads) and checkout processing (writes).
  Problem: During sales events, spikes in checkout write traffic saturate database connections,
  causing product browsing pages to slow down and reducing customer conversion.

With CQRS (Command Query Responsibility Segregation):
  Write Model:
    PostgreSQL clusters manage orders and inventory with ACID transactions and CHECK constraints.
  Read Model:
    Elasticsearch handles full text search and faceted filtering.
    Redis caches hot product detail pages.
    MongoDB read replicas serve catalog browsing queries.
  
  Synchronization:
    Transactional writes record domain events in an outbox, and an outbox relay publishes them to Kafka so read models update asynchronously.
  
  Benefits:
    - Reads and writes scale independently according to traffic asymmetry. In this baseline, 5B product page views vs 10M daily orders represents roughly 500 product page views per order, which illustrates the much heavier browsing workload.
    - Each database technology is optimized for its specific access pattern.
    - Write database load during peak flash sales is isolated from catalog read capacity, reducing the chance that checkout spikes degrade browsing latency.
  Trade-offs:
    - Eventual consistency between write and read models introduces a transient sync lag.
    - Increased architectural and operational complexity.
  
  Mitigating the Consistency Gap:
    When a product price is updated, Kafka normally synchronizes Elasticsearch within 1 to 5 seconds.
    To prevent checkout with stale catalog data, the checkout orchestrator always re-fetches
    authoritative pricing and stock directly from the write database before charging the customer.