Good reasons
Reach for it when there genuinely is only one of the thing and creating a second would be wrong, not merely wasteful: a connection pool, a metrics registry, a logging backend, an in-process cache, a hardware device handle. The tell is that two instances would disagree with each other.
Why senior engineers flinchIt's a global variable wearing a jacket. A static accessor can be called from anywhere, so any class can quietly acquire a dependency without it appearing in the constructor. Six months later nobody can tell what a class actually needs by reading its signature.
It makes tests share state. One test mutates the singleton, the next test reads it, and now your suite passes or fails depending on the order it runs in. Because the constructor is private, you usually cannot substitute a fake either.
The lifetime is the process, not the request. Anything you cache in a singleton lives until restart — which is exactly what you want for a connection pool and exactly what you don't want for anything user-specific.
The alternative worth knowing
Most modern codebases keep the one instance and throw away the static accessor: create the object once at startup and pass it in through constructors, or register it with a DI container as a singleton-scoped service. You still get one connection pool; you also get a class whose dependencies are visible in its signature and replaceable in a test. That is why services.AddSingleton<T>() and Spring's default singleton bean scope exist — same guarantee, none of the global.