When a client connects to PostgreSQL database, postgres supervisor process (PostgreSQL database server) accepts the request, and forks a backend process.
So a connection = process in the system.
Usually your PostgreSQL server can have hundreds of connections without problems (max_connections, default is 100). But not all connection can actively do work (latency, expensive application logic), so they are idle.
From various resources, depends on your shared_buffers and huge_pages settings, usually an idle connection costs about 1.5–14.5 MB per connection on AWS RDS0, 7.6MB on simple read-only OLTP load.
It is also not good to keep a connection opens for too long. When a connection has seen too many tables, its memory usage increased and stayed, e.g., internal catalog metadata caches.
Connection Pooling help maintain a persistent connection between your Client and PostgreSQL database. Connection Pooling is to create a pool of connections with Postgres. Connection Pooling get available connections from the Postgres Server and gives to client.
You should reach for PgBouncer when you have far more clients than available connections. PostgreSQL has 500 connections but you have potentially 1500 clients.
PgBouncer is one of the Connection Pooling softwares. PgBouncer can keep your connection open with the database. Reuse existing opened connection for your clients (your web server). PgBouncer uses port 6432.
[Web] -> [PgBouncer] -> [PostgreSQL]
When PgBouncer got a request:
- Authenticate
- Checks if there is existing connection with same auth details
- Reuse if found; Otherwise create a new connection to your Database
PgBouncer has few pooling mode:
- Session pooling — Connection assigned during a session (until client disconnect)
- Transaction pooling — Connection assigned during a transaction (run a transaction)
- Statement pooling — Connection assigned during a statement (run a query)
Session pooling is too long and statement pooling has too many overheads. So Transaction pooling is a common choice. But this means you can’t use session-level features anymore, like named prepared statements, session advisory locks, listen/notify, or other session level features.
Configure PgBouncer by pgbouncer.ini configuration file. You also need to configure PgBouncer to start at server boot.
Each server when it is doing database work, it needs a connection. You could have more servers than your database connection. That’s why Active Record has ConnectionPool.
[Web Server / ActiveRecord ConnectionPool] <-> [PgBouncer] <-> [PostgreSQL]
Using PgBouncer means you can have more Web Servers because PgBouncer will
To use PgBouncer with ActiveRecord, you need to add prepared_statements=false to the database URL.
PgBouncer is a Connection Pooling software for your PostgreSQL database. v1.5.0 is the latest version as of Mar 25, 2021.
To install PgBouncer you also need openssl. Then apt-get:
apt-get install -y pgbouncer
Another connection pooling software is Odyssey.
How to Manage Connections Efficiently in Postgres, or Any Database