Connection pooling
Let many app connections share a few database connections, with a pooler Rowsafe installs and watches.
A pooler sits between your apps and the database. Apps open as many connections as they like; the pooler shares a few real ones between them. Rowsafe installs it, configures it and shows it in Pulse, where root allowed it and when someone turns it on.
This page describes PgBouncer, with a database app on a server db-1.
| Database | Pooler | Details |
|---|---|---|
| PostgreSQL | PgBouncer | this page |
| MySQL, MariaDB | ProxySQL | MySQL and MariaDB |
| ClickHouse | chproxy (HTTP interface) | ClickHouse |
| MongoDB | none needed | MongoDB's drivers keep their own pool in every app |
| Redis, Valkey | not yet |
When you need it
- You run out of connections: Pulse says "Almost out of connections", or apps get
too many clients already. - You run many app instances (web servers, workers, autoscaling containers).
- Your code runs on serverless platforms that connect per request.
- Most connections sit idle, costing memory.
A few long-lived app servers probably don't need it yet.
Allow it on the server
Root must allow it. The installer asks:
Install and manage PgBouncer (connection pooling), when someone turns pooling on? Nothing is installed now. [Y/n]Yes installs nothing yet. PgBouncer may then go only in front of the server's current clusters. To change it later (permissions):
sudo rowsafe-allow pooler # allow it
sudo rowsafe-allow --remove pooler # don't allow it
sudo rowsafe-allow pooler pooler-public # also allow listening on public addresses- Installer flags:
--allow-pooler,--no-allow-pooler,--allow-pooler-public. For a cluster added later, run the installer again. - If you disallow it, a PgBouncer Rowsafe set up keeps running, but Rowsafe can't change it. Turn pooling off first.
Turn it on
Click Turn on pooling on the database's Connections tab, or Apply fix on Pulse's "Most connections are in use" finding. Or:
rowsafe pooling on app # Rowsafe's defaults
rowsafe pooling on app --mode session --port 6433Owners and admins only. It takes about a minute. Nothing changes for your apps until you point them at PgBouncer.
What Rowsafe does
- Installs PgBouncer from apt, if needed.
- Creates a lookup role,
rowsafe_pgbouncer(see "How passwords work"). - Checks PgBouncer can log in at
127.0.0.1. - Writes
/etc/pgbouncer/pgbouncer.ini. Your own edits get replaced. - Raises the open-file limit to 65536.
- Starts PgBouncer and tests a connection.
The task says what it set up:
Pooling is on: PgBouncer 1.24.1 listens on 127.0.0.1, 10.0.0.5, port 6432, in transaction mode, with up to 20 server connections per pool.A PgBouncer with its own configuration is left alone.
Settings
Leave them empty and Rowsafe picks them.
| Setting | CLI flag | Default | |
|---|---|---|---|
| Mode | --mode | transaction | transaction or session. See Transaction or session mode. |
| Pool size | --pool-size | From the server | PostgreSQL connections per pool. A pool is one database and one user. |
| Client connections | --max-client-conn | 1000 | How many app connections PgBouncer accepts, 10 to 100000. |
| Where it listens | --listen | local | local, private or public. See below. |
| Port | --port | 6432 | 1024 to 65535. |
Pool size: twice the CPUs, between 10 and 50, at most half the free room in max_connections (you can set up to all of it). All pools together use at most three quarters.
Where it listens:
--listen | PgBouncer accepts connections on |
|---|---|
local | This server only (127.0.0.1). The default. |
private | This server and its private addresses (10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16, 100.64.0.0/10, fc00::/7). |
public | Every address. Only if you choose it and root allowed it (--allow-pooler-public). Put a firewall in front of it. |
Transaction or session mode
Transaction mode (the default) lends a connection for one transaction at a time, so a few connections serve hundreds of clients. The catch: what outlives a transaction doesn't carry over:
SET(useSET LOCAL, orALTER ROLE app SET ...);LISTENandNOTIFY;- session advisory locks (
pg_advisory_lock;pg_advisory_xact_lockworks); - SQL
PREPARE; - temporary tables that outlive a transaction, including
ON COMMIT PRESERVE ROWS; WITH HOLDcursors.
Driver prepared statements work with PgBouncer 1.21 or newer. With older ones, turn them off in your driver.
Session mode gives each client its own connection. Everything works, but no more clients than PostgreSQL allows.
An app that needs session features (say, LISTEN) can connect directly while others use PgBouncer.
Connect your apps
The Connections tab and rowsafe pooling status app show two strings:
Direct: postgres://USER:PASSWORD@db-1:5432/DATABASE
Pooled: postgres://USER:[email protected]:6432/DATABASEPoint apps at pooled: same user, password and database. Move one app at a time. Keep direct for migrations and admin work.
Driver notes for transaction mode:
| Stack | What to do |
|---|---|
| Prisma | Add ?pgbouncer=true to the pooled URL, and put the direct URL in directUrl: prisma migrate needs a direct connection. |
| Rails | In database.yml, set advisory_locks: false (migrations take an advisory lock), or run migrations over the direct URL. With PgBouncer older than 1.21, also set prepared_statements: false. |
| Go (pgx) | Works as is with PgBouncer 1.21 or newer. With an older one, add default_query_exec_mode=simple_protocol to the connection string. |
| Java (JDBC) | Works as is with PgBouncer 1.21 or newer. With an older one, add prepareThreshold=0 to the JDBC URL. |
| Python (psycopg, SQLAlchemy) | psycopg 2 works as is. psycopg 3 prepares queries it runs often: with PgBouncer older than 1.21, pass prepare_threshold=None. With SQLAlchemy, keep its own pool small, or use NullPool and let PgBouncer do the pooling. |
| Node.js (node-postgres) | Works as is, unless you name your queries (name: makes a prepared statement): then use PgBouncer 1.21 or newer. |
Apps can keep their own pool, smaller.
Change the settings
Click Change settings, or rerun rowsafe pooling on with the flags to change:
rowsafe pooling on app --pool-size 30Mode, pool size or client limit reload (nothing disconnects). Port or listen address restart PgBouncer (connections drop).
Watch it in Pulse
The agent reads PgBouncer's statistics every minute:
| Metric | Meaning |
|---|---|
pooler_up | 1 when PgBouncer answered that minute, 0 when it didn't |
pooler_clients_active | Client connections to PgBouncer using a server connection or idle |
pooler_clients_waiting | Client connections waiting for PgBouncer to give them a server connection |
pooler_servers_active | PgBouncer's server connections in use by a client |
pooler_servers_idle | PgBouncer's server connections open and free |
pooler_pool_used_pct | Server connections in use in the busiest pool, as a percentage of its pool size |
pooler_wait_max_seconds | How long the longest-waiting client has waited for a server connection |
pooler_wait_avg_ms | Average time clients waited for a server connection over the last minute |
pooler_xact_rate | Transactions per second through PgBouncer |
pooler_query_rate | Queries per second through PgBouncer |
Two findings count in the health score:
| Finding | Severity | Fix |
|---|---|---|
PgBouncer isn't answering (pooler_down). Apps that connect through it can't reach the database. | Critical | Start PgBouncer again: rewrites its configuration with the current settings and starts it. If it keeps stopping, sudo journalctl -u pgbouncer says why. |
Apps wait for a database connection (pooler_waiting). Clients are waiting: the longest for a second or more, or 100 ms or more on average. | Warning | Raise the pool size, by half, within what max_connections allows. In session mode, also Switch to transaction mode. |
Built-in alert rules: pooler_down (critical, after 2 minutes) and pooler_clients_waiting (warning, over 2 seconds for 5 minutes). Long transactions are the most common reason clients wait.
Failover
PgBouncer only reaches clusters on its own server. It follows a standby promoted on the same server; for a new primary elsewhere, point apps there directly.
When it switches, it pauses PgBouncer (up to 15 seconds), so new queries wait instead of failing, then resumes on the new primary. The task reports the wait, for example "Clients waited 850 ms; none got an error." If it can't pause in time, clients mid-transaction get an error. The new cluster must accept password logins from 127.0.0.1.
Turn it off
Click Turn pooling off on the Connections tab, or:
rowsafe pooling off appRowsafe stops PgBouncer, restores its original files, removes the package if Rowsafe installed it, and drops the lookup role.
Apps connected through PgBouncer lose their connection. Switch them to direct first.
Uninstalling the agent doesn't turn pooling off: sudo systemctl disable --now pgbouncer does.
Docker
In Docker, run PgBouncer yourself as a compose service. Rowsafe monitors it (same metrics and findings) but can't change it.
There's no official PgBouncer image, and Rowsafe doesn't ship one. The files below are an example to adapt, using the community image edoburu/pgbouncer. Pin a version you've tried. Rowsafe tests only the monitoring side (ROWSAFE_POOLER_STATS_URL).
[databases]
* = host=postgres port=5432
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000
max_prepared_statements = 200
stats_users = stats
ignore_startup_parameters = extra_float_digitsThe user list has each app role's SCRAM verifier (SELECT rolname, rolpassword FROM pg_authid;), plus a stats user for Rowsafe (no PostgreSQL role needed).
"app" "SCRAM-SHA-256$4096:..."
"stats" "a-long-random-password"Keep both files private: the user list holds secrets.
services:
postgres:
# ... unchanged
pgbouncer:
image: edoburu/pgbouncer:latest # pin a version in production
restart: unless-stopped
depends_on: [postgres]
volumes:
- ./pgbouncer/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro
- ./pgbouncer/userlist.txt:/etc/pgbouncer/userlist.txt:ro
# ports: ["6432:6432"] # only for apps outside this compose project
rowsafe-agent:
# ... unchangedApps connect to pgbouncer:6432. Tell the agent, then docker compose up -d rowsafe-agent:
ROWSAFE_POOLER_STATS_URL=postgres://stats:PASSWORD@pgbouncer:6432/pgbouncer
# ROWSAFE_POOLER_DATABASE=app # the database's name in Rowsafe, if this agent has severalThe same settings work for a PgBouncer you run on a server.
MySQL and MariaDB (ProxySQL)
Turn on ProxySQL under Connections, the same way. It installs from the server's package sources (add ProxySQL's apt repository if missing) and listens on port 6033.
- Apps use their own users. ProxySQL gets the password hashes on the server; Rowsafe never sees passwords.
- Users limited to other hosts, or using socket, PAM or ed25519 logins, connect directly. The page lists them.
- On MySQL,
caching_sha2_passwordusers need TLS or their driver's option to fetch the server's public key. - After a standby's promotion, ProxySQL follows on the same server. For another server, root allows it once:
sudo rowsafe-allow pooler-target ADDRESS PORT(the Standby page shows it).
ClickHouse (chproxy)
chproxy sits in front of the HTTP interface. It limits queries per user and queues the rest (up to 30 seconds), so bursts wait instead of failing.
- HTTP only. Native clients (port 9000) connect directly.
- Apps' own logins pass through. Rowsafe stores no credentials.
- Root's helper downloads chproxy 1.30.0 from GitHub and checks its pinned SHA-256.
- Settings: Queries at once per user (the cores, at least 4), Queue (100), port (9090), and who can connect.
- Pulse shows queries running and waiting.
Apps connect with their own user:
http://USER:PASSWORD@db-1:9090/?database=DATABASELimits
- PgBouncer only reaches clusters on its own server.
- One pgbouncer package install or removal every 10 minutes, at most.
- One Rowsafe-managed PgBouncer per server, for one cluster.
- Debian and Ubuntu only. In Docker, Rowsafe only monitors (see Docker).
- Not on a standby. Turn it on for the primary.
- PgBouncer 1.14 or newer (1.21 for driver prepared statements). Debian 12 and Ubuntu 22.04 ship older; apt.postgresql.org has a current one.
- PgBouncer older than 1.20: a database created later isn't reachable until you click Change settings.
- md5 passwords don't work. Rowsafe lists those roles; set their passwords again (SCRAM is the default since PostgreSQL 14).
- Superusers can't log in through PgBouncer.
- PgBouncer connects over TCP at
127.0.0.1with a password. Ifpg_hba.confrefuses, Rowsafe names the line to add. - AI agents change pooling as the person who connected them, if that person is an owner or admin.
If something goes wrong
The task log (rowsafe task ID, or the task on the Connections tab) says what failed:
| Message | Fix |
|---|---|
installing and managing PgBouncer from Rowsafe isn't allowed for port 5432 on db-1 | Run the installer on db-1 again with --allow-pooler. |
PgBouncer is allowed on db-1, but not for this PostgreSQL (port 5433) | The cluster was added after the installer ran. Run it again to refresh the list. |
PgBouncer on db-1 already pools other-db; Rowsafe manages one pooler per server | Turn pooling off for the other database first. |
PostgreSQL refuses password logins from PgBouncer | Add the line it names to pg_hba.conf and reload PostgreSQL. |
PostgreSQL doesn't accept TCP connections on 127.0.0.1:5432 (check listen_addresses) | Make listen_addresses include localhost (a restart). |
PgBouncer 1.12.0 is too old: Rowsafe needs 1.14 or newer | Install a newer pgbouncer package, for example from apt.postgresql.org. |
this PostgreSQL is a standby | Turn pooling on for the primary. |
the PgBouncer helper on db-1 did not answer | Check systemctl status rowsafe-pooler.path rowsafe-pooler.service, then run sudo rowsafe-allow pooler on the server. |
PgBouncer did not start | sudo journalctl -u pgbouncer and /var/log/postgresql/pgbouncer.log say why. |
Files on the server
/etc/rowsafe/pooler-allowed root 0644; the PostgreSQL ports Rowsafe may pool (--allow-pooler)
/etc/pgbouncer/pgbouncer.ini root:postgres 0640; written by Rowsafe from its template
/etc/pgbouncer/userlist.txt root:postgres 0640; the password of rowsafe_pgbouncer
/etc/systemd/system/pgbouncer.service.d/rowsafe.conf the open-file limit
/etc/systemd/system/rowsafe-pooler.path starts the root helper when the agent asks
/etc/systemd/system/rowsafe-pooler.service the root helper, in PgBouncer mode: no network, writes only /etc/pgbouncer and its drop-in
/etc/systemd/system/[email protected] installs or removes the pgbouncer package, nothing else
/var/lib/rowsafe/pooler/ the agent's requests to the helper
/var/lib/rowsafe/pooling.json what the agent set up: database, settings, target
/var/lib/rowsafe-pooler/ root 0700; PgBouncer's original files, and whether Rowsafe installed it
/var/log/postgresql/pgbouncer.log PgBouncer's logThe agent runs as postgres and installs nothing itself. A root helper checks each request, writes PgBouncer's files from its template, and only installs or removes the pgbouncer package.
API
| Request | |
|---|---|
GET /v1/databases/{ref}/pooling | State (off, turning_on, on, turning_off, failed), whether it's allowed and available (and why not), settings, addresses, target, connection strings, the latest statistics, and the settings Rowsafe would pick. |
PUT /v1/databases/{ref}/pooling | Turn pooling on, or change its settings. Body: {"settings": {"mode": "transaction", "pool_size": 20, "max_client_conn": 1000, "listen": "private", "port": 6432}, "confirm": "app"}. Leave out settings Rowsafe should pick or keep. Returns 202 and the task. |
POST /v1/databases/{ref}/pooling/off | Turn pooling off. Body: {"confirm": "app"}. Returns 202 and the task. |
confirm must be the database's name. Errors:
400: a missing or wrongconfirm, or an invalid setting;403: pooling isn't allowed on that server (the message says why), a read-only API key, or a request through MCP;409: pooling is already off, the agent isn't connected, a pooling change is already running, or the server's PgBouncer already serves another database.