The CPU speed is also limited but it's a different kind of challenge to try to make a game be so complicated that there's enough math to even do to challenge the available CPU allocation given how few permutations of screen space are available to try to alter.
Depending on your game, RAM limitation and program space limitation might also come into play.
But the artificial limitations are only one element to why the platform is popular, the other element is the tooling.
Much like using Unity, Unreal, Roblox, etc you have loads of primitives and coding features (and music and SFX creation suites) at your fingertips built right into the platform, and how the limitations help here is to cap how subtle and complicated a person's goals are liable to be in order to help refocus the developer's attention back onto the gameplay loop.
It was a lot of fun to try to squeeze a game into 4 kB but I can definitely also admit that part of it was being able to use the extreme limit as an excuse for making things simpler and not have to worry about the game being ugly or not having sounds or not having a title screen and all the other 90% of stuff that would have been expected to turn it into a complete game.
I play around a bit with TIC-80. Some say it is just an open source PICO-8 and I guess it is to some extent. I paid for both but I have not spent as much time with PICO-8. I like that TIC-80 supports quite many different programming languages, but maybe PICO-8 does as well these days? There is no super obvious reason to use one or the other, so I go with the one with free source code.
Real retro gamedev is tempting, but it is difficult to find a real console that were heavily restricted while also being convenient to develop for. The fantasy consoles cheat a bit in being restricted like some early 1980's hardware while running code like some 2010's game engine. It's like the best of both worlds, but also a bit boring compared to writing something actually limited to what some old console could do.
If it does, then it doesn't show. It feels as though most major game engines have completely given up on performance-as-a-default in favour of simplifying (sometimes marginally) art workflows, and the frame rendering breakdown articles/videos I consume now and then entail baffling, utterly ridiculous and unnecessary drawcall counts, unoptimized geometry, poorly optimized shaders that give marginal graphical improvements, huge overdraw, etc.
It's gotten bad enough that resolution upscaling is becoming commonplace because of how ridiculous the per-fragment rendering costs are getting. That has no right to happen in games without high-fidelity subsurface scattering, volumetric effects, indirect lighting, raytraced reflections, etc. And yet it does. This is something nobody resorted to even in the 90s.
I recommend videos from the YT channel Threat Interactive, where they break down a lot of the horrifying rendering performance regressions that have become the norm in recent times.
And honestly, I'm not very impressed by volumetric clouds when games like Assassin's Creed Unity from 2014 had enormous, sprawling open worlds with high-quality visuals comparable to much more recent games that struggle to run on hardware with several times the juice.
Very often, modern games look straight up worse for much higher resource cost because they stop looking better once you're forced to drop from maximum settings(and you are). There's blurry AA techniques, resolution upscaling(ew), or the fact that lowering texture resolution often degrades graphics way, way harder than in older titles using similar texture fidelity.
It is just that it is much more complex and much less obvious. Before, an advance could going from flat triangles to textures, using a clever maths trick. Now an advance is better shading in the corners of a room, using a clever maths trick. The difference is that people will be able to tell immediately how an improvement the textures are, but unless they are in the field, they won't be able to point out the shadows in the corner of the room. These shadows are not useless though, it adds up to better graphics overall, but it is not as impressive.
About the AAA gaming industry, the thing is that this is big business, these games are awfully expensive, financed by investors who want to see a return on their investment. Usually, it means that they study the market, see what sell, and do something along these lines. Not very original, but less risky.
The indie game industry is the opposite. There are thousands of studios, they can't beat AAA on content and polish, and they need to come out of the pack, so they need something else, like original ideas. But overcoming technical limitations is usually not the driving force anymore, as there are not that many limitations anymore. In fact, they usually underperform compared to AAA productions because it would be unaffordable otherwise.
I don't know much about the PICO-8 scene, how much of it is about overcoming the (artificial) technical limitations and how much is about using the platform "as intended" and focus on gameplay.