Magento 2 Large Catalog Performance: Scaling Beyond 100K Products

Running Magento 2 with tens of thousands of products is one thing. Crossing the 100K threshold — or pushing toward a million SKUs — is an entirely different ballgame. The architecture that hums along happily at 10K products starts showing cracks: category pages that take 5+ seconds, layered navigation that times out, indexers that run for hours, and an admin panel that's barely usable.

The good news? Magento 2 can absolutely handle catalogs of this size. It just needs the right configuration, the right extensions, and the right approach. Here's what actually matters.

The EAV Bottleneck: Your First Wall

Magento 2's EAV (Entity-Attribute-Value) data model is flexible but brutal at scale. Every product page load can generate dozens of JOIN queries against catalog_product_entity_* tables. At 10K products this is fine. At 200K, it becomes a database killer.

What to do:

First, evaluate which attributes genuinely need to be searchable, filterable, or visible in product listings. Every attribute flagged as "Filterable" or "Used in Product Listing" adds weight to the EAV queries. In a large catalog, only the attributes your customers actually use should be enabled.

Second, use the catalog_product_flat tables — but with caution. Flat tables denormalize EAV data into a single row per product, which can dramatically speed up category and listing queries. However, they come with trade-offs: longer reindex times, increased storage, and complexity with multi-store setups. For catalogs over 500K products, flat tables may actually hurt more than help — test aggressively.

Third, consider moving attribute-heavy lookups to Elasticsearch or OpenSearch. The search engine handles faceted navigation far more efficiently than MySQL EAV queries. This is the single biggest win for large catalogs.

Category Tree Navigation at Scale

Category pages are often the most visited pages on a Magento store. With a large catalog, rendering a category tree with thousands of products and deep nesting becomes expensive.

Key optimizations:

• Limit category depth. Deeply nested categories (5+ levels) generate recursive queries that don't scale. Flatten your tree where possible.
• Use full-page caching aggressively. Category pages are cacheable by default. Ensure your TTL is reasonable and that cache warming covers your top 100-200 categories.
• Anchor categories are expensive. An anchor category inherits products from all subcategories. With large catalogs, an anchor category near the root can represent 80% of your catalog. Use anchor sparingly.
• Consider pagination size. 24 products per page means more page loads and more queries. For B2B catalogs, 48-96 per page can reduce server load. Don't offer "Show All" — it will crash.

Layered Navigation: The Performance Killer

Layered navigation (filterable attributes on category pages) is often the #1 performance problem in large catalogs. The default Magento implementation queries the EAV index tables, which can be catastrophically slow with 200K+ products.

Solutions ranked by effectiveness:

1. Move to Elasticsearch/OpenSearch — The search engine handles aggregations natively, fast. This is non-negotiable for large catalogs.
2. Enable "Use in Search Results Layered Navigation" instead of "Use in Layered Navigation" for attributes that don't need to show on category pages.
3. Limit the number of filterable attributes. 10-15 is plenty. Every extra attribute is an additional aggregation query.
4. Price navigation is especially expensive. The dynamic price range calculation scans the entire price index. Consider using fixed price ranges instead.
5. Cache the layered navigation state with a dedicated Redis cache for catalog layer data.

Price Calculation and Tier Prices

Price calculations at scale involve:

• Base price, special price, tier prices, catalog rules, customer group prices, and tax rules
• All computed across potentially hundreds of thousands of products
• Usually during index operations but sometimes on-the-fly

For large catalogs:

• Avoid complex catalog price rules. Each rule is evaluated against every applicable product during indexing. A single "Buy X get Y" rule with conditions can triple index time.
• Prefer tier prices and customer group prices over catalog rules when possible. They're evaluated at the product level, not through rule logic.
• Use the catalog_product_price indexer in "Schedule" mode, never "Update by Schedule" — and monitor its runtime carefully.
• Consider splitting complex catalogs: base prices indexed normally, dynamic pricing handled through a custom module or PPC (Per-Price-Cache) extension.

Indexer Strategy for Large Catalogs

This is where most large-catalog Magento stores fail. Default indexer settings don't account for scale.

Critical rules:

• All indexers should be "Update by Schedule" — never "Update on Save" for catalogs over 50K products.
• Stagger indexer runs. Don't run all indexers simultaneously. Create a cron schedule that runs them sequentially.
• Monitor indexer table sizes. The catalog_product_index_price and catalogrule_product tables grow linearly with products × customer groups × websites. At 200K products × 5 websites × 10 customer groups, you're at 10M rows.
• Use the --batch-size option to control memory. Default batch is 1000; for very large catalogs, 500 may be safer to avoid memory spikes.
• Consider incremental indexing where possible. Some indexers support partial reindexing based on change log tables.

Search Engine Scaling

Elasticsearch or OpenSearch is mandatory for any large Magento catalog. But even search engines need tuning.

Tips for search at scale:

• Allocate enough heap. A 500K product catalog with 30 attributes indexed needs at least 8GB-16GB of JVM heap. Don't run on defaults.
• Optimize mapping. Only index attributes that are actually used in search, filters, or sorting. Each indexed attribute increases index size and query time.
• Use index sharding wisely. For a single Magento store, 3-5 shards per index is usually enough. Too many shards fragment the index and hurt performance.
• Refresh interval matters. The default 1-second refresh interval in ES writes continuously. For Magento, 30-60 seconds is usually fine and reduces I/O pressure.
• Consider a dedicated search cluster for very large catalogs (>1M products). Running ES on the same server as MySQL and PHP is a recipe for resource contention.

Database-Level Optimizations

Your MySQL configuration needs to change at scale:

• Increase innodb_buffer_pool_size to 70-80% of available RAM. For a 500K product catalog with complex EAV, the working set easily reaches 8-16GB.
• Enable innodb_parallel_read_threads (MySQL 8+) to parallelize EAV JOIN queries. This alone can cut category page load times by 30-40%.
• Partition large tables. The catalog_product_index_price and catalogrule_product tables are excellent candidates for MySQL partitioning by website or customer group.
• Monitor slow queries. Magento generates some inherently slow queries at scale. Use the MySQL slow query log to find them and consider query rewrites via plugins.
• Connection pooling becomes critical. At 200+ concurrent admin + frontend users, Magento's default connection handling creates too many connections. Use a connection pooler like ProxySQL or PHP-FPM's persistent connections.

Admin Panel Performance

A large catalog doesn't just affect customers — your team suffers too. The admin product grid with 200K products is painful.

Admin-specific optimizations:

• Limit the admin product grid — don't show all products by default. Set default pagination to 20 and disable "Show All" for grids.
• Use admin mass actions for bulk updates instead of the product grid. Direct grid editing generates individual AJAX saves.
• Create admin roles with scoped catalog access — if your team only manages a subset of products, give them filtered access.
• Consider a headless admin or dedicated PIM integration for very large catalogs.

Real-World Results

Here's what a properly tuned Magento 2 store with 350K SKUs achieved:

The key wasn't any single change — it was the combination of ES-powered navigation, sequential indexers, database tuning, and aggressive caching.

Summary Checklist

☐ Move all layered navigation to Elasticsearch/OpenSearch

☐ Set all indexers to "Update by Schedule"

☐ Stagger indexer cron jobs with different intervals

☐ Increase MySQL buffer pool to 70-80% of RAM

☐ Limit filterable attributes to 10-15

☐ Avoid complex catalog price rules

☐ Use tier prices and group prices instead

☐ Limit category depth (max 3-4 levels)

☐ Increase search engine heap allocation

☐ Tune ES/OpenSearch refresh interval to 30-60s

☐ Test flat tables vs EAV at your specific scale

☐ Monitor indexer times daily

☐ Restrict admin grid defaults

Large catalog performance isn't a single fix — it's a strategy. Start with the search engine migration, then work through the indexer and database layers. Each optimization compounds, and before you know it, that 300K catalog runs faster than your competitor's 10K one.