I'm not sure what you mean by 'need'. Why do we need HD video? Higher resolutions opens up more creative possibilities (assuming the same amount of colors could be used).
Because you're talking about a system, a retro system, with a specific design scope when it comes to capabilities and limitations. If you want something with less limitations, or more capability, move to dev'ing on a system from a different generation. Unless you need that resolution on the SNES that overrides the limitations that it inherits; thus the question. Is it possible to use that res to make an RPG? Yes. Do the advantages of a higher res out weight the limitations? That's for you to decide. The resolution is pretty much there for brag rights, more than practicality, just like the 64x64 sprite mode of the SNES.
But I guess that settles that, if the vram is a bottleneck.VRAM is the least of your worries, if you not familiar with SNES homebrew/dev. SNES is not a beginner friendly system to learn to dev on (dev != hack).