Magento 2 Database Table Partitioning & Archiving Strategies

Every Magento 2 store eventually hits the same wall: the database keeps growing, queries keep slowing down, and nobody notices until checkout times spike or admin grids become unusable. The sales_order table has 5 million rows. The quote table has 12 million. The log_visitor table doesn't even bear thinking about.

This guide covers practical strategies for partitioning, archiving, and maintaining Magento's largest tables — so your store stays fast without losing historical data.

Which Tables Cause the Most Pain?

Not all tables grow equally. These are the usual suspects:

Transactional Tables

• quote / quote_item — Every cart session creates rows. Guest carts never clean up by default. At 10k daily visitors with 70% abandonment, you're adding ~7,000 abandoned quotes per day.
• sales_order / sales_order_item — Grows linearly with sales. A store doing 500 orders/day hits 1.8M rows/year in sales_order alone, plus 3-5x that in sales_order_item.
• sales_invoice / sales_invoice_item / sales_creditmemo / sales_creditmemo_item — Same growth pattern, sometimes with additional rows per order.

Log Tables

• log_visitor / log_visitor_info / log_url / log_url_info — Magento's built-in visitor logging. Creates multiple rows per page view. On a busy store, these tables can grow by 100k+ rows per day.
• report_event / report_viewed_product_index — Reporting tables that accumulate indefinitely.

Search Tables

• search_query — Every unique search term gets logged. Popular stores accumulate hundreds of thousands of rows.
• catalogsearch_fulltext — The full-text search index can become massive with large catalogs.

Strategy 1: Clean Up What You Don't Need

Before partitioning anything, remove data you'll never use.

Purge Old Visitor Logs

Magento's visitor logs are rarely useful after 30 days. Add a cron job or manual cleanup:

-- Remove visitor data older than 30 days
DELETE FROM log_visitor WHERE visit_last_activity_at < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 10000;
DELETE FROM log_visitor_info WHERE visitor_id NOT IN (SELECT visitor_id FROM log_visitor);
DELETE FROM log_url WHERE visitor_id NOT IN (SELECT visitor_id FROM log_visitor);
DELETE FROM log_url_info WHERE url_id NOT IN (SELECT url_id FROM log_url);

Important: Always use LIMIT with deletes on large tables. A single DELETE on 10M rows locks the table for minutes and fills the InnoDB buffer pool with undo data. Run the delete in a loop or use a batched approach.

Clean Up Orphaned Quotes

Guest quotes accumulate fast. Magento has a built-in cleanup, but it's often not aggressive enough:

-- Remove guest quotes older than 7 days (they're never coming back)
DELETE q, qi FROM quote q
INNER JOIN quote_item qi ON qi.quote_id = q.entity_id
WHERE q.customer_id = 0
AND q.updated_at < DATE_SUB(NOW(), INTERVAL 7 DAY)
LIMIT 5000;

Magento's sales/cleanup/quotes cron handles this, but check its configuration in Stores > Configuration > Sales > Checkout > Shopping Cart. Set "Quote Lifetime (days)" to something reasonable like 7-14 days.

Truncate Search Query Table

If you're not using search analytics, truncate it:

TRUNCATE TABLE search_query;

If you need some history, keep the last 90 days:

DELETE FROM search_query WHERE updated_at < DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 10000;

Strategy 2: Archive Old Orders

Archiving moves completed orders from the main tables to archive tables, keeping the active tables small and fast.

Magento's Built-In Archive

Magento provides order archiving through the admin: Sales > Orders > Archive. You can also trigger it via CLI:

bin/magento sales:order:archive --date=2025-12-31

This moves orders placed before the given date to archive tables (sales_order_archive, etc.). The archive tables have identical structure, so reports and lookups still work — they just query a different table.

Custom Archiving with Partition Swap

For more control, create your own archive tables and use partition swapping:

-- 1. Create archive table matching structure
CREATE TABLE sales_order_archive LIKE sales_order;

-- 2. Move old orders in batches
INSERT INTO sales_order_archive
SELECT * FROM sales_order
WHERE created_at < '2025-01-01'
AND entity_id BETWEEN 1 AND 100000;

-- 3. Delete from main table (same batch)
DELETE FROM sales_order
WHERE created_at < '2025-01-01'
AND entity_id BETWEEN 1 AND 100000;

Why batch by entity_id? A single DELETE with a date filter on a 10M row table creates a massive transaction. Batching by primary key range keeps each transaction small and avoids long-running locks.

Reindex After Archiving

After removing large amounts of data, rebuild affected indexers:

bin/magento indexer:reindex salesrule_rule catalogsearch_fulltext

Archived orders still need index entries if you're displaying historical data, but the main order tables will reindex much faster with fewer rows.

Strategy 3: Table Partitioning

MySQL partitioning splits a single logical table into multiple physical segments. Queries that include the partition key only scan relevant partitions, dramatically improving performance.

Range Partitioning by Date

The most effective partitioning strategy for Magento's order tables:

ALTER TABLE sales_order
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404),
    -- ... continue for each month
    PARTITION p202607 VALUES LESS THAN (202608),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Key rule: The partition key must be part of every unique index. Since sales_order has a primary key on entity_id, you need to drop and recreate it:

ALTER TABLE sales_order DROP PRIMARY KEY, ADD PRIMARY KEY (entity_id, created_at);

This is why partitioning is easier to set up when you create the table from scratch — retrofitting on production requires downtime.

Hash Partitioning for Quote Tables

Quotes don't have a natural date range that queries use. Hash partitioning by customer_id or entity_id distributes data evenly:

ALTER TABLE quote
PARTITION BY HASH (entity_id)
PARTITIONS 32;

This improves concurrent access patterns — different customer sessions hit different partitions.

Partition Maintenance

Add new partitions monthly and drop old ones:

-- Add next month
ALTER TABLE sales_order REORGANIZE PARTITION p_future INTO (
    PARTITION p202608 VALUES LESS THAN (202609),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Drop archived month
ALTER TABLE sales_order DROP PARTITION p202401;

Automate this with a monthly cron job. Dropping a partition is instant — it doesn't generate undo logs or lock the table.

What Magento Queries Benefit?

Not all queries automatically use partitions. The query must include the partition key in its WHERE clause. Magento's order grid queries typically filter by date, so they benefit directly:

-- This query hits only 1 partition instead of scanning the whole table
SELECT * FROM sales_order
WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01';

But queries that filter only by entity_id or increment_id will still scan all partitions. For those, add a covering index that includes created_at.

Strategy 4: External Archiving for Reporting

If you need years of order history for reporting but don't want it polluting the production database, export it:

Export to a Reporting Database

# Dump old orders to a separate database
mysqldump --where="created_at < '2025-01-01'" magento sales_order sales_order_item | \
mysql magento_archive

Then truncate or partition the production tables. Your analytics team queries the archive database; your production database stays lean.

Elasticsearch for Historical Search

If customers need to search their order history, index historical orders in Elasticsearch. Magento's search infrastructure can query ES much faster than scanning a massive sales_order table for a customer's old orders.

Monitoring Table Growth

Set up alerts before tables become a problem:

-- Check table sizes
SELECT
    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_schema = 'magento'
AND table_name IN ('quote', 'quote_item', 'sales_order', 'sales_order_item',
    'log_visitor', 'log_url', 'search_query')
ORDER BY data_length DESC;

Set a cron to run this daily and alert you when any table exceeds a threshold (e.g., 1GB data or 5M rows).

Recommended Cleanup Schedule

Table	Retention	Method
log_visitor*	30 days	Batch delete
log_url*	30 days	Batch delete
quote (guest)	7 days	Magento config + batch delete
quote (customer)	30 days	Magento config
search_query	90 days	Batch delete
report_event	90 days	Batch delete
sales_order	2+ years	Archive + partition drop

The Bottom Line

A Magento database that isn't maintained will eventually bring your store to its knees. The combination of regular cleanup, partitioning for large tables, and archiving for historical data keeps query times low, indexers fast, and admin grids responsive.

Don't wait until checkout takes 10 seconds. Set up monitoring, implement a cleanup schedule, and partition your largest tables before they become an emergency.

The upfront investment is a few hours of setup and a monthly cron job. The payoff is a store that stays fast regardless of how many orders you've processed or how many abandoned carts are sitting in your database.