I recently read a great article exploring what would change if they were to switch from processes to threads for each connection. Running a connection pooler didn't seem so bad to me after reading it. https://medium.com/@tusharmalhotra_81114/why-postgresql-choo...
FWIW, there actually are some ongoing efforts towards that - including several preparatory changes in PG 18. Still lots more work, but we are working towards it.
Do you know, how well pgbouncer works with prepared statement nowadays? We slowly migrating our clusters to pg16/new bouncer and feel unsure on stability and reliability of prepared statements support
