In my experience the sweet spot is often to just use a simple CPU side
bounding frustum test to cull objects that are obviously behind the
camera
If you target graphics cards with less filling power than the 9800 GTX or the Xbox, then you can do a lot better by doing better culling. If you can build a cell/portal system, for example, you can often get a lot of culling for very cheap. On my 64-bit laptop graphics, I have a level of "maze" canyons, and when I'm looking straight at a wall, with wall after wall behind it, the frame rate dips below 20. When I look in another direction, it's back up to 60.
Also, sorting opaque geometry near-to-far is a good idea, as you will get the benefit of early Z optimizations in fill rate.