Magento 2 Product Attribute Indexing: EAV vs Flat Catalog Decision Guide

The decision

For Magento Open Source 2.4.x catalogs, the practical decision is how product attributes are read on the storefront: keep denormalized Flat Catalog tables or rely on the native Entity-Attribute-Value model with Elasticsearch/OpenSearch for search and listing.

EAV stands for Entity-Attribute-Value. Magento uses it to store product attributes in separate tables per data type, allowing new attributes without schema changes. Flat Catalog denormalizes those EAV rows into a single table per entity, such as catalog_product_flat_1, to reduce joins at read time.

Constraints that drive the decision are catalog size, frequency of attribute updates, and search requirements. High-frequency updates create indexer churn. Large catalogs with many custom attributes amplify EAV join cost. Frontend search and layered navigation must be served by a dedicated engine in modern versions.

Supported options compared

Trade-offs

Flat Catalog reduces per-request joins but adds a fragile denormalization step. If the catalog_product_flat indexer fails or is delayed, storefront data can be stale or missing. Enabling flat tables in 2.4+ is discouraged because core features assume Elasticsearch for catalog search and layered navigation.

EAV + Elasticsearch shifts query load from MySQL to a search engine. MySQL remains the source of truth for catalog_product_entity and the *_entity_* value tables. Elasticsearch handles full-text, sorting and filtering. This reduces database overhead during high-frequency attribute updates and avoids indexer conflicts that flat tables create.

Attribute sets control which attributes are attached to a product type. More attributes increase EAV table count and complexity. Custom attributes must be marked "Used in Product Listing" to be included in search and layered navigation indexes. Without that flag, the attribute is stored in EAV but not surfaced in Elasticsearch.

Limitations: Flat Catalog is deprecated for frontend queries in modern Magento. Disabling it without a working Elasticsearch cluster will degrade listing performance. EAV queries without proper indexing can cause N+1 query patterns in custom code.

Concrete validation

Verify flat catalog state

Check the admin configuration path Stores > Configuration > Catalog > Catalog > Storefront. The settings "Use Flat Catalog Product" and "Use Flat Catalog Category" should be disabled for 2.4+ storefronts that use Elasticsearch.

Expected check: the config values are set to No. If they are Yes, the system will attempt to build and read flat tables.

Confirm indexer health

Run indexer status from the Magento root directory with a user that has shell access and read permissions to the codebase.

bin/magento indexer:status

Expected check: catalog_product_price, catalog_product_attribute, catalog_category_product, and catalogsearch_fulltext show as "Valid" or "Ready". An "Invalid" state indicates pending reindex.

Risk: running reindex on a production database under load can increase MySQL load. Schedule during low traffic.

Confirm search engine configuration

Check Stores > Configuration > Catalog > Catalog Search. The Search Engine should be Elasticsearch 7.x or OpenSearch 1.x/2.x. Without this, disabling flat catalog leaves listings to fall back to EAV joins.

Data persistence check

Inspect that product data persists in EAV tables. The base table is catalog_product_entity. Attribute values live in catalog_product_entity_varchar, catalog_product_entity_int, etc., linked by attribute_id.

Expected check: rows exist for a sample product_id in catalog_product_entity and corresponding value tables. This confirms EAV is the source of truth.

Rollback note

If flat catalog was previously enabled and you disable it, rollback is to re-enable "Use Flat Catalog Product" and "Use Flat Catalog Category" in Stores > Configuration > Catalog > Catalog > Storefront, then run bin/magento indexer:reindex for catalog_product_flat. Re-enabling restores the denormalized read path but reintroduces indexer fragility.

Version assumption: guidance applies to Magento Open Source 2.4.x with Elasticsearch/OpenSearch. Behavior may differ on earlier 2.3 releases where flat catalog was the default optimization.