Are flat tables still a thing we need to worry about in Magento 2? I know there's UI where we can turn the flat tables on, but I'm unsure how well they've been tested, and if they're even needed anymore.

If flat tables are still a thing -- where/when do the attributes for flat tables get generated and/or regenerated. Does this only happen at reindexing? Or can other UI actions trigger a flat table generation? Does it happen at php bin/magento setup:di:compile? Other places?

Also -- how does Magento decide if it needs to make an attribute a flat attribute? Are all EAV attributes flattened? Or are there others which might not be?


Yes, flat tables are still a thing. You can enable flat tables for products and categories from stores->configuration->catalog->storefront. enter image description here

Why do we need flat tables?

They do offer significant performance boost for large catalog data. One of our client has only 5 categories and around 250 products so it doesn't really matter if you have flat on or off. It really mattered when we switched it on for another store that had 800 categories and 17000 products and 5 multistores. Performance boost becomes apparent when you start noticing what's happening in the database. Here's what the database looks like when you have multiple websites.

flat product tables for each store

same thing for categories

The point being joins become very expensive with increase in table size even with indexes. Flat tables REDUCE (not eliminate) JOINs.

When are they created or updated

Flat tables are regenerated when you do full reindex with bin/magento indexer:reindex. They are updated on product or category update. Only relevant rows are updated.

Flat tables are not generated on setup:di:compile

**Which attributes go into flat tables? (and some limitations) **

Short answer is all the attributes that are going to be used in product listing (listing + layered navigation). An attributes store front settings decide whether or not the attribute should be included in flat tables

enter image description here

All attributes are not flattened because of limitations I mention below.

Flat table limitation

Finally there is one limitation for these flat tables which is imposed by mysql. There are row size and column size limits on table. You can read more about it on mysql table limits

You will find this <max_index_count>64</max_index_count> interesting node in config.xml of module-catalog.

Hope that helps.


Based on magento documentation for flat table mentioned in https://docs.magento.com/m2/ce/user_guide/catalog/catalog-flat.html

Starting with Magento 2.3.0+, the use of a flat catalog is no longer a best practice and is not recommended. Continued use of this feature is known to cause performance degradation and other indexing issues

  • 1
    Lot of queries need to be rewritten :(
    – Rustyjim
    Nov 21 '19 at 16:35
  • If there are no flat tables why we need indexing at all ? Aug 3 '20 at 13:51

Flat tables not a thing anymore It doesn't add any performance improvements and would be deprecated in next releases

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.