Normally it is not automatically deleted. However, ss soon as you delete it yourself, it won’t be possible to reinstall it. E.g. Relai, an app still unavailable in my country of residence, which I still have despite its removal from the App Store (long time). I could also transfer it to a new phone (thanks to backup, that is).