You can build applications in any language that have many short-lived transactions in single connections or a few huge, blocking one, or anything in between.
The former case is a good one for PGBouncer (it can interleave transactions) and the latter isn’t (it can’t do much about your three minute long BEGIN…COMMIT). Neither has to do with the language.
This may change vaguely soon, but right now you typically scale python apps by starting multiple python processes, while for Java you can just add threads. Python processes can't share a thread pool among all of them, while Java can.
Sadly, I’m deeply familiar. What made Evan Phoenix’s Rubinius work so exciting in ~2012 was getting rid of the GIL and seeing what was “fixed” by parallelism and what was not.
You make a good point. I didn’t read the parent comment that way but you’re right about how you scale python with the GIL vs JVM.
For I/O, you can add threads and share the same connection object. Like any other languages, you are responsible for making it thread-safe.
There's also async. Many Python web apps spawn a thread per worker in your web server, not processes. Look into ASGI vs WSGI.
PHP allows persistent connections to postgres that will be reused by subsequent requests. But of course the connection isn't shared by concurrent requests (unless you use Swoole), so it doesn't solve the process-per-connection on postgres' side, it merely saves some overhead.