Use a secret manager the moment more than one person or more than one machine needs the same credential, which is almost immediately in any real deployment. It is not optional for production systems handling user data, payments, or anything regulated by SOC 2, PCI DSS, or HIPAA, all of which expect access control and audit logs on credentials.
The cost is operational complexity and a new dependency in your critical path. If your application reads its database password from Vault at startup and Vault is down, your service cannot boot. Teams mitigate this with high availability clusters, caching, and sidecar agents that keep a recent copy in memory. There is also latency: an extra authenticated network round trip on cold start, which is why most apps fetch secrets once and cache them rather than per request.
A lighter approach for small teams is encrypted files committed to git, using SOPS or git-crypt. The secret stays encrypted in the repo and is decrypted at deploy time with a KMS key. This needs no running server, but it gives you no rotation, no audit trail, and no dynamic secrets, so most organizations graduate to a managed service as they scale.