What should be the minimum size of SD card and class of Raspberry Pi for better performance of the right size.Is SD card just used to initially flash the board with the OS, or does it need to remain in the slot to store the OS indefinitely?
The minimum size could be as low as 512MB. In practical terms you're not likely to find anything less than 8GB for sale now, possibly even 16GB. All of the easy OS installations assume you're putting it all on the SD card at least initially.
If you're buying a new SD card then I suspect it'll be a class 10 device. If IO performance is an issue then you don't want to be using SD and should have an external storage device. I have run a Postgres DB off an SD card and it worked surprisingly well, if somewhat slow for more complex queries. Running a DB or anything that does a lot of write will also wear out the SD card very quickly. Actually that's another advantage for getting an oversize card is you have more "spare" cells for the wear levelling to use before the card dies.
You do need to boot from SD with the Pi (not sure about very latest version) and need to keep the card in permanently. But if you have external storage then you can put the rest of the OS on there with a bit of fiddling so once the OS has booted it won't use the SD any further. There are tutorials around on how to do this and it will be educational so definately have a go! Other options are to move frequently written directories like /var and /tmp (and swap!) to external storage which are a lot easier. Just make sure to include them in /etc/fstab referenced by UUID so they can always be found. Oh, and you'll need to use a powered USB hub to drive an external disk/ssd too - if SD card is too slow you'll find most USB sticks unsuitable as well.
In the same way that a typical computer needs a hard drive or SSD to hold the operating system, applications and all of your files ... the Raspberry Pi needs a storage device (there is no permanent storage on the Pi itself).
The microSD card is that storage device. You'll download operating systems and use a utility to write the OS image to the microSD card (such as 'Etcher' ... but these days it goes by the name 'balenaEtcher' (see: https://www.balena.io/etcher/ )
The card size is dependent on how much space you need for your operating system + apps + files. If you were to download Raspbian, the latest (non-lite) version is well over 4GB. You would want an 8GB card as a minimum. But since 16GB cards are cheap, I'd probably opt for at least a 16GB card (most of my own cards are 32GB cards, but these cards are running quite a few applications and a lot of data.)
In terms of performance, that'll depend on the card brand & model.
Here's a blog with some benchmarks: https://www.jeffgeerling.com/blog/2018/raspberry-pi-microsd-card-performance-comparison-2018