Magento store owners with the large product catalog generally raise this question:
how many products can Magento actually manage? Today, we will explain the answer by providing practical guidelines.

• With and without extra optimizations, how many products different versions on Magento can be easily managed on an average server?
• What makes the difference between Magento versions in terms of catalog performance?
• How can Magento be scaled to handle even more products and what are the main bottlenecks for a big catalog?

What are the common mistakes that a person can make when handling a huge Magento catalog?

With and without extra optimizations, how many products different versions on Magento can be easily managed on an average server?

• Magento CE 1.9.x safely manages – 10,000 – 25,000 products in most cases, without much extra care. And on another hand, with more heavy system-wide scaling, resources tuning code optimization, it can manage 100,000 – 200,000 products.
• Magento EE 13.x. 14.x safely manages – 100,000 – 200,000 products in most cases, without much extra care. And on another hand, with proper scaling, optimization and server resources, it can manage 400,000 – 500,000 products.
• Magento 2 CE safely manages – 100,000 – 200,000 products in most cases, without much scaling and extra care, and with proper scaling, optimization and server resources, it can manage 400,000 – 500,000 products.
• Magento 2 EE is designed to be able to manage even more**, depending on some highly advanced enterprise features like database sharding, job ques, advanced MySQL and web server topologies and proper resources.

All these details give a rough idea, of course, it refers to a catalog with few categories and simple products. These reports and figures are based on Magento’s own performance and own experiences. The figures may vary greatly after a period of time depending upon the setup and software resources too.

And this brings to notice that, due to Magento’s database design, there are some especially massive aspects that act as an add-on to get the actual number of products Magento really works with.

Some of the most important elements that have a huge impact are:

• A number of Magento shops/languages.
• A number of product attributes.
• A number of categories and depth of the category tree.
• A number of configurable/bundle products.
• A number of customer groups with different product prices.

All this means that a Magento catalog with a few thousand products in a heavyweight catalog setup is concerned in a catalog management.

There are few features that make Magento really flexible, but nothing comes for free.

What makes the difference between Magento versions in terms of catalog performance?

Magento CE 1.x, including 1.9.x and

• Indexing, especially URL and search indexes are not optimized for large catalogs (this also applies to EE up to 1.12.x).
• Full Page Cache (FPC) is not available by default.
• Some frontend optimizations like javascript + CSS merging, CDN support.

Magento CE 1.x, including 1.9.x and

• FPC which speeds up catalog browsing and saves a lot of server resources is available.
• From version 1.13+.x incremental indexing is introduced whereby products that were changed or added will be re-indexed in the background by cron jobs.
• From version 1.13+.x full reindex processes are highly improved as well which work well even for large catalogs.

Magento 1.x EE

• FPC which speeds up catalog browsing and saves a lot of server resources is available.
• From version 1.13+.x incremental indexing is introduced whereby products that were changed or added will be re-indexed in the background by cron jobs.
• From version 1.13+.x full reindex processes are highly improved as well which work well even for large catalogs.
• Solr search engine is by default available.

Magento 2 CE

• Inherits incremental indexing feature from Magento EE 1.13+.x
• Inherits FPC from EE 1.13+.x and Varnish frontend cache is added as a choice of FPC. The load on the web nodes reduces, as the requests that are served by the varnish cache never need to reach the servers of Magento application, which improves the time of response.
• Browser cache is utilized for session data caching
• Checkout process is improved greatly
• Async order and product updates
• Client side optimizations like minification, js resources bundling, caching static content, image compression
• PHP 7 is supported by default. PHP 7 may have even 200% performance gain over PHP 5.x by itself.

Magento 2 EE

• Has all the features of Magento 2 CE
• Solr (2.0) and Elasticsearch (2.1) for search
• Database sharding separating catalog, checkout and order business domains is available
• Mysql cluster and Multi-master MySQL setup is supported
• Job queues introduced for advanced background data processing (deferred stock update as the first implementation)

How can Magento be scaled to handle even more products and what are the main bottlenecks for a big catalog?

• Hosting – a multi-node server setup may be needed, as big catalog obviously needs more resources, a VPS with plenty of memory and a multicore processor is a must.
• Server software – Nginx – As a web server and PHP 7 and Mysql 5.6 or equivalent (Percona/MariaDb) are highly recommended, even for Magento 1. Fine tuning of these pieces of server software for Magento is essential in this case.
• Product Import – optimized and tailored product import and update is one of the key elements in this case. Batch database updates enable tools which are most helpful. Any method or tool that uses single product updates is a huge potential bottleneck. Magmi is one of the preferred choices here.
• Some other rules of thumb:
  • Save only what has changed
  • Use dedicated resources for import processes
  • Separate price, stock, and basic product data import
  • Re-index only products and product data that need to be reindexed

Indexing

Indexing in Magento is a second step of saving product data and it is the trickiest part when having a huge catalog. It consists of a series of processes to copy product data from database tables optimized for data storage to tables optimized for different aspects of frontend data access. Since indexing is “only” needed for Magento frontend features, it is possible to separate indexing from product save or import. In the newer versions of Magento 1 EE and Magento 2 CE and EE to speed up working in the admin, incremental background indexing is introduced. but still may not be ideal for large volume product updates.

Indexing in Magento 1 CE is one of the greatest bottlenecks.

URL indexing can be bloated to millions of records as tends to provide the most issues, as it runs for a long time and in Magento 1 CE it is not optimized for big catalogs.

Beyond a certain catalog size and number of Magento stores, the increased overhead of product flat indexes will outweigh its benefits so it tends to be better not to turn on this feature for a big catalog.

Catalog search

Many resources need Default MYSQL full-text search for indexing and the actual search on the front end tends to decrease its accuracy and the relevance of the result set is fairly poor. So it is needful to replace, even in Magento 1. There are free alternatives for Magento 1 to replace MYSQL search with Solr, Elasticsearch or Sphinx. Magento 1 EE has Solr and Magento 2 from 2.1 on Elasticsearch. A 3rd party Elasticsearch and Solr 3rd extensions can replace the default MYSQL layered navigation engine at the same time, which can be a huge benefit.

Full Page Cache

Full Page Cache (FPC) is a mechanism whereby HTML pages generated by the server software are cached as a whole.

Next time the same web page is required, the cached version is returned without the need to regenerate the content. No FPC is there in Magento 1 CE and Magento code itself manages the Magento 1 EE caching. It’s too speedy and saves the lot of resources which makes it economical, by implementing caching in a layer in front of Magento is the best way to do FPC and there is no need to touch Magento at all when a cached content is served. This is achieved by Varnish caching in Magento 2. Though Magento 1 CE has no FPC and Varnish, there are good extensions to implement these features, too.

Application Cache

Magento heavily supports different types of configuration and application caches, Redis which is a memory based, scalable application cache is highly recommended with tag management. Magento starting from the latest version of Magento 1 CE creates an extension to handle redis to built.

To sum it up different versions of Magento

• Common bottlenecks are search and layered navigation on the front end and product import and indexing in the backend. There are other important factors like checkout and order management but these are more strongly related to the number of visitors and transactions.
• In many Magento versions, there are chances that batch products import is to be tailored and optimized. But still features like rich and optimized product import to be solved even in Magento 2 EE.
• Magento 1 Ce cannot handle big catalogs, but this can be solved through heavy scaling, proper hosting and 3rd party extensions that provide better indexing, search and caching, and it can be considered upscaled. above all, this indexing may still remain a bottleneck.
• To implement Varnish cache and fine tune to Magento 1 EE, the server architecture are the best candidates to scale it up in future, as it already has a bunch of optimizations in place.
• Just like Magento 1 EE, Magento 2 CE is also designed in a way that it should be prepared to serve middle-sized businesses also. But functionality wise, features like store credits, better CMS management etc., are still lacking in Magento 2 CE. But due to Varnish, the performance of Magento 2 CE is almost equal to Magento 1 EE. By using built-in optimization option, there is a way to scale it up through fine architecture and resource and element Elasticsearch or Solr for catalog search.
• To utilize the benefits of cloud computing, Magento 2 EE targets enterprises even beyond the middle size range and further aims to offer a highly scalable architecture.

What are the common mistakes that a person can make when handling a huge Magento catalog?

There are chances that you can come across additional mistakes while having a huge catalog.

Under scaling :

It is very important to build a live Magento store in such a way that it has plenty of system reserves. Performance tests throughout the development cycles are very useful so that you should know the limit of your system.

Low-quality 3rd party or custom extensions

Generally, 3rd party extensions are not meant for testing the large catalogs. Sometimes even a small oversight in the extension design can harm the performance.

Lack of proper monitoring

You need to monitor your system at a regular interval to avoid issues and bottlenecks.

Some technical insights

Data Storage in Magento

Magento is very complex and its features are virtually maxing out the limits of a PHP/MYSQL based system. Products are urged along with it comes the number of attribute scheme through prices, images, categories, product options to different product relations.

All these properties have different layers of values as many stores and languages used. Moreover, these aspects are potentially extended by a number of 3rd party extensions. Thus, it concludes that saving and updating products in the database is complex action.

Magento’s Frontend Data Access – Indexes

In Magento, indexing is a process where data is copied from the database tables optimized for data storage to tables optimized for frontend data access.

The structure of the database tables where product data is primarily saved is optimized for flexibility for data storage but as nature is complex in rational databases which are not optimized for different types of data retrieval at the same time. To reduce all this Magento has introduced Index tables which are known by the name of indexing.

You need some indexes for your stock Magento to work like URL, category/product relation, price and stock and attribute indexes and also search index. Apart from these, there are some optional indexes such as catalog flat and product indexes which flatten the EAV and multi-store data to dedicated store tables and single product rows.

Indexing in Magento comes with two faces first; it enables Magento’s most powerful features to work and optimizes data access and second, it makes it more complex, time-consuming and resource greedy to store product data in a usable way for the frontend.

Product Attributes Index :

It is just as layered navigation will work as it copies product/attribute options data to a table structure that is optimal for finding products based on the different attribute options which are already included. The number of attribute options is multiplied by the number of stores/languages Magento has.

Product Price Index :

By default, it is important so that sorting and filtering product prices work. Tier prices, configurable, bundle, and product complicate this calculation since the minimal price of complex product types depends on the price of the constituent products. The number of Magento websites and price groups complicates these factors.

Catalog URL Rewrites Index :

This index is essential so that SEO friendly URLs and redirection from old URLs to new ones work. The table is greatly affected by the depth and size of the category structure, the number of old URLs and stores/languages. The size of the table can go up to millions of records and later also it will keep on growing even in the case of a catalog with a few thousand products and few languages. As far as big catalogs are concerned Magento 1 CE and Magento 1 EE up to 12.x are facing the most problematic indexer.

Category Products Index :

Based on categories you have to optimize product filtering by storing catalog-product relations in a separate table. It is essential for the frontend.

Catalog Search Index :

For MYSQL based search you need this index. It merges option labels of individual products and the text of product attributes so that you can easily search in MYSQL full-text engine. Due to many stores, their many index rows are created for the single product.

Stock Status Index :

This index is used for calculating the fact whether the product is purchasable in Magento which can be governed by the mixture of some global, website and product level values and settings.

Product Flat Data Index :

This index is optional and was part of Magento in later stages of product listing/sorting and spare server resources. Its job is copied product attribute values which can be normally retrieved by huge queries of joining multiple tables into a flat structure with only one record per product and store.

Database Design

Magento EAV

EAV stands for Entity-Attribute-Value. Is a dynamic attribute management pattern which enables adding/removing/modifying product attributes like color, manufacturer etc., without changing the structure of the database tables. It is a very powerful and user-friendly feature and Magento has a number of configurational options for custom attributes out of the box.

Magento Multi-Store :

It is a unique Magento feature that a single Magento database can manage multiple stores in a way that attributes can have dedicated store level values that may override default values. This, again, is a highly user-friendly approach and makes it easy, for example, to create different language versions of the same catalog only by changing the description of the products and label of the product options.

Magento Indexing :

In Magento, Indexing is a process where data is copied from database tables and optimized for data storage to tables and then optimized for frontend data access. Different indexes are used for different types of data access. Indexing processes are built to speed up the shop and generally provide a huge benefit, in the case of full reindexing, which is sometimes inevitable, and any Magento shop should be prepared to be able to perform a full index rebuild, it may take a long time and require huge resources to reindex all product data.

Bottom Line

Magento has seen a great evolution since the beginning and now with Magento 2 which is not only the most feature rich open source online store system but also it is considered best in performance. If proper care and resourced are provided then huge catalogs can be managed easily and your business might go on increasing.