PostgreSQL interview question · Most asked
Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?
Summary
PostgreSQL forks a backend process per connection, which costs memory and makes thousands of connections expensive (contention, context switching). PgBouncer lets many clients share a small number of server connections. Session mode assigns a server connection for the whole client session; transaction mode only for each transaction (the common choice); statement mode for each statement.
Longer Answer
Transaction mode gives the best multiplexing but breaks features that rely on session state: session-level advisory locks, SET without LOCAL, temporary tables across transactions, LISTEN, and (historically) named prepared statements, which PgBouncer 1.21+ can now track. A good server-side pool size is small, roughly a few times the number of CPU cores, not hundreds.
Serverless and autoscaled applications are the classic case: every function instance opens connections and exhausts max_connections without a pooler (or a managed proxy such as RDS Proxy).
Key Points
- Process per connection is expensive.
- PgBouncer session/transaction/statement modes.
- Transaction mode breaks session state.
- Small server pools.
More most-asked PostgreSQL questions
- How does MVCC work in PostgreSQL?
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- What does VACUUM do, and how does autovacuum work?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- How do you build a job queue on PostgreSQL with SELECT ... FOR UPDATE SKIP LOCKED?