Designing High-Performance Catalogue Architecture for Large E-Commerce
Architecting e-commerce infrastructure supporting 500,000+ SKUs with multi-attribute faceted filtering, sub-30ms search response times, and zero database lockups during flash sales.
An international fashion and technology retailer saw their database crash during promotional campaigns due to complex SQL JOIN queries on product variants, categories, and inventory tables.
Enabling lightning-fast faceted search across 20+ dimensions (size, color, brand, price range, compatibility) while ensuring inventory levels remain strictly accurate and carts never double-sell limited stock.
+-------------------------------------------------------------------------+
| SHOPPERS & BOTS |
| (Flash Sale Spikes: 25,000 Req/Sec) |
+------------------------------------+------------------------------------+
|
v
+-------------------------------------------------------------------------+
| CLOUDFLARE EDGE (STALE-WHILE-REVALIDATE) |
| - Serves cached product details & category pages in 15ms |
| - Real-time stock status injected via Edge KV / Micro-Fetch |
+------------------------------------+------------------------------------+
| Cache Miss / Dynamic Filter
v
+-------------------------------------------------------------------------+
| SEARCH & FACETED CATALOGUE (ELASTICSEARCH / MEILISEARCH) |
| - Inverted Index of all 500,000 SKUs |
| - Pre-computed facet counts (eliminates catastrophic SQL JOINs) |
+------------------------------------+------------------------------------+
| Checkout / Cart Action
v
+-------------------------------------------------------------------------+
| ISOLATED TRANSACTIONAL CART ENGINE |
| - Redis Atomic Decrement (DECR) for inventory reservation lock |
| - 10-Minute Cart Hold Timer |
| - Flushes committed orders to PostgreSQL with ACID transactions |
+-------------------------------------------------------------------------+Core Technology Foundation
Architectural Rationale & Trade-Offs
Evaluating failure modes, scaling bottlenecks, and defensive engineering decisions.
Why dedicated search engines over SQL queries for product catalogues?
Filtering 500,000 products by 6 attributes (e.g. Size=L, Color=Navy, Brand=Nike, Price=$50-$100) requires multiple table JOINs across products, variants, tags, and categories. At scale, this forces full table scans that lock database CPU at 100%. A dedicated search index pre-computes attribute facets and returns results in 12ms.
How do you prevent inventory overselling during flash sales?
Redis atomic counters with optimistic reservation locks. When a user clicks "Add to Cart", Redis executes `DECR product_stock:{sku}`. If the value drops below zero, the reservation fails instantly without touching Postgres. If successful, a 10-minute expiration key holds the reservation. If the user abandons checkout, the reservation expires and Redis increments the stock automatically.
Where does caching live?
Stale-While-Revalidate caching at the CDN Edge for product catalogues. When stock changes, an event triggers an asynchronous cache revalidation webhook to purge the specific product URL.
What happens at 10x traffic during Black Friday / Cyber Monday?
Read traffic (98% of total requests) is absorbed by CDN Edge caching and Elasticsearch read clusters. The primary PostgreSQL database handles strictly validated checkout order commitments, keeping database load well below 30%.
What happens if the search cluster crashes?
The application automatically falls back to indexed PostgreSQL read-replica queries with simplified pagination, ensuring the store stays online and transactional.
What would I deliberately NOT build?
1) No real-time relational table locking (`SELECT FOR UPDATE`) on product rows during cart additions, which creates massive lock contention and crashes checkout funnels. 2) No un-cached live calculation of cart totals or tax lookups on every single keypress.
Project Forge & Project Aurora: High-Scale E-Commerce Architectures
Sub-second catalogue search across 500k+ SKUs
You own the system.
The code. The infra. The data.
Software should not become valuable enough to matter and then be impossible for the client to own or operate.
Everything I build is 100% client-owned intellectual property from day one. You own the code, the accounts, the infrastructure, and the data. No vendor lock-in.
What belongs to you in full:
- 01Source code & Complete Git Repositories
- 02Apple, Google & Enterprise Developer Accounts
- 03Cloud Infrastructure, VPCs & Production Databases
- 04Figma Design Files, Assets & Documentation
- 05CI/CD Deployment Pipelines & Signing Keys
Have a system that needs this level of architectural rigor?
Whether you are building from zero or rescuing a fragile prototype, I provide decisive architectural leadership.
Start an Architectural Engagement