a) Erratic max latency
IME Node.js performs well if you are not pushing its limits. If you have a service receiving 25k+ requests per second, you should better benchmark it, regardless of the language. Horizontal scaling should be configured based on the results of the benchmark.
b) Cognitive overhead
Yes, there is a bit of an additional overhead when mixing async and non async functions, but same goes for every other language that supports promises, C#, Python, Rust, Scala, etc.
Typescript should fail if you try to access a property/method of a promise, without resolving it, so I'd say it's not such a big deal.
Personally, I rarely work on Node.js, but when I do, I'm really grateful for await/async and typescript.
You know what's an even worse cognitive overhead? Languages without strong typing, like Elixir.
c) `pg` connect & query
I would say the issue lies with how the library is implemented. I'd definitely expect `query` to fail if it's not connected to the database.