Although easy to overlook, a proper exhaust is critical for any and every project. Each build has its own needs, so it is important to note that one size does NOT fit all here. Piping diameter, shape, types of bends, types of collector, and even material all effect what your car sounds like, the noise level, how much power you make, and where you make it. Remember, bigger isn't always better! Sometimes exhaust gas velocity (not backpressure) is more important than peak flow numbers.
You'll find all sorts of articles and how-to's mentioning that "a little bit of backpressure is important, especially for N/A engines." This isn't entirely true. You don't really want any back pressure, as that backpressure is stopping the efficient flow of exhaust out the tail pipe. What is actually needed is the proper diameter piping, which influences the speed of which the exhaust is traveling. When you switch to a larger diameter pipe, you can get more flow out of that pipe and the cost of what speed that gas is flowing at. What this means in real world terms is that if you swap out your exhaust for a larger, high performance version, you'll be trading low end torque for more HP in the upper RPM range.
There's much more that goes into exhaust design than just that, but those are the basics. A proper exhaust will not drone during interstate/high speed cruising, will not sacrifice much low end grunt, and will help your engine breathe better, making your car sound better and more powerful!