Stock reindex at scale without locking checkout

Part 6 of the MSI / Inventory series — the indexer is the checkout's shadow dependency.

Nobody pages the on-call engineer because "the indexer is slow." They page because checkout shows stale salability, search returns products that cannot be bought, or the database stalls under a full reindex at noon. At 100k SKUs times a handful of sources, the MSI stock index is the largest derived table most merchants never think about, and its rebuild competes directly with the reads checkout depends on. This post covers how to keep it fast: the right indexer mode, sane cron topology, and the reservation-table signal that warns you before salability queries crawl.

Realtime versus schedule: pick by write volume

Magento offers two indexer modes, and MSI punishes the wrong choice harder than catalog price index does.

Realtime (update on save) rebuilds affected salability rows on every product or source-item save. For a small catalog with manual admin edits, this is fine and keeps the storefront instantly consistent. For an ERP feed writing thousands of qty updates per hour, it is a self-inflicted denial of service: each save triggers invalidation plus a partial rebuild, lock contention climbs, and checkout reads queue behind indexer writes on the same tables.

Schedule (update by schedule) accumulates changed entity IDs in changelog tables (*_cl suffix) and rebuilds them in batches on cron. The storefront lags the feed by the cron interval, typically one minute, but write throughput stays flat and checkout reads are isolated from import spikes. Any catalog above roughly 50k SKUs, any store with an external inventory feed, and any multi-source setup should run the inventory indexer in schedule mode:

bin/magento indexer:set-mode schedule inventory
bin/magento indexer:set-mode schedule cataloginventory_stock
bin/magento indexer:show-mode

Verify the decision with numbers, not doctrine. Compare indexer_state updated timestamps against order placement rate during a feed run. If realtime mode shows indexer locks coinciding with checkout latency spikes, the experiment is over: switch to schedule.

Changelog-based partial reindex, not full on save

Schedule mode only helps if the changelog pipeline actually drains. Each scheduled indexer tracks its position in the changelog; bin/magento indexer:status shows the backlog per indexer. A healthy system shows small pending counts that return to zero every cycle. A sick one shows a monotonically growing backlog, which means the batch size is too small, the cron runs too rarely, or a previous full reindex is still holding locks.

Three practices keep partial reindex healthy. First, never trigger a full indexer:reindex inventory from application code or an import observer; let the changelog accumulate and drain on its own cadence. Reserve full reindex for deployments, stock topology changes, and recovery. Second, scope reindex dimensions where possible: if only one source's feed updated, the invalidation set is that source's SKUs, not the catalog. Third, run indexer:update-mview style partial updates on cron every minute and reserve the heavier full rebuild for an off-peak window.

When a full reindex is unavoidable, run it with parallel workers if the environment allows, and announce it the way you would announce a deployment: off-peak, monitored, with a rollback read on indexer state.

Isolate indexer cron from checkout pressure

Default cron topology runs everything through one consumer group on one schedule, which means a heavy inventory reindex steals MySQL connections and CPU from checkout at exactly the wrong moment. Split the load.

Put indexers on their own cron group with a dedicated schedule and, where the infrastructure allows, a separate database user or connection pool with a capped connection count. The exact mechanism varies by host, but the principle is constant: indexer queries are batch analytics wearing an OLTP costume, and they must not share an unbounded pool with sub-second checkout reads.

# Example: run inventory indexers on their own cadence
bin/magento cron:run --group=index

Monitor the split with SHOW PROCESSLIST snapshots during a reindex: if indexer threads dominate the connection list while frontend threads wait, the isolation is insufficient. Also watch search: MSI salability feeds layered navigation and search result availability, so a stalled inventory indexer surfaces as "in stock" products that fail at cart, which customers experience as a broken store.

Size the worker count to the catalog. One indexer worker serializing 100k SKUs through a single thread finishes sometime tomorrow. Parallel batches sized so each batch completes comfortably inside the cron interval keep the backlog near zero. Measure batch duration after any catalog doubling; indexer cost scales with SKUs times sources, not SKUs alone.

The reservation table is your early warning

The inventory_reservation table grows with every order placed and shrinks on shipment, invoicing compensation, or cleanup. MSI salability queries subtract open reservations from source qty, so a bloated reservation table slows every salability check and every source selection. It is the single best proxy for reindex-adjacent health.

Query it regularly:

SELECT COUNT(*) AS open_reservations,
       MIN(created_at) AS oldest_open
FROM inventory_reservation;

A count climbing week over week with an oldest-open date months in the past means compensations are not firing: shipments created outside Magento, custom order flows that skip the reservation API, or a cleanup cron that never ran. Magento ships a reservation cleanup path; ensure it runs and confirm orphaned rows for canceled or closed orders actually disappear. After cleanup, compare salability query latency before and after on a sampled SKU set. Teams are routinely surprised how much checkout latency was reservation bloat, not indexer speed.

Also monitor changelog table sizes (inventory_*_cl). A changelog table with millions of unprocessed rows is a reindex that will never catch up at its current cadence; truncate-and-rebuild after fixing the drain rate, off-peak, with the store in maintenance only if the topology changed underneath.

Pitfalls

• On-save reindex during bulk ERP imports. Thousands of saves each triggering partial rebuilds saturates the database. Switch inventory indexers to schedule mode before the feed runs, not after the incident.
• One indexer worker for a catalog that needs parallel batches. Serial reindex of a large SKU-times-source matrix never drains. Parallelize and verify batch duration fits inside the cron interval.
• Ignoring reservation table bloat until salability queries crawl. Open reservations accumulate silently from custom flows and external shipments. Monitor the count and oldest row; run cleanup on schedule.
• Full reindex at peak hours. A full inventory rebuild competes with checkout reads. Restrict full runs to off-peak windows; let changelog partials handle the day.
• Treating "indexer green" as "salability correct." Indexer status reports freshness, not correctness. Probe real parent and child SKUs on the storefront after topology changes, because a green indexer over a wrong stock assignment still sells nothing.

Closing

Stock reindex at scale is a scheduling problem disguised as a performance problem. Schedule mode with a draining changelog, indexer cron isolated from checkout connections, parallel batches sized to the catalog, and a watched reservation table will carry most stores past the million-row mark without drama. The alternative is reindex competing with revenue on the same database pool, which is a competition revenue always loses.

Next in this series:

magento2-msi-migration-legacy