The PSP itself is capable of reading up to 2TB when running any CFW (I think officially it's like 128GB or something?), so if you did get two 500GB cards that should theoretically work fine.
However, it's the adapter you get that will matter the most, as some adapters don't support the SDXC cards required to hit high capacities like that. Unfortunately there's no "guaranteed" adapter that will work, because at this point they're all random Chinese trash so it's impossible to say what you'll get. But you can follow this guide for general information on finding a proper Pro Duo adapter that will support larger cards:
https://wololo.net/talk/viewtopic.php?t=48418 <
It also includes tips on formatting and things like that as well.