The situation
Kroger operates thousands of grocery stores across the United States. Their delivery platform does something genuinely hard: it shows each customer what's in stock at their specific local store, updated in near real-time as shelf inventory changes. The challenge is that 'near real-time' inventory data, combined with 'millions of users browsing simultaneously,' creates a database load problem that most retail platforms don't face. When we joined the project, catalog page latency was unacceptable under sustained concurrent load. The root cause was a combination of factors: unindexed joins across product, store, and inventory tables; inventory update writes that were acquiring broad table locks rather than row-level locks; and high-read catalog pages hitting the database directly without any caching layer. We addressed all three. A composite index strategy on the most common query patterns — product-by-category at a specific store — reduced query execution time dramatically. The inventory write pipeline was rewritten to use row-level locking, eliminating the contention that was causing read latency during bulk inventory syncs. Redis caching went in front of all catalog browse pages, with a TTL calibrated to match the inventory sync cadence so customers always see data that's accurate within the sync window.
What had to change
Catalog pages were slow under sustained concurrent load — the data showed unindexed joins across three large tables hitting full scans under load. Bulk inventory sync operations from stores were acquiring table-level locks, creating read latency spikes for all users during sync windows. Every peak period meant degraded performance.
What we changed
Three-part fix: composite indexes on all product×store×category query patterns; inventory pipeline rewritten with row-level locking to eliminate sync-window latency spikes; Redis caching on all catalog browse paths with TTL matched to the inventory sync cadence. The database now handles peak concurrent load without degradation, and inventory sync no longer impacts read performance.
What the client gained
- 01 Composite indexing on all catalog query hot paths
- 02 Row-level locking — sync writes don't block reads
- 03 Redis cache TTL matched to inventory sync cadence
- 04 Scales to 2,700+ stores with individual real-time stock
