>niche
Literally all companies I've worked for a know about use Vault for storing secrets to be used during deployment.
It would be interesting to learn that this is different elsewhere.
Yes, we always use the vault/secrets infrastructure of the respective cloud vendor.
Never used the product Vault.
If in Cloud, then go Cloud native. No reason to run Vault. But on prem, there are limited options and Vault fits the bill.
If you want auto unseal with HSM and don't have to worry about the unseal key shards, then you can hook it up the HSM. Of course, HSMs are expensive and you also have to buy a Vault license.