If the N64 could be stretched this far with some ingenuity, I wonder how far one could get on hardware that's old, but somewhat modern like the PS3 or the PC hardware contemporary to it.
The main resource limitations you deal with are the main RAM banwidth and the TMEM size. The complexity comes from the division of work between the CPU and a coprocessor called the RSP, which is basically a stripped-down MIPS CPU core that has a SIMD unit and some scratch RAM. You can come up with cool ways to use the RSP, but if you eat up more of the main RAM bandwidth, you'll hurt performance.
The demo here is focused on working around the TMEM size limitation, but it looks like it also reduces the RAM bandwidth use by drawing in Z order rather than using the Z buffer... which is an overall solid approach for improving graphics performance on the N64.
The PS3 has some striking similarities to the N64, in that both consoles have coprocessors with SIMD units that operate on scratch RAM. Both consoles have a reputation for being difficult to program for. The PS3 takes things a bit farther in that the Cell SPEs can only access main memory through DMA. I can only imagine how hard it was to effectively use the SPEs.
Uncharted and The Last of Us is just continue what is tradition at this point :)