Prerendered cutscenes ate up the lion's share of the disc. If you sacrificed on those and used lots of compression, you absolutely could build an RPG on the N64 that would stand up to whatever you could do on PlayStation.

However, nobody wanted to do that. The only RPGs we got on the N64 were Quest 64 and Paper Mario. The reason why requires diving into the development history of what would have been a third RPG for the system: MOTHER 3. To put it bluntly, they ran out of storage space, and pivoted to the N64 DD to get more. The custom floppy disks the N64 DD took could hold 64MB[0]. Even then, this wasn't enough, so the game was then broken up into two parts: an N64 cartridge plus a DD expansion pack, which would give them 96MB in total to play with. MOTHER 3 would then continue in development hell before being canned and reborn as a Game Boy Advance title[1].

Fire Emblem was also supposed to release on the N64. However, unlike Itoi biting off more than he could chew with the N64, FE64 failed because the head designer, Shouzou Kaga, didn't even want to touch the hardware. He'd stalled for time by working on Satellaview FE games[2] on the SNES, and then left Nintendo entirely right before Thracia 776 came out. He'd then release a totally-not-Fire-Emblem clone on PlayStation and get sued into oblivion for... basically telling all the games press that he was making Fire Emblem on PlayStation.

So that's a 50% success rate for RPGs on the N64 - not a good look. Could homebrewers do better?

On prior Nintendo systems, cartridges executed in-place - that is, the CPU bus and game cartridges were wired up such that the game was effectively the system's boot ROM. This also meant that the CPU address bus width would limit the size of the game without further bankswitching. On the N64, however, cartridges load into memory through the PI bus, which has a separate address space from the main MIPS CPU. PI bus uses 32-bit addressing, and the address range allocated to the cartridge would allow up to 3.75GB ROMs. Data storage devices that large would never exist during the lifespan of the N64, but building an N64 cartridge that large would be practical today. I suspect loading the ROM image into emulators or flashcarts wouldn't be too difficult either.

And, of course, if you did manage to hook your N64 up to ridiculous amounts of storage, you could do crazy dynamic loading tricks like this[3]. If the N64 wasn't limited by storage space, RPGs would have actually looked better than on PlayStation, and render in real time to boot.

[0] Cartridges were later produced at this size, notably for the N64 port of Resident Evil 2

[1] This did not actually fix the problems with MOTHER 3. If anything, it made them worse, because GBA cartridges are limited to 16MB without bankswitching. The game used significant amounts of compression to deal with this, which complicated efforts to make an English translation. MOTHER 3 would also not release until a month after the DS Lite, which meant that an official English release of the game was very much out of the question.

[2] The Satellaview was a SNES hardware addon that allowed downloading games off of satellite radio services operated by St.GIGA. Think of it like Nintendo's equivalent to the SEGA Channel, except Japan-only because satellite radio didn't even exist in the US yet.

[3] https://www.youtube.com/watch?v=Sf036fO-ZUk