Magento 2 Inventory Reservation Performance: Fixing the Silent Checkout Killer

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

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

-- 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;

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

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

<?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
            ]
        );
    }
}

<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>

<?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
        ]);
    }
}

<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>

<?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
            }
        });
    }
}

-- 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;