Magento 2: Are Flat Tables Still a Thing?

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

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.

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

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