Requirements: Complex Faceting & Extreme Read/Write Asymmetry
An e-commerce product catalog (like Amazon or Shopify) must support sub-100ms multi-attribute filtering (brand, size, color, price range, customer rating), full-text search across titles and descriptions, and instant inventory availability updates.
CQRS (Command Query Responsibility Segregation) Architecture
When a Dedicated Search Index Helps Faceting
Relational systems can support faceting with normalized attributes, arrays or JSON indexes, generated columns, and engine-specific bitmap or spatial structures. At large catalog scale, combining full-text relevance with many dynamic filters and facet counts may be easier to scale in a dedicated search index. Choose from measured query shapes, update freshness, operational cost, and consistency needs rather than assuming SQL must full-scan or fail.
Elasticsearch & Doc Values for Faceting
Search engines like Elasticsearch or OpenSearch index documents in inverted indexes for full-text search and simultaneously build Doc Values (columnar disk structures). Columnar storage makes aggregation and facet counting across millions of matching products fast, though high-cardinality facets cost more, and terms-aggregation counts across shards can be approximate (tuned with shard_size).
Change Data Capture (CDC) Sync
To avoid dual-write inconsistencies between the transactional database and the search index, production architectures deploy Change Data Capture (via Debezium reading Postgres WAL or DynamoDB Streams) to stream mutations reliably into Kafka.
Worked example: sizing the index
- The catalog has 50 million SKUs at about 4 KB of indexed JSON each (title, description, brand, attributes, price and rating). That is about 200 GB of primary data, or roughly 400 GB with one replica. The on-disk index size varies with mappings, so measure it.
- With shards targeted at 10 to 50 GB, about 8 to 12 primary shards is sensible. Too many small shards waste heap memory and slow down queries that fan out to every shard.
- Merchants and inventory systems send about 3,000 updates per second at peak (price and stock). With
refresh_intervalat 1 s, changes become searchable within about a second. Bulk-index updates in batches of 1,000 to 5,000 documents instead of sending one request per document. - Queries peak at 5,000 per second, each with 6 facet aggregations. Scale reads by adding replicas, and cache common category landing pages (the first page of popular categories) for 30 to 60 s.
Where should faceting live?
| Option | Strength | Weakness | Good fit |
|---|---|---|---|
| Relational DB (Postgres with GIN on JSONB, or generated columns) | Transactional, one system | Relevance ranking and many dynamic facet counts get hard beyond a few million rows | Small and medium catalogs |
| Search engine (Elasticsearch / OpenSearch / Solr) | BM25 relevance, fast aggregations over doc values, typo tolerance | Second system to run; eventual consistency | Large catalogs with rich search |
| Hosted search (Algolia, Typesense Cloud) | Low operational load, fast | Cost per record and per operation; less control | Teams without search specialists |
| Precomputed category pages in a KV store or cache | Fastest reads | Combinations of filters explode | The top N category and sort combinations only |
Handling fast-changing inventory
Stock levels change much more often than titles. Common patterns:
- Index a coarse stock bucket (in stock, low, out) instead of the exact count, so most stock changes do not require reindexing.
- Apply exact availability at display time, by looking up the 24 SKUs on the page in an inventory cache.
- Rank out-of-stock items lower rather than removing them, so pages do not reshuffle every time stock changes.
Amazon, Shopify and Zalando have all described this split publicly: a source-of-truth catalog service, change streams, and a search layer tuned for relevance and facets.