If you want to have fine grained manipulation of the interaction between the client and your server, mod_security is the way to go. It's a fine Open Source solution if you want to implement a "Web Application Firewall." (WAF)
If you do not want to spend the time properly configuring it and writing rules suited for your particular applications the benefit will be more limited.
As with other security solutions such as network and host based IDS, WAF are becoming more common. It looks like WAF have the potential to become a standard in the industry. Nevertheless, as with IDS, mod_security is only as good as the person responsible for it is willing to make it.
If you had an application you did not develop and were particularly concerned about, you could limit the potential risk via mod_security without modifying the application.