The key is to have sophisticated software to "filter" down the content into reasonable geometry (rather than the grossly inefficient geometry you'd get out of typical photogrammetry) and then into LOD levels and very optimized streaming representations for that geometry. Anyway this is why unreal engine 5 comes together with current-generation console runtime SSD streaming, it's to facilitate this sort of streaming large quantities of geometry for virtual environments.
Obviously your result is only gonna be as good as your source imagery, but a large part of the magic is in how precisely the photogrammetry is able to solve and compute correct camera poses so as not to inject artifacts, combined with the automated generation of efficient meshes.
It seems like traditional asset pipelines never really had a need for crazy optimized LOD generation. There have been sophisticated mesh optimization tools, but usually it's only a small fraction of the manual effort to make some LODs compared to modeling the actual full detail model. It's usually done manually, but I don't know for sure, never worked in the industry. But you can imagine how this is a non-starter for when your full detail model came out of photogrammetry. I haven't been following it that closely but my take on it is that Unreal Engine 5 is a paradigm shift in that it will liberate you from even needing to consider levels of detail, much the same as you don't consider releasing memory in a garbage collected programming language. This is clearly possible once those concerns you state, "input is an absolute mess", "having to recreate models", become automated and reaches a level of quality that equals what a human can do.
I would expect in unreal engine 5 you will be able to import assets in traditional 3D formats (in addition, of course, to a bundle of photos) and it will recommend you provide it with the highest possible quality input. And you'll just be able to straight away instantiate hundreds of thousands of copies of it into your scene stretching into infinity and your framerate will be fine.