We can configure Flat Catalog for Category & Products from Admin -> Stores -> Configuration -> Catalog -> Catalog -> Storefront Tab.
If it improves the performance of Magento, then by default why it's value is No?
Is it required to turn up both (Category & Product) at the same time? Or I can make Category Yes & Product No.
It has the same concept as we have in Magento 1 or it different in Magento 2?
There are 2 data models in magento: eav (default) and flat. EAV is a very angile model it allows user to add custom attributes as many is he likes, but the problem is that it stores the data in many tables and in order to get the data your query will have many JOINs which is a bad performance. Flat model takes the attributes you have created with system created attributes and creates a multicolumn table. So in order to get the data you will have to perform just 1 simple SELECT query to db.
This is like a production mode. You define your a scheme in eav and then switch to flat.
Being in flat mode you can change or add attributes but you have to reindex (or it will happen by itself, depends on your configuration), which will create the flat table again while putting the store to a eav mode until it is done.
Regarding products and categories it does not depend on each other.
As of Magento 2.3, it is no longer best practice to use the Flat Catalog feature at all.
The primary justfication for this revolves around this official troubleshooting article that recognises ongoing performance issues. The official docs links to that article. Also, the Magento 2.3/2.4 docs now show an official stance on this:
Some tweets that are relevant, they shed light on this change when we first discovered this change in tact from the Magento team and still now contain some interesting thoughts:
Flat Catalog will help you to speed up your product collection instead of managing the catalog data in many tables by the Entity Attribute Value (EAV). When your store applies the Flat Catalog, the new tables are generated on the fly and store the necessary data related to your product or category. You can refer to this answer for further explanation
