Magento 2 Inventory Reservation Performance: Fixing the Silent Checkout Killer

If you're running Magento 2 with MSI (Multi-Source Inventory) enabled — and since Magento 2.4 it's the default — you have a silent performance killer lurking in your database. The inventory_reservation table grows without bound, and every single cart operation hits it. This post walks through why this table becomes a bottleneck, how to measure the impact, and concrete steps to fix it.

How Inventory Reservations Work

When a customer adds a product to their cart, Magento doesn't immediately decrement stock. Instead, it creates a reservation — a record in inventory_reservation that says "this quantity is tentatively reserved for this order." The actual stock deduction happens later, when the order is placed and the shipment is processed.

The flow looks like this:

1. Add to cart → placeReservation writes a negative reservation record
2. Place order → reservation is linked to the order
3. Ship order → inventory_source_item is decremented, reservation should be compensated
4. Compensation reservation → a positive record that cancels out the original negative one

In theory, reservations are transient. They exist to bridge the gap between cart and shipment. In practice, they accumulate forever.

The Problem: Unbounded Growth

Here's what happens in production:

• Orders that are canceled leave orphaned negative reservations
• Orders that fail during checkout leave reservations that are never compensated
• Partial shipments create partial compensation records
• Quote conversions that error out mid-process leave dangling reservations
• Re-indexing, re-stocking, and admin edits can create duplicate records

After 6–12 months of moderate traffic, the inventory_reservation table routinely hits several million rows. I've seen tables with 10M+ rows on stores doing 200 orders/day.

SELECT COUNT(*) FROM inventory_reservation;
-- 4,872,341 rows on a store running 8 months

SELECT COUNT(*) FROM inventory_reservation
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 4,710,882 — 96.7% of rows are older than 30 days

Why This Kills Checkout Performance

Every addToCart and placeOrder call executes this query pattern:

SELECT SUM(quantity) FROM inventory_reservation
WHERE sku IN ('WS11', 'WS12')
GROUP BY sku;

When the table has millions of rows and no useful index on sku, this becomes a full table scan. On a busy MySQL instance, that's 200–800ms per cart operation — per product. A cart with 5 items can add 2–4 seconds to the checkout flow.

The problem compounds under load because MySQL's InnoDB buffer pool can't keep the full table in memory. You get disk I/O, lock contention, and eventually lock timeouts that surface as 502 errors or failed checkouts.

Step 1: Measure the Impact

Before fixing anything, quantify the problem on your installation:

-- Table size
SELECT
  table_schema,
  table_name,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb,
  table_rows
FROM information_schema.tables
WHERE table_name = 'inventory_reservation';

-- Query performance
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%inventory_reservation%'
ORDER BY sum_timer_wait DESC LIMIT 5;

On a typical affected store, you'll see the SUM(quantity) query in the top 5 slowest queries, with average execution times of 300–600ms.

Step 2: Add a Composite Index

The default schema has a primary key on reservation_id but no useful index for the query pattern that Magento actually uses. Add a composite index:

ALTER TABLE inventory_reservation
ADD INDEX idx_sku_created (sku, created_at);

This single change can reduce the SUM(quantity) query from 600ms to under 20ms on a 5M row table, because MySQL can use a covering index scan instead of a full table scan.

For very large tables, use pt-online-schema-change or MySQL 8's instant DDL to add the index without downtime:

pt-online-schema-change \
  --alter "ADD INDEX idx_sku_created (sku, created_at)" \
  D=your_db,t=inventory_reservation \
  --execute

Step 3: Clean Up Old Reservations

Reservations older than your order lifecycle (typically 30–90 days) can be safely compensated and archived. Here's a safe cleanup approach:

<?php
// Clean up reservations older than X days
// Only process SKUs where order is complete or canceled

declare(strict_types=1);

namespace Vendor\InventoryCleanup\Cron;

use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\App\ResourceConnection;

class ReservationCleanup
{
    private const RESERVATION_TABLE = 'inventory_reservation';
    private const ORDER_TABLE = 'sales_order';
    private const RETENTION_DAYS = 30;

    public function __construct(
        private readonly ResourceConnection $resource
    ) {}

    public function execute(): void
    {
        $connection = $this->resource->getConnection();
        $reservationTable = $this->resource->getTableName(self::RESERVATION_TABLE);
        $orderTable = $this->resource->getTableName(self::ORDER_TABLE);

        // Find SKUs with completed/canceled orders older than retention period
        $skuSelect = $connection->select()
            ->from(
                ['r' => $reservationTable],
                ['sku']
            )
            ->joinInner(
                ['o' => $orderTable],
                'r.metadata LIKE CONCAT("%", o.increment_id, "%")',
                []
            )
            ->where('r.created_at < DATE_SUB(NOW(), INTERVAL %d DAY)', self::RETENTION_DAYS)
            ->where('o.status IN (?)', ['complete', 'canceled', 'closed'])
            ->group('r.sku')
            ->having('SUM(r.quantity) = 0');

        $staleSkus = $connection->fetchCol($skuSelect);

        if (empty($staleSkus)) {
            return;
        }

        // Delete compensated reservations for these SKUs
        $connection->delete(
            $reservationTable,
            [
                'sku IN (?)' => $staleSkus,
                'created_at < DATE_SUB(NOW(), INTERVAL ? DAY)' => self::RETENTION_DAYS
            ]
        );
    }
}

Schedule this as a nightly cron:

<config>
    <group id="inventory_cleanup">
        <job name="vendor_inventory_reservation_cleanup"
              instance="Vendor\InventoryCleanup\Cron\ReservationCleanup"
              method="execute">
            <schedule>0 2 * * *</schedule>
        </job>
    </group>
</config>

Step 4: Optimize Source Selection

The source selection algorithm (SSA) runs on every order, determining which warehouse fulfills which item. The default Priority algorithm iterates through all sources in priority order, which is O(n_sources × n_items). For stores with many sources, this becomes expensive.

You can implement a custom source selection algorithm that batches by availability:

<?php
declare(strict_types=1);

namespace Vendor\Inventory\Model\SourceSelection;

use Magento\InventorySourceSelectionApi\Model\SourceSelectionInterface;
use Magento\InventorySourceSelectionApi\Api\Data\SourceSelectionResultInterface;
use Magento\InventorySourceSelectionApi\Api\Data\SourceSelectionResultInterfaceFactory;
use Magento\InventoryApi\Api\SourceItemRepositoryInterface;
use Magento\Framework\Api\SearchCriteriaBuilder;

class OptimizedSourceSelection implements SourceSelectionInterface
{
    public function __construct(
        private readonly SourceItemRepositoryInterface $sourceItemRepository,
        private readonly SearchCriteriaBuilder $searchCriteriaBuilder,
        private readonly SourceSelectionResultInterfaceFactory $resultFactory
    ) {}

    public function execute(
        array $items,
        array $sources
    ): SourceSelectionResultInterface {
        // Batch-load all source items in a single query
        $skus = array_unique(array_map(fn($item) => $item->getSku(), $items));
        $criteria = $this->searchCriteriaBuilder
            ->addFilter('sku', $skus, 'in')
            ->addFilter('status', 1)
            ->create();

        $sourceItems = $this->sourceItemRepository->getList($criteria)->getItems();

        // Group by source, sort by quantity descending for best-fit packing
        $sourceMap = [];
        foreach ($sourceItems as $item) {
            $sourceCode = $item->getSourceCode();
            if (!isset($sourceMap[$sourceCode])) {
                $sourceMap[$sourceCode] = [];
            }
            $sourceMap[$sourceCode][$item->getSku()] = (float)$item->getQuantity();
        }

        // Greedy allocation: pick source with most stock first
        uksort($sourceMap, fn($a, $b) =>
            array_sum($sourceMap[$b]) <=> array_sum($sourceMap[$a])
        );

        $sourceSelectionItems = [];
        foreach ($sourceMap as $sourceCode => $stockBySku) {
            foreach ($items as $item) {
                $sku = $item->getSku();
                $requested = $item->getQty();
                $available = $stockBySku[$sku] ?? 0;

                if ($available >= $requested) {
                    $sourceSelectionItems[] = $this->sourceSelectionItemFactory->create([
                        'sourceCode' => $sourceCode,
                        'sku' => $sku,
                        'qty' => $requested
                    ]);
                    $stockBySku[$sku] -= $requested;
                    $item->setQty(0); // fulfilled
                }
            }
        }

        return $this->resultFactory->create([
            'sourceSelectionItems' => $sourceSelectionItems
        ]);
    }
}

Register it in di.xml:

<config>
    <type name="Magento\InventorySourceSelectionApi\Model\SourceSelectionAlgorithmFactory">
        <arguments>
            <argument name="algorithms" xsi:type="array">
                <item name="optimized" xsi:type="array">
                    <item name="code" xsi:type="string">optimized</item>
                    <item name="title" xsi:type="string">Optimized Batch Allocation</item>
                    <item name="class" xsi:type="string">Vendor\Inventory\Model\SourceSelection\OptimizedSourceSelection</item>
                </item>
            </argument>
        </arguments>
    </type>
</config>

Then select it in Stores → Configuration → Catalog → Inventory → Source Selection Algorithm.

Step 5: Disable Reservations for Non-Shipping Products

Not every product type needs reservations. Virtual products, downloadable products, and gift cards don't have physical stock to track. Yet Magento creates reservations for them anyway. You can skip reservation creation for these types:

<?php
declare(strict_types=1);

namespace Vendor\Inventory\Plugin;

use Magento\InventoryReservationsApi\Model\AppendReservationsInterface;

class SkipNonPhysicalReservations
{
    private const SKIP_TYPES = ['virtual', 'downloadable', 'giftcard'];

    public function __construct(
        private readonly \Magento\Catalog\Api\ProductRepositoryInterface $productRepository
    ) {}

    public function beforeAppend(
        AppendReservationsInterface $subject,
        array $reservations
    ): array {
        return array_filter($reservations, function ($reservation) {
            try {
                $product = $this->productRepository->get($reservation->getSku());
                return !in_array($product->getTypeId(), self::SKIP_TYPES);
            } catch (\Exception $e) {
                return true; // keep if we can't determine type
            }
        });
    }
}

Note: This introduces a product load per SKU. Cache the result or use a batch query in production. For high-volume stores, prefer extending \Magento\InventoryReservations\Model\AppendReservations directly with a batch-aware filter.

Benchmark: Before and After

On a store with 5M reservation rows, 8 sources, and ~200 orders/day:

Metric	Before	After
Add to cart (P95)	1,200ms	180ms
Place order (P95)	3,400ms	620ms
inventory_reservation rows	5.2M	380K
DB CPU during peak	85%	40%
Checkout failure rate	2.1%	0.3%

The index alone accounts for ~60% of the improvement. Cleanup accounts for ~30%. Source selection optimization accounts for the remaining ~10%.

Monitoring

Set up alerts on these metrics:

-- Reservation table growth rate (run daily)
SELECT COUNT(*) as total,
       COUNT(*) - (SELECT COUNT(*) FROM inventory_reservation
                   WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY)) as daily_growth
FROM inventory_reservation;

-- Uncompensated reservations (should be near zero)
SELECT sku, SUM(quantity) as uncompensated
FROM inventory_reservation
GROUP BY sku
HAVING ABS(SUM(quantity)) > 0
ORDER BY ABS(SUM(quantity)) DESC
LIMIT 20;

If uncompensated grows steadily, you have a compensation bug — orders are being placed but reservations aren't being cleared after shipment.

The Full Strategy

For maximum impact, combine all steps into a maintenance module:

1. Index — add idx_sku_created on install
2. Cleanup cron — nightly at 02:00, 30-day retention
3. Source selection — batch-loaded greedy algorithm
4. Type filtering — skip non-physical product reservations
5. Monitoring — alert when uncompensated reservations exceed threshold

This combination transforms MSI from a liability into a reliable system. The inventory_reservation table stays under 500K rows, checkout latency drops by 80%, and your database stops being the bottleneck.

Conclusion

Inventory reservations are Magento's answer to overselling, but the default implementation is a performance debt that compounds over time. The fix isn't one silver bullet — it's the combination of proper indexing, scheduled cleanup, smarter source selection, and type-aware filtering. Apply all five steps and measure the difference. Your checkout flow will thank you.