If CGI is dead, I had not noticed. :) I'll admit that I don't write CGI programs often, but I do still write them.
I think, though, that the database interacting, templating, etc., are things that you will very nearly always do in PHP because they're there. File-backed storage (if any storage is even needed) is more than adequate for most cases where one might write a CGI program in C. The last time I had occasion to write something from scratch and target CGI, I was generating graphs. No templating, no database, very fast.
The major pain of templating lies in C's string handling, though, and if you aren't worrying about freeing memory, there's not much pain. As far as HTTP goes, if the protocol was designed to be parsed in C, it's not often difficult to parse in C. But for all of those things, there's a library.
I can't comment specifically about whether or not mod_php would be faster (since I used PHP only briefly and several years ago) but I suspect very strongly that the overhead of firing up a small, statically linked C program beats it.
As I said previously, I don't think it's usually a good idea, but it's a more than viable tool and nice to keep in the box.