System Design: E-Commerce Data Layer
On this page
The problem: design an e-commerce data layer
An e-commerce platform serves 20 million customers. Peak traffic is around 5,000 orders per minute and 50,000 page views per second. Product data varies by category. Finance needs daily reports; product teams want to analyze browsing behavior. Design the data layer. Work from the center outward and state what each choice costs.
The transactional core: schema, transactions and indexes
Orders, payments, customers and inventory live in a normalized relational schema (Day 21) on PostgreSQL or SQL Server. These need ACID transactions (Day 27): placing an order reserves stock and records payment intent together.
Order lines snapshot product name and price at purchase time, the one deliberate denormalization in the core.
Indexes follow the hot queries (Day 22): (customer_id, created_at DESC) for "my orders", (status, created_at) for the fulfillment queue, plus a unique index on the order number. Each is verified with a query plan, and each costs something on every insert, so no speculative ones.
Scaling reads and connections
5,000 orders per minute is about 85 writes per second for orders, plus their lines and stock updates: well within one primary. The exception is a flash sale on one product: thousands of orders trying to decrement the same inventory row at once queue on its lock (Day 21). Reserve stock with an atomic conditional update, and for extreme cases split a hot item's stock into several rows.
Reads dominate, so:
- Read replicas (Day 25) serve order history and back-office screens; read-after-write paths, like the order confirmation page, read from the primary.
- Connection pools sized from measured concurrency, with PgBouncer in front of the primary (Day 28).
- Caching (Module 4) takes most product page reads off the database entirely.
When writes outgrow one primary: sharding and backups
If growth continues, shard (Day 26) orders and customers by customer id, so a customer's orders and transactions stay on one shard. Platform-wide questions ("orders per region today") move to the analytics side instead of fanning out across shards. Use logical shards from the start of the sharding project, so adding servers later moves shards, not rows. Switch to ids that are unique across shards, such as UUID version 7, before the split, not during it.
Backups sit under all of this. Replicas copy mistakes instantly (Day 25), so keep point-in-time recovery on the primary and test restores. Know how long restoring the full database takes; at several terabytes, it's hours.
Specialized stores: Redis, search and analytics
| Data | Store | Reason |
|---|---|---|
| Carts and sessions | Redis | Key lookups, very high rate, expiring (Day 24) |
| Product catalog | Relational with jsonb attributes, or a document store | Attributes vary by category (Day 23) |
| Product search | Elasticsearch or OpenSearch, fed by CDC | Full-text relevance and facets (Day 23) |
| Analytics | Warehouse + data lake, fed by CDC (Day 29) | Large scans away from production |
Each extra store is justified by an access pattern the core database serves poorly, and each adds operational cost, so name it.
What a strong data layer design includes
- Starting from numbers, and noticing that one primary handles the write load for now.
- A normalized core with transactions where correctness matters.
- Indexes tied to named queries, not "index everything".
- Replicas with a stated read-after-write rule.
- A shard key chosen for the dominant access pattern, with its cross-shard cost named.
- Each specialized store justified by an access pattern and an operational cost.
- Analytics separated from production.
- Backups with tested restores, and a stated restore time.
- A rough monthly cost for each store, because each one is a bill as well as a system to run.
Check yourself
The order was just written. A lagging replica might not have it yet, and the customer would see "order not found" right after paying.
Most queries and transactions are per customer, so they stay on one shard. Sharding by order id would scatter one customer's orders across all shards.
The CDC pipeline into the search index. The deletion should flow as an event; check whether it was published, consumed or rejected, and whether the consumer is lagging.
In the analytics warehouse, fed continuously by change data capture from every shard. Querying all production shards for each dashboard refresh would add load to the transactional databases and couple finance's queries to the store's availability. The warehouse is built for exactly these cross-shard aggregates.