Domain 2 of 4 · Chapter 12 of 12

Optimize PostgreSQL connections for throughput and latency

Unlock the complete study guide + 1,040 practice questions across 16 full exams.

Bundled into the existing Developing AI Cloud Solutions on Azure premium course — no separate purchase.

14-day money-back guarantee — no questions asked.

Included in this chapter:

  • Two budgets: server processes and round trips
  • What one connection costs, and where it runs out
  • Sizing the client-side pool
  • Running the built-in PgBouncer
  • Choosing a pooling mode
  • Fewer round trips: distance, pipelining, COPY
  • Keeping a pooled connection alive
  • Exam-pattern recognition

PgBouncer pooling modes: when the server connection goes back to the pool

BehaviorSession poolingTransaction poolingStatement pooling
Server connection returnedWhen the client disconnectsAt the end of each transactionAfter each individual statement
Default whereOpen-source PgBouncerAzure built-in PgBouncerNeither
Clients per backend processOne at a timeManyMost
Session-scoped state survivesYesNoNo
Multi-statement transactionsSupportedSupportedNot supported
Prepared statementsWork normallyProtocol-level only, and only once `pgbouncer.max_prepared_statements` is above its default of 0Cannot be used

Decision tree

Is the pain latency, not exhaustion?Does it need session-scoped state?Running on the Burstable tier?Many replicas fanning into one server?Co-locate app and server, then cutround trips: pipeline, executemany, COPYSession pooling, or stay on port 5432No built-in PgBouncer on Burstable:pool in-process, or run your ownEnable pgbouncer.enabled, use 6432;default_pool_size = vCores x 2 to 5Size the client-side pool to process concurrencyand leave max_connections aloneyesyesyesyesnononono
Cheat sheet

Cheat sheet

  • Flexible server has built-in PgBouncer enabled by a server parameter and reached on port 6432
  • A pooler reuses a small set of server connections and absorbs connection storms
  • Transaction pooling returns the connection after each transaction; session pooling holds it for the session
  • Transaction pooling breaks session-scoped features like SET, advisory locks, and LISTEN/NOTIFY
  • Each connection is a backend process, so max_connections is bounded by SKU memory
  • An in-process connection pool reuses warm connections rather than one per request
  • Co-locate the app and the flexible server in the same region to cut round-trip latency
  • Batch writes and bulk loads to collapse many round-trips into few
  • TCP keepalives prevent idle pooled connections from being silently dropped

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. PgBouncer in Azure Database for PostgreSQL Flexible Server
  2. PostgreSQL: Architectural Fundamentals
  3. Connection Pooling Strategy Using PgBouncer in Azure Database for PostgreSQL Flexible Server
  4. Limits in Azure Database for PostgreSQL flexible server
  5. Psycopg 3: Connection pools
  6. SQLAlchemy: Connection Pooling
  7. PostgreSQL: SET
  8. PostgreSQL: Explicit Locking (advisory locks)
  9. PostgreSQL: LISTEN
  10. PostgreSQL: PREPARE
  11. asyncpg API reference
  12. What Is Azure Database for PostgreSQL flexible server?
  13. Plan Azure Database for PostgreSQL flexible server Deployments for Operational Performance Well-Architected
  14. Azure network round-trip latency statistics
  15. Psycopg 3: Pipeline mode support
  16. psycopg2: Extra specialized adapters and helpers
  17. PostgreSQL: Populating a Database
  18. Handle Transient Connectivity Errors
  19. Configure load balancer TCP reset and idle timeout
  20. PostgreSQL: libpq connection parameters
  21. Connections and Authentication / TCP Settings parameters