I had the same opinion, but it changed after playing Doom (2016, Xbox One) and Doom Eternal (2020, Xbox Series X) back to back. The difference was dramatic.

I still believe we are reaching a point where Moore’s law is hitting a fundamental limit, but only time will tell how things will pan out. My guess is that consoles will need to get bigger to get better.

Increased demand translates to increased memory and ram production capacity?

I think the problem might sort itself out once the data centers are actually built. The impact from maintaining a data center is probably less than from actually building one.