# Connection pooling

> Let many app connections share a few database connections, with a pooler Rowsafe installs and watches.

Source: https://rowsafe.sh/docs/guides/connection-pooling

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](#mysql-and-mariadb-proxysql)   |
| ClickHouse     | chproxy (HTTP interface) | [ClickHouse](#clickhouse-chproxy)                  |
| 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:

```text
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](https://rowsafe.sh/docs/guides/permissions)):

```sh
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:

```sh
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](#connect-your-apps).

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

```text
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](#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. |

**How passwords work**

Apps log in with **their own PostgreSQL user and password** (`scram-sha-256`). PgBouncer looks the hash up as `rowsafe_pgbouncer`, through a `SECURITY DEFINER` function that **never returns superusers** or expired passwords. The role has a random password, no special rights, and at most 20 connections.

So **superusers can't log in through PgBouncer**. Connect directly for admin work.

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

```text
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:

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

```sh
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:

| 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](https://rowsafe.sh/docs/guides/monitoring): `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:

```sh
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.

> **Warning:** 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`).

```ini title="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).

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

Keep both files private: the user list holds secrets.

```yaml title="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`:

```sh title="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](https://proxysql.com) under **Connections**, the same way. It installs from the server's package sources (add [ProxySQL's apt repository](https://proxysql.com/documentation/installing-proxysql/) 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](https://github.com/ContentSquare/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](#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](https://rowsafe.sh/docs/guides/agent-autonomy), 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

```text
/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}/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 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.
