Attribute design for a 500k-SKU catalog

Part 1 of the Catalog at Scale series — attribute sets decide index size before a single product is saved.

A 500k-SKU catalog does not fail at 500k products. It fails at 40 attributes, when every product save takes minutes, every product collection joins a dozen value tables, and the flat index never finishes. Attribute design is the leverage point: get it right early and everything downstream, from indexing to search to imports, stays manageable.

If you have not read it yet, start with Magento 2 EAV and database for the groundwork on eav_attribute, catalog_product_entity, and why values live in typed tables. This post builds on that model and turns it into design rules for catalogs that are two orders of magnitude larger than a demo store.

Backend types are a storage decision, not a label

Every attribute declares a backend_type: varchar, int, decimal, text, datetime, or static. That single column in eav_attribute decides which physical table holds every value: catalog_product_entity_varchar, catalog_product_entity_int, catalog_product_entity_decimal, catalog_product_entity_text, or catalog_product_entity_datetime. static attributes live directly on catalog_product_entity as real columns, which is why sku, type_id, and created_at are cheap to filter.

The mistake at scale is treating text as the default. A text backend type cannot be indexed the way varchar or int can, and its values land in catalog_product_entity_text alongside genuinely long content like descriptions. A merchant-facing "material code" stored as text because "it might get long" forces full scans where a varchar(255) would have used an index. Rule of thumb: varchar for short searchable strings, int for option selects and flags, decimal for measurements, text only for human-readable long content that is never used in layered navigation or sorting.

Source-model attributes deserve the same scrutiny. A select attribute with 2,000 options backed by eav_attribute_option_value is a taxonomy problem, not a dropdown. At 500k SKUs, every option value multiplies the rows the indexer and the search engine must resolve. Prefer a normalized external reference or a text facet over a giant option list when the values are identifiers rather than shopper-facing choices.

Attribute sets bound the blast radius

Attribute sets group attributes into families: apparel gets size and color, electronics get wattage and voltage. Technically they are rows in eav_attribute_set joined through eav_entity_attribute, but operationally they are the single biggest control on index width.

Each attribute that is marked used_in_product_listing, used_for_sort_by, or filterable in layered navigation adds work to every product in every set that contains it. An attribute that belongs to one family but is added to the default set "just in case" gets indexed for all 500k products instead of 30k. Audit each set separately: list its attributes, flag which ones participate in listing, sorting, filtering, or search, and remove the rest from the storefront-facing flags even if they stay visible on the product page.

Keep the number of sets small and deliberate. Five to fifteen sets cover most large catalogs. Fifty sets usually means sets are being used as categories, which they are not. When sets proliferate, import mappings, ERP integrations, and indexer debugging all slow down because every code path must branch per set.

The flags that actually cost money

Four attribute properties dominate performance at scale:

• is_required: required attributes must be present on every import row. A required attribute with a slow backend or source model blocks the whole import pipeline.
• is_unique: unique attributes get a uniqueness check per row. Only sku should normally carry this at 500k SKUs.
• is_searchable / is_filterable: each searchable or filterable attribute adds an OpenSearch field and layered-navigation aggregation. Ten filterable attributes over 500k products is fine; sixty is a search-cluster sizing event.
• used_in_product_listing: this flag pulls the attribute into product collections and the flat/index tables. It is the most over-enabled flag in large catalogs. If an attribute is only needed on the product detail page, leave this off.

A practical audit query lists attributes by backend type with their storefront flags, ordered by how many products carry values. The attributes at the top of that list with text backends and listing flags enabled are your first refactor targets.

EAV joins, flat tables, and the read path

Loading one product by ID is cheap. Loading a category page of fifty products with twenty listing attributes is fifty times a join across catalog_product_entity_varchar, _int, _decimal, and friends. Magento answers this with index tables: catalog_product_flat variants and the price and stock index tables that the storefront actually reads.

The design implication is direct: every attribute you flag for listing or filtering widens those index tables and lengthens every full reindex. Measure index width, not just attribute count. A catalog with 120 attributes where only 25 are listing-enabled will reindex dramatically faster than one with 80 attributes where 60 are listing-enabled. When merchants ask for "one more filter," show them the reindex delta, not just the frontend mockup.

Import-friendly attribute discipline

At 500k SKUs, attributes are also an import contract. Stable attribute codes, documented backend types, and frozen option values let the ERP team map once instead of per feed. Renaming an attribute code breaks every mapping, every saved import profile, and every third-party feed that references it. Treat attribute codes like API fields: versioned, documented, never renamed casually.

Separate merchant-editable attributes from system-fed attributes. Price, stock, and ERP identifiers should be locked against manual edits that the next nightly import will overwrite anyway. Use per-attribute scope deliberately: global scope for identifiers and technical flags, store-view scope only for genuinely localized content like names and descriptions. Every store-view-scoped attribute multiplies value rows by the number of views.

A checklist before you grow past 100k SKUs

• Backend types reviewed: no text where varchar or int fits.
• Attribute sets consolidated: each set maps to a real product family.
• Listing, sorting, filtering, and search flags enabled only where the storefront uses them.
• Giant option lists replaced or externalized.
• Attribute codes frozen and documented as integration contracts.
• Full reindex timed on a production-shaped dataset, not a ten-product staging copy.

Attribute design is unglamorous work, and it is the cheapest performance project a large catalog will ever do. The next post moves from storage to pricing: where catalog and cart price rules actually spend indexer time.

Next in this series:

Continue with magento2-catalog-price-rule-indexing for catalog and cart price rules and where the indexer really spends its time.