Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

AdvancedAdvanced PostgreSQL

Theory: Connection Pooling (PgBouncer)

The query

SQL
-- Q: Why is a connection pooler like PgBouncer needed for PostgreSQL?
-- A: PostgreSQL uses a process-based architecture (forks a new OS process 
-- for every connection). This consumes significant RAM (~10MB+ per connection). 
-- Opening and closing connections is slow.
-- PgBouncer maintains a pool of active connections and multiplexes lightweight 
-- client connections over them, preventing memory exhaustion.
Tested against PostgreSQL 16

Note

“MySQL uses a thread-based architecture, making native connection pooling less critical than in PostgreSQL.