Client, Connection, Postgres
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.
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).
[Web] -> [PgBouncer] -> [PostgreSQL]
When PgBouncer got a request:
- 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.
PgBouncer and Active Record
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
[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 install -y pgbouncer
Another connection pooling software is Odyssey.