Magento sales_order_grid: the staged 4GB shrink we ran

The 23:41 Loom from a Black Friday survivor

A Dutch agency we work with sent a Loom at 23:41 on a Tuesday after Black Friday. Their Magento 2.4.6 store had crawled to a halt in the admin. The order listing took 14 seconds to paint. Their hosting dashboard flagged the database at 11GB total, and one table was eating 4GB on its own: sales_order_grid.

The shop had been running since 2020. Roughly 380,000 orders across six years of trading. Black Friday added 9,200 more in 72 hours. Nothing had ever been pruned, archived, or rotated. The sales_order_grid index had simply grown, and the mview-driven update pattern had layered on enough fragmentation that the table on disk was almost double the logical row size.

The brief was simple. Cut the table. Keep every archived order accessible from the admin. Do not lose a single row. Do it inside a maintenance window between 02:00 and 04:00 Amsterdam time.

What sits inside sales_order_grid

The sales_order_grid table is not the source of truth for orders. The canonical data lives in sales_order, sales_order_item, and the rest of the sales_* family. sales_order_grid is a denormalised projection built so the admin order list can render fast without joining ten tables at once. It carries a flattened copy of the order header (number, status, customer name, billing and shipping name, totals), and it is repopulated by Magento's materialised view indexer layer.

That last detail matters. You can truncate sales_order_grid and Magento will rebuild it from sales_order. You will not lose a single archived order, because the orders are not stored there. The grid is a cache. A 4GB cache, but a cache.

The footgun is the mview state. If you truncate without telling mview, the next changelog event will try to incrementally update a table that no longer matches its expected state, and you end up with a partial rebuild that takes longer to diagnose than the truncate took to run.

The audit, before any DDL

The first thing we run on any oversized Magento database is a sizing pass against information_schema. It tells you whether the problem is data, indexes, or fragmentation, and that decides the procedure.

SELECT
  table_name,
  ROUND(data_length / 1024 / 1024)  AS data_mb,
  ROUND(index_length / 1024 / 1024) AS index_mb,
  ROUND(data_free   / 1024 / 1024)  AS free_mb,
  table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name LIKE 'sales_order_grid%'
ORDER BY data_length DESC;

The output on this client's database read roughly:

table_name              data_mb  index_mb  free_mb  table_rows
sales_order_grid        2840     980       310      389214
sales_order_grid_arch   0        0         0        0

Two takeaways. First, the row count (389k) was right in line with the order count, so no orphaned rows were inflating the table. Second, data_free at 310MB meant InnoDB was holding onto unused pages, which fragmentation compounds over years of UPDATE traffic. The bulk of the bloat (2.8GB on data, almost 1GB on indexes) came from years of accumulated rows on a denormalised table that had never been rebuilt.

Next, the mview state. Magento exposes this through one table:

SELECT view_id, status, updated, version_id
FROM mview_state
WHERE view_id LIKE 'sales_order%';

We wanted every sales_order_* view in idle before touching anything. If a view is in working, an indexer cron is mid-run, and any DDL on its target table will either deadlock or corrupt the changelog pointer. The MySQL docs on online DDL operations are explicit about this: TRUNCATE on an InnoDB table requires an exclusive metadata lock, and you do not want to fight a running indexer for it.

The staged shrink

With a 4GB table and a two-hour window, we did not want to OPTIMIZE TABLE in place. On InnoDB that rewrites the entire table while holding metadata locks, and on this workload the admin grid was being hit by a Klaviyo sync every 90 seconds and a Mirakl marketplace poller every minute. We needed something that could be aborted, audited, and resumed.

The plan, in order:

1. Put Magento into maintenance mode for the order admin area only.
2. Stop the indexer cron group cleanly.
3. Pause the Klaviyo and Mirakl integrations from their own dashboards.
4. Snapshot sales_order_grid to a backup table on the same server.
5. Truncate the live table.
6. Reset the mview changelog so the next reindex is a full rebuild, not an incremental one.
7. Run bin/magento indexer:reindex sales_order_grid.
8. Verify row counts and content against sales_order.

The maintenance flag is per-area, not the global bin/magento maintenance:enable. We dropped a one-line guard into app/etc/config.php that returned a 503 on /admin/sales_order routes only. The storefront kept serving orders into sales_order normally. New orders simply did not appear in the grid until we were done. That was acceptable because the shop's admin was empty at 02:00.

Stopping the indexer cron group is straightforward:

php bin/magento cron:remove --group=index
ps -ef | grep "cron:run" | grep -v grep

If any cron:run --group=index process is still alive after the remove, kill it by PID. Do not skip this. A mid-run indexer holding row locks on sales_order_grid while you are trying to truncate it is the worst-case ending.

The snapshot is one statement:

CREATE TABLE sales_order_grid_bk_20260603 LIKE sales_order_grid;
INSERT INTO sales_order_grid_bk_20260603 SELECT * FROM sales_order_grid;

That doubled the disk briefly to ~5GB, which we had budgeted for. The point of the snapshot is not the data itself (we can rebuild from sales_order at any time) but the audit trail. If a customer service rep flagged a missing row the next morning, we wanted to compare against a frozen copy, not a moving target.

Then the truncate:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE sales_order_grid;
SET FOREIGN_KEY_CHECKS = 1;

And the mview reset, which is the step most teardown guides skip:

UPDATE mview_state
SET status = 'idle', version_id = NULL
WHERE view_id IN (
  'sales_order_grid',
  'sales_order_invoice_grid',
  'sales_order_shipment_grid',
  'sales_order_creditmemo_grid'
);
TRUNCATE TABLE sales_order_grid_cl;

Setting version_id = NULL tells Magento to treat the next indexer run as a full rebuild instead of an incremental pickup. Truncating the changelog table (sales_order_grid_cl) removes stale change records that would otherwise queue up against a now-empty target.

Reindex and verify

With the table empty and mview reset, the reindex is one command:

php bin/magento indexer:reindex sales_order_grid

On this database it took 11 minutes 40 seconds to rebuild 389,214 rows. We watched it from a second SSH session running:

SELECT COUNT(*) FROM sales_order_grid;

every 30 seconds, just to see the count climb. When it stopped at exactly the row count of sales_order, we ran the audit query again.

table_name              data_mb  index_mb  free_mb  table_rows
sales_order_grid        612      188       0        389214

From 3.8GB combined down to 800MB. The rest of the savings came from a freshly built clustered index with no accumulated fragmentation, plus the JSON items column being re-encoded compactly instead of carrying years of UPDATE deltas.

The verification step is non-negotiable. A row count match is not enough on its own, because a partial rebuild can still hit the right total if the indexer ran out of order. We also ran an explicit anti-join:

SELECT
  (SELECT COUNT(*) FROM sales_order)      AS orders,
  (SELECT COUNT(*) FROM sales_order_grid) AS grid,
  (SELECT COUNT(*) FROM sales_order o
   WHERE NOT EXISTS (
     SELECT 1 FROM sales_order_grid g
     WHERE g.entity_id = o.entity_id
   ))                                     AS missing_from_grid;

The missing_from_grid value came back at zero. Every archived order from 2020 onwards still rendered in the admin grid. The 14-second list view dropped to under 800ms because the InnoDB buffer pool no longer thrashed on a fragmented index. We kept the snapshot table in place for two weeks, then dropped it once the CS team had a quiet Monday.

Last step: re-enable the cron group, flush caches, and unpause the integrations.

php bin/magento cron:install
php bin/magento cache:flush

What we left in place

Three things we deliberately did not change.

We did not turn on additional InnoDB compression. On this database it was already enabled. More compression would have shaved more, at the cost of CPU on every order write, and the agency's primary complaint was admin latency, not storage cost.

We did not migrate orders to the archive module. Magento has a built-in order archive feature that moves closed orders out of the main tables. It is a fine policy for shops with millions of orders, but it changes the admin UX and the client wanted parity with what their CS team already knew.

We did not touch the storefront. The shrink ran entirely against the grid layer. Storefront performance was a separate engagement.

What to watch in the week after

For seven days after the shrink we kept a low-frequency check on three signals. Table size, mview lag, and admin latency. The table-size delta is the same information_schema query on a cron, dumped to a CSV in /var/log/. Watching it climb back over a week gives you a real growth rate per order, which is the number you actually need when you plan the next maintenance window.

Mview lag is the row count of sales_order_grid_cl at any moment. On a healthy shop it should sit in the low hundreds, drained by the indexer cron every minute. If it climbs into the tens of thousands, you have an indexer not running, or running but not finishing, and the next shrink will be uglier than this one.

Admin latency was measured with the same Lighthouse run the agency had been using on the storefront, pointed at /admin/sales_order. They added it to their internal dashboard. The bar is under 1.5 seconds for the first paint of the order list. The day they see it climb back over three seconds, they reach for the audit query, not the maintenance window.

The smallest thing you can do today

Run the audit query at the top of this post against your own database. Five seconds of SQL tells you whether sales_order_grid is a problem on your shop or a non-issue. Most Magento shops we audit hit 500MB to 2GB on this table. A 4GB grid is the kind of thing that does not announce itself until the admin grinds, and by then your maintenance window is shorter than you would like it to be.

When we built Pier we ran into this exact pattern on enough legacy sites that the MySQL editor ships with a one-click table-sizes view that runs the same query against any connected database. Every destructive step is wrapped in version history, so the snapshot table and the truncate live in the same audit trail as the rest of the work.