NOR is semi-permanent storage. it takes longer to write to, but the game can then be run without ever writing it in there again unless you flash another game to NOR. NOR has double the capacity and double the SRAM allocated, so it is the only way to run some games. When turning on the DS, the 3-in-1 defaults to a GBA cartridge with the NOR game.
PSRAM on the other hand is volatile storage. It was originally used only for browser RAM and stuff like that, but Rudolph's tool (and some other new GBA loaders) make use of it as space for GBA games, as well. It is faster to write to and more suitable for playing many different games instead of primarily a single one. It however has the disadvantages that it is half the size of the NOR (but still fits most games) and that the allocated SRAM is bank 2 (and 3, but it can't be used for technical reasons), not banks 0 and 1. Since it does not use the default banks, it can't do bank switching, so it can only use one bank instead of two. but very few games use two banks.
