The flat catalog will reduce the load on your MySQL server by combining all the product/category tables into one big table instead of performing expensive table joins on every page load. Generally you will see greater benefit the more products/categories you have. One of the downsides is that each time you make a change to a product the flat catalog tables have to be re-indexed. There is also an edge case where the flat tables may exceed the MySQL row limit on stores with a large number of custom attributes, but this is hardly an issue for most users.
In most cases your store performance will benefit from the reduced query complexity so it is worth trying.
On a development site it is better to keep it turned off so that changes become visible immediately without having to do a re-index.
