in a perfect world where fast storage became cheap enough to do this, that would be amazing actually
SSD prices have shit the bed in the last few years. Yeah it's thanks to consoles finally catching up with PC but that widespread adoption has really helped!
Just before PS5/XSX came out I paid £390 for a 2TB SSD (pcie 3.0). I bought another one the other day, same make/brand/capacity except it was now pcie 4.0, and it only cost me £165. I actually bought the exact same one in December just there (returned it) and it cost £210. In the space of ~4 months the price went down by 22% and since I got my current 2TB drive the price has dropped ~58%.
If games are going to be <200GB then that obviously means the cost of an SSD will be much cheaper. A PS5 SSD runs at 5.5GB/s. The cheapest drive with more than 5.5GB/s that I can find is ~£60. So yeah that's still expensive but it's a 500GB drive. You can get a 250GB drive for £20 but it's only 3GB/s.
Assuming this generation lasts to around 2027/2028 then it's actually not too crazy to think we could see next gen consoles move to NVMe storage for physical games especially with those consoles increasing RAM capacity (likely 32-48GB). I'm just going off prices of retail units and Sony/MS could get drives for cheaper as they order in bulk. Of course, next gen games will increase in size. If Sony/Microsoft don't chase 8K (which they really shouldn't) then sizes shouldn't increase significantly. If they do try go for 8K then not only is it a complete waste of power but it would completely destroy physical media. If we're struggling to deal with 4K textures then 8K will be even worse.
Nintendo uses flash based storage with capacity up to 64GB although I think 32GB is the largest used and that's still much shorter than the size of PS5/XSX games. Both the Switch and Steam Deck can run games off a MicroSD card so there is some hope that next gen consoles would manage with NVMe storage.