Something MS might have alleviated if they'd just gone with 12GB of RAM and a 192-bit bus.
As games mature into the technology, it's likely this won't be a factor. A general lack of compute power will be the overall bottleneck here for Series S if you want to speak to scaling issues vs Series X in the long term.
You are unlikely to cap out the memory on Series S when it's working in 1080p with 8GB of memory in today's titles. Factoring in things like SFS and upscaling where your buffer sizes are reduced further, it's unlikely to see any 'footprint' issues on Series S. Bandwidth and compute will be the main limiting factors for it, but reducing resolution there will help immensely.
SFS won't be needed for Series S from a footprint perspective (as any regular texture streaming would likely suffice, and games just moving to relying on JIT SSD texture retrieval will be a large impact already) and if games take on upscaling algorithms in which it can create a fairly good 1080p image with reconstruction, than that system should be sufficient in keeping up with Series X.
The reality is that both PS5 and XSX will be targeting ~4Kish with or without reconstruction for the whole generation. You will never see either console releasing something at 1080p reconstruction, so this idea that developers will continually scale down to Series S level is fairly unlikely and not a reasonable edge case to discuss.
Series S will be a fairly good stop-gap for gamers who do not feel the need to move to 4K screens (and this is likely the last generation to do so), but PS5 and XSX are meant to support the mainstream movement into 4K. These consoles are not likely to drop out of their 1440p-4K range, and Series S is not likely to drop out of it's 720p-1440p range.
All the challenges you see with Series S today really just stems from engines that are still built around older base technologies.
You are welcome to blast Series S to the sky, but if history is any teacher here; Xbox One started with all games running at 720p. CoD Ghost and Battlefield 4. Many predicted the worst, but with Halo Infinite and Forza 5 releasing this year on XBO, the game looks stunningly better and the hardware hasn't changed at all frankly speaking. Both of these titles (and Gears 5) will likely be the best looking titles for the Xbox One era.