I'm wondering if there is any merit in using multiple spatial algorithms for visibility determination. What do more modern game engines do? Do they have "one system to rule them all" or various systems that specialize in different types of renderables/geometry present in the engine?

Most companies I know of do whatever they need to get it to work correctly. You need to balance as you see fit. An octree may be great for a giant scene, but may be useless for a particle emitter.

I would aim for supporting any type of visibility testing for any type of object if you're shooting for a robust engine. And if they all use the same system, then great.