Retail inventory systems need millisecond updates at POS while merchandising teams query hourly rollups. OceanBase’s columnar replica (optional) or mixed row/column storage lets both workloads share one cluster.

Workload profile

  • OLTP: 12k TPS on inventory_levels updates, p99 < 25ms.
  • OLAP: Hourly SUM(quantity) by SKU and store, scanning 48h of line items.

Architecture

Route OLTP through primary replicas. Enable column store projection on order_lines for analytical scans, or use a read-only replica tenant with relaxed freshness (30s).

Query pattern

SELECT sku, store_id, SUM(qty) AS sold
FROM order_lines
WHERE sold_at >= NOW() - INTERVAL 24 HOUR
GROUP BY sku, store_id;

Add a composite index on (sold_at, store_id, sku) for row-store fallback when column store is disabled.

Lessons learned

Batch analytical queries off peak or use resource groups to cap OLAP CPU at 30% during business hours.