Product Catalog System Low-Level Design

By /
⏱ 5 min read

What is a Product Catalog System?

A product catalog stores and serves product data: names, descriptions, images, prices, variants, and attributes. Amazon has 350 million products; Shopify serves millions of merchants. The catalog must support fast reads (product page, search), flexible attribute schemas (a laptop has different fields than a dress), variant management (size x color combinations), and real-time price and availability updates without full cache invalidation.

Requirements

  • Store products with flexible attributes (electronics vs apparel have different fields)
  • Variant support: size, color, material combinations with per-variant pricing and stock
  • Real-time price and availability updates (<5 second propagation)
  • Product page load: <100ms including images, price, variants, reviews count
  • Full-text search across name, description, attributes
  • 100M products, 10K price updates/second during sales events

Data Model

Product(product_id UUID, merchant_id UUID, name VARCHAR,
        description TEXT, category_id UUID,
        status ENUM(ACTIVE, INACTIVE, DRAFT),
        created_at, updated_at)

ProductAttribute(product_id UUID, key VARCHAR, value VARCHAR,
                 PRIMARY KEY (product_id, key))
-- EAV: flexible attributes without schema changes
-- 'brand'='Apple', 'processor'='M3', 'screen_size'='14'

ProductVariant(variant_id UUID, product_id UUID, sku VARCHAR UNIQUE,
               options JSONB,          -- {'size': 'M', 'color': 'blue'}
               price_cents INT,
               compare_at_price_cents INT,
               inventory_quantity INT DEFAULT 0)

ProductImage(image_id UUID, product_id UUID, variant_id UUID,
             url VARCHAR, alt_text VARCHAR, position INT)

Flexible Attributes with EAV

Entity-Attribute-Value (EAV) allows arbitrary product attributes without schema changes. A laptop gets ‘processor’, ‘ram_gb’, ‘storage_type’; a dress gets ‘material’, ‘neckline’, ‘sleeve_length’. These cannot be predefined as columns without hundreds of nullable columns.

-- Index for attribute-based filtering
CREATE INDEX idx_attr_lookup ON ProductAttribute(key, value, product_id);

-- Filter products by attribute: all Apple laptops
SELECT DISTINCT p.product_id FROM Product p
JOIN ProductAttribute a1 ON p.product_id = a1.product_id
JOIN ProductAttribute a2 ON p.product_id = a2.product_id
WHERE a1.key='brand' AND a1.value='Apple'
  AND a2.key='category' AND a2.value='laptop';

-- Full product detail (3 queries, no N+1)
def get_product_detail(product_id):
    product    = db.get_product(product_id)
    attributes = db.get_attributes(product_id)
    variants   = db.get_variants(product_id)
    images     = db.get_images(product_id)
    return {**product, 'attributes': attributes,
            'variants': variants, 'images': images}

Price and Availability Updates

def update_variant_price(variant_id, new_price_cents):
    db.execute('''
        UPDATE ProductVariant SET price_cents=:price, updated_at=NOW()
        WHERE variant_id=:vid
    ''', price=new_price_cents, vid=variant_id)

    # Invalidate product page cache
    product_id = get_product_id_for_variant(variant_id)
    redis.delete(f'product:{product_id}')

    # Publish event for search index reindex
    kafka.produce('catalog-updates', {
        'type': 'price_update',
        'variant_id': str(variant_id),
        'product_id': str(product_id)
    })

Product Page Caching

def get_product_page(product_id):
    key = f'product:{product_id}'
    cached = redis.get(key)
    if cached:
        return json.loads(cached)

    product = get_product_detail(product_id)
    redis.setex(key, 300, json.dumps(product))  # TTL 5 minutes
    return product

Event-driven invalidation: Kafka consumer listens for catalog-updates events and calls redis.delete for affected product keys. This ensures price changes propagate within seconds rather than waiting for TTL expiry.

Key Design Decisions

  • EAV for attributes — flexible schema; new attribute types need no migrations
  • Variants as separate rows — each size/color combination has its own SKU, price, and inventory count
  • Redis cache for product pages — 3-4 DB queries per product; cache keeps origin load manageable
  • Event-driven cache invalidation — Kafka price-update event triggers immediate cache delete
  • Batch attribute/variant/image queries — avoids N+1; 3 queries total for full product detail regardless of variant count

Product catalog system design is a key topic in Amazon system design interview questions.

Product catalog and variant management design is covered in Shopify system design interview preparation.

Product catalog and search indexing design is discussed in Google system design interview guide.

See also: Airbnb Interview Guide 2026: Search Systems, Trust and Safety, and Full-Stack Engineering

Scroll to Top