Skip to content
Rowsafe
Docs

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.

DatabasePoolerDetails
PostgreSQLPgBouncerthis page
MySQL, MariaDBProxySQLMySQL and MariaDB
ClickHousechproxy (HTTP interface)ClickHouse
MongoDBnone neededMongoDB's drivers keep their own pool in every app
Redis, Valkeynot 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 6433

Owners and admins only. It takes about a minute. Nothing changes for your apps until you point them at PgBouncer.

What Rowsafe does

  1. Installs PgBouncer from apt, if needed.
  2. Creates a lookup role, rowsafe_pgbouncer (see "How passwords work").
  3. Checks PgBouncer can log in at 127.0.0.1.
  4. Writes /etc/pgbouncer/pgbouncer.ini. Your own edits get replaced.
  5. Raises the open-file limit to 65536.
  6. 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.

SettingCLI flagDefault
Mode--modetransactiontransaction or session. See Transaction or session mode.
Pool size--pool-sizeFrom the serverPostgreSQL connections per pool. A pool is one database and one user.
Client connections--max-client-conn1000How many app connections PgBouncer accepts, 10 to 100000.
Where it listens--listenlocallocal, private or public. See below.
Port--port64321024 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:

--listenPgBouncer accepts connections on
localThis server only (127.0.0.1). The default.
privateThis 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).
publicEvery 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 (use SET LOCAL, or ALTER ROLE app SET ...);
  • LISTEN and NOTIFY;
  • session advisory locks (pg_advisory_lock; pg_advisory_xact_lock works);
  • SQL PREPARE;
  • temporary tables that outlive a transaction, including ON COMMIT PRESERVE ROWS;
  • WITH HOLD cursors.

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:PASSWORD@10.0.0.5:6432/DATABASE

Point 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:

StackWhat to do
PrismaAdd ?pgbouncer=true to the pooled URL, and put the direct URL in directUrl: prisma migrate needs a direct connection.
RailsIn 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 30

Mode, 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:

MetricMeaning
pooler_up1 when PgBouncer answered that minute, 0 when it didn't
pooler_clients_activeClient connections to PgBouncer using a server connection or idle
pooler_clients_waitingClient connections waiting for PgBouncer to give them a server connection
pooler_servers_activePgBouncer's server connections in use by a client
pooler_servers_idlePgBouncer's server connections open and free
pooler_pool_used_pctServer connections in use in the busiest pool, as a percentage of its pool size
pooler_wait_max_secondsHow long the longest-waiting client has waited for a server connection
pooler_wait_avg_msAverage time clients waited for a server connection over the last minute
pooler_xact_rateTransactions per second through PgBouncer
pooler_query_rateQueries per second through PgBouncer

Two findings count in the health score:

FindingSeverityFix
PgBouncer isn't answering (pooler_down). Apps that connect through it can't reach the database.CriticalStart 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.WarningRaise 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 app

Rowsafe 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).

pgbouncer/pgbouncer.ini
[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_digits

The 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).

pgbouncer/userlist.txt
"app" "SCRAM-SHA-256$4096:..."
"stats" "a-long-random-password"

Keep both files private: the user list holds secrets.

compose.yml
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:
    # ... unchanged

Apps connect to pgbouncer:6432. Tell the agent, then docker compose up -d rowsafe-agent:

rowsafe-agent.env
ROWSAFE_POOLER_STATS_URL=postgres://stats:PASSWORD@pgbouncer:6432/pgbouncer
# ROWSAFE_POOLER_DATABASE=app       # the database's name in Rowsafe, if this agent has several

The 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_password users 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=DATABASE

Limits

  • 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.1 with a password. If pg_hba.conf refuses, 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:

MessageFix
installing and managing PgBouncer from Rowsafe isn't allowed for port 5432 on db-1Run 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 serverTurn pooling off for the other database first.
PostgreSQL refuses password logins from PgBouncerAdd 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 newerInstall a newer pgbouncer package, for example from apt.postgresql.org.
this PostgreSQL is a standbyTurn pooling on for the primary.
the PgBouncer helper on db-1 did not answerCheck systemctl status rowsafe-pooler.path rowsafe-pooler.service, then run sudo rowsafe-allow pooler on the server.
PgBouncer did not startsudo 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/rowsafe-pooler-apt@.service   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 log

The 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}/poolingState (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}/poolingTurn 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/offTurn pooling off. Body: {"confirm": "app"}. Returns 202 and the task.

confirm must be the database's name. Errors:

  • 400: a missing or wrong confirm, 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.
Edit on GitHub