I’ve been running a setup much like this for a year and a half now. I ended up buying a Samsung T5 2TB USB drive and plugging it into my RPi 4. Works amazingly, performance is ideal. And there’s even a way to boot from a USB SSD if you want to avoid SD card wear.

Why the T5 and not a higher tier SSD? Turns our the T7 and higher chips only benefit from speeds if you’ve got a thunderbolt port, consume a lot of extra power, and generate a ton of extra heat. The T5 will hopefully hold up better over time since it’s almost always cool to the touch. Performance has not been an issue.

Of course, you could also look into SBC with built-in PCIe ports and plug an SSD right in.