For illustration purposes, I will use pgpool-II and pgbouncer to explain concepts of connection pooling and compare which one is more effective in pooling connections because some connection poolers can even affect database performance. In this article, we will explore how we can use connection pooling middleware like pgpool and pgbouncer to reduce overhead and network latency. Your production database shouldn’t be your bottleneck. Traffic is never constant, so pooling can better manage traffic peaks without causing outages. It lets your database scale effectively as the data stored there and the number of clients accessing it grow. Instead of opening and closing connections for every request, connection pooling uses a cache of database connections that can be reused when future requests to the database are required. If you expose your backend service as an API, repeated slowdowns and failures could cause cascading problems and lose you customers. If that performance deteriorates, it can lead to poor user experiences, revenue losses, and even unscheduled downtime. This creates a large amount of overhead causing database performance to deteriorate.Ĭonsumers of your service expect fast response times. In a production environment where we expect to receive thousands or millions of concurrent connections to the backend service, this can quickly exceed your memory resources (or if you have a scalable cloud, it can get very expensive very quickly).īecause each time a client attempts to access a backend service, it requires OS resources to create, maintain, and close connections to the datastore. With PostgreSQL, each new connection can take up to 1.3MB in memory. Most web services are backed by relational database servers such as Postgres or MySQL. But you can further improve performance by pooling users' connections to a database.Ĭlient users need to create a connection to a web service before they can perform CRUD operations. Caching frequently-accessed queries in memory or via a database can optimize write/read performance and reduce network latency, especially for heavy-workload applications, such as gaming services and Q&A portals. We tend to rely on caching solutions to improve database performance.

Stack vs queue routing