> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prisme.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL connection pools — internals

> How each Prisme.ai backend service opens connection pools against PostgreSQL: number of pools per pod, per-thread behavior, observed warm-up patterns, and tuning levers.

This page is the **source of truth** for understanding how many PostgreSQL connections each Prisme.ai pod can consume, where they come from in the code, and how to tune them. The user-facing summary lives in [Self-hosting / PostgreSQL](/self-hosting/databases/postgresql); this document drills into the implementation details that explain the numbers.

## What sits between Prisme.ai and PostgreSQL

All four backend services that talk to PostgreSQL (`api-gateway`, `workspaces`, `events`, `runtime`) use:

* **[MikroORM](https://mikro-orm.io/) v6** as the ORM layer (instantiated from `@prisme.ai/permissions` → `lib/databases/orm.ts`).
* MikroORM uses **[Knex.js](https://knexjs.org/) + [Tarn.js](https://github.com/vincit/tarn.js/)** under the hood for SQL connection pooling (not `pg-pool` directly).
* Pool defaults come from Knex (`node_modules/knex/lib/client.js:poolDefaults()`):

  | Option              | Default                            |
  | ------------------- | ---------------------------------- |
  | `min`               | **2** sockets per pool             |
  | `max`               | **10** sockets per pool            |
  | `idleTimeoutMillis` | **30 000 ms** (30 s, Tarn default) |

A pool keeps at least `min` sockets warm at all times (this is why every pool you observe holds \~2 connections even when idle) and grows on demand up to `max`. Idle sockets above `min` are supposed to be released after `idleTimeoutMillis`. In practice (see [Why pools never shrink](#why-pools-never-shrink-in-practice)) continuous background activity keeps the pools at their high-water mark for hours.

## How many pools does each pod open?

This is the part that explains why a "small" deployment can hold many connections.

### `prismeai-api-gateway`

Single-process pod (no clustering, no worker threads).

| Pool              | Target DB             | Code reference                                                                                                                                                                     |
| ----------------- | --------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Users storage     | **`users`**           | `services/api-gateway/src/storage/index.ts:50` (`DatabaseOrm.build()`)                                                                                                             |
| Org AccessManager | **`users`** (same DB) | `services/api-gateway/src/index.ts:102` → `initOrgAccessManager(orgStorageConfig)` where `orgStorageConfig` is derived from the same `storage` config (`src/config/storage.ts:90`) |

The api-gateway does **not** connect to the `permissions` database. Its AccessManager handles **org-level** RBAC (organizations, memberships, service accounts, invites) and stores those tables inside the `users` DB alongside the user accounts. The `permissions` DB is reserved for **workspace-level** RBAC, which only `workspaces`, `events` and `runtime` consume.

The two pools are independent `pg.Pool` instances on the same DB, which is why a single api-gateway pod typically shows \~4 connections on `users` (2 pools × \~2 warm sockets).

Also, no separate pool exists for sessions, magic links, refresh tokens, or service accounts — they all share the single users storage pool.

**Total pools per pod:** 2, both targeting `users`.

### `prismeai-workspaces`

Single-process pod.

| Pool                        | Code reference                                                                                 |
| --------------------------- | ---------------------------------------------------------------------------------------------- |
| 1 pool to **`permissions`** | `services/workspaces/src/app.ts:71` → `initAccessManager(PERMISSIONS_STORAGE_OPTIONS, broker)` |

**Total pools per pod:** 1.

### `prismeai-events`

Single-process pod.

| Pool                        | Code reference                                                                             |
| --------------------------- | ------------------------------------------------------------------------------------------ |
| 1 pool to **`permissions`** | `services/events/src/app.ts:39` → `initAccessManager(PERMISSIONS_STORAGE_OPTIONS, broker)` |

**Total pools per pod:** 1.

### `prismeai-runtime`

**Multi-threaded** via Node.js `worker_threads`. The number of worker threads is controlled by `RUNNER_MAX_THREADS` (`services/runtime/config/runtime.ts:7`, **default `1`**). Each worker thread fully re-initializes its database stack, so it opens **its own pools** — they are not shared with the main thread.

Let `W = RUNNER_MAX_THREADS`.

**Main thread:**

| Pool                        | Code reference                                           |
| --------------------------- | -------------------------------------------------------- |
| 1 pool to **`permissions`** | `services/runtime/src/app.ts:41` → `initAccessManager()` |

The main thread does **not** open a pool to `collections` — the collections modules are only started inside workers.

**Per worker thread** (×W):

| Pool                                     | Code reference                                                                                                                                           |
| ---------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 pool to **`permissions`**              | `services/runtime/src/services/runtime/workers/runner.ts:90` → `initAccessManager()` re-called in the worker context                                     |
| 1 pool to **`collections`**              | `services/runtime/src/runtimeModules/collections/index.ts:133` → `DatabaseOrm.build()` in `CollectionModules.start()`                                    |
| 1 pool to **`collections`** (second one) | `services/runtime/src/runtimeModules/access-manager/productBindingsManager.ts:108` → `DatabaseOrm.build()` for product bindings — distinct pool, same DB |

**Total pools per pod:**

* `permissions`: `1 + W` pools
* `collections`: `2 × W` pools

For the default `W=1`: 2 pools to permissions, 2 to collections.
For a tuned runtime `W=4`: 5 pools to permissions, 8 to collections.

## Connection budget per pod, per database

Multiplying pool count by the `pg-pool` `max=10` default gives the **theoretical maximum** each pod can hold. The **observed minimum** in our sandbox (idle but warm) is roughly 1–3 sockets per pool, because there is enough background traffic to keep every pool at a non-zero high-water mark.

| Service                | Database      | Pools per pod                         | Min observed (warm but idle) | Max possible (default `max=10`) |
| ---------------------- | ------------- | ------------------------------------- | ---------------------------- | ------------------------------- |
| `prismeai-api-gateway` | `users`       | 2 (users storage + org AccessManager) | \~2–4                        | 20                              |
| `prismeai-workspaces`  | `permissions` | 1                                     | \~1–2                        | 10                              |
| `prismeai-events`      | `permissions` | 1                                     | \~1–2                        | 10                              |
| `prismeai-runtime`     | `permissions` | `1 + RUNNER_MAX_THREADS`              | \~2–3 × pools                | 10 × pools                      |
| `prismeai-runtime`     | `collections` | `2 × RUNNER_MAX_THREADS`              | \~1–2 × pools                | 10 × pools                      |

`prismeai-api-gateway` does not open any pool against the `permissions` DB — its org-level RBAC tables live alongside the user accounts in the `users` DB.

### Total connection envelope

For a deployment with `Na` api-gateway pods, `Nw` workspaces pods, `Ne` events pods, `Nr` runtime pods (each with `W` worker threads):

```
permissions = (Nw + Ne + Nr × (1 + W)) × <connections per pool>
users       =  Na × 2                  × <connections per pool>
collections =  Nr × 2 × W              × <connections per pool>
```

With "connections per pool" ranging from \~1 (cold) to 10 (saturated). Add HPA scale-out into the mix and the cluster-wide envelope multiplies linearly with the number of replicas of every service.

## Why pools never shrink in practice

`pg-pool` is supposed to release idle sockets after `idleTimeoutMillis=10s`. Yet observed connections on the sandbox stay open for **hours**:

> PID 3066265 opened at 05:00, last query at 11:47 — still open at 12:45.

This is because Prisme.ai pods continuously emit small queries against `permissions` (RBAC checks during event subscriptions, cron-triggered automations, workspace registry refresh, etc.). Each of these queries touches a socket and resets its idle timer just often enough to keep it from being recycled. Once a pool reaches its high-water mark during a burst, it stays there indefinitely.

A signature of this pattern is visible in `pg_stat_activity`: several connections holding the **exact same `query_id`** for the same workspace's role lookup. That is one warm socket per runtime thread (main + each worker), each having most recently served the same RBAC check.

## Tuning levers

Every storage block accepts pool-sizing env vars of the form `<NAME>_STORAGE_POOL_<key>`. They are parsed by `parsePoolOptionsFromEnv()` (`packages/permissions/lib/databases/poolOptions.ts`) and forwarded to MikroORM's `pool` option, which Knex/Tarn enforces. Set the same env vars on every replica of the corresponding Deployment.

| Env var                                    | PostgreSQL (Knex/Tarn)                     | MongoDB (native driver)                                                      |
| ------------------------------------------ | ------------------------------------------ | ---------------------------------------------------------------------------- |
| `PERMISSIONS_STORAGE_POOL_MIN`             | `pool.min` (default `2`)                   | `minPoolSize` (default `0`) — translated by MikroORM.                        |
| `PERMISSIONS_STORAGE_POOL_MAX`             | `pool.max` (default `10`)                  | `maxPoolSize` (default `100`) — translated by MikroORM.                      |
| `PERMISSIONS_STORAGE_POOL_IDLE_TIMEOUT_MS` | `pool.idleTimeoutMillis` (default `30000`) | `maxIdleTimeMS` — translated in `driverConfig.ts` (MikroORM doesn't map it). |

The same `MIN` / `MAX` / `IDLE_TIMEOUT_MS` keys exist for `USERS_STORAGE_POOL_*` (api-gateway users pools — the org RBAC AccessManager also targets the `users` DB and respects the same vars) and `COLLECTIONS_STORAGE_POOL_*` (each runtime collections pool, one per worker). Set the same env vars on every replica of the corresponding Deployment.

For the runtime specifically, `RUNNER_MAX_THREADS` (default `1`) has the largest impact on connection count — each unit reduction removes one `permissions` pool and two `collections` pools.

<Note>
  The legacy `<NAME>_STORAGE_OPT_<key>` env vars feed MikroORM's `driverOptions`, which are passed to the underlying client (`pg.Client` for Postgres, `MongoClient` for Mongo) — **not** to the pool on Postgres. On Postgres, setting `PERMISSIONS_STORAGE_OPT_max=5` does *not* cap the pool; use `PERMISSIONS_STORAGE_POOL_MAX=5` instead. The `*_STORAGE_OPT_*` channel is still useful as an escape hatch when a driver-specific option needs to override a `*_STORAGE_POOL_*` value (for example, `*_STORAGE_OPT_maxIdleTimeMS` wins over `*_STORAGE_POOL_IDLE_TIMEOUT_MS` on Mongo).
</Note>

### Typical tuning recipes

**Cap pool growth without losing parallelism** (sensible default for medium deployments):

```bash theme={null}
PERMISSIONS_STORAGE_POOL_MAX=5
USERS_STORAGE_POOL_MAX=5
COLLECTIONS_STORAGE_POOL_MAX=3
```

This bounds each pod to a predictable budget while still allowing \~3–5 concurrent queries per pool — enough for most request bursts.

**Shrink the idle floor** when many pools sit warm with no traffic:

```bash theme={null}
PERMISSIONS_STORAGE_POOL_MIN=0
COLLECTIONS_STORAGE_POOL_MIN=0
```

Combined with a small `IDLE_TIMEOUT_MS`, this brings the steady-state connection count back down between bursts instead of camping at `min`:

```bash theme={null}
PERMISSIONS_STORAGE_POOL_IDLE_TIMEOUT_MS=2000
COLLECTIONS_STORAGE_POOL_IDLE_TIMEOUT_MS=2000
```

**Reduce runtime footprint** by lowering worker count if you don't need the parallelism:

```bash theme={null}
RUNNER_MAX_THREADS=1   # default; remove if you previously raised it
```

## Inspection queries

Distribution of connections per database (replace `avnadmin` with your role):

```sql theme={null}
SELECT datname, COUNT(*) AS nb_connexions
FROM pg_stat_activity
WHERE usename = 'avnadmin'
GROUP BY datname
ORDER BY nb_connexions DESC;
```

Per-query distribution to identify hot pools (useful to spot the runtime worker fan-out — repeated identical `query_id` across N rows = N runtime threads):

```sql theme={null}
SELECT
    datname,
    query_id,
    COUNT(*) AS nb,
    MIN(backend_start) AS oldest,
    MAX(state_change)  AS most_recent
FROM pg_stat_activity
WHERE usename = 'avnadmin' AND state = 'idle'
GROUP BY datname, query_id
ORDER BY nb DESC, oldest;
```

Connection age (to confirm pools are not shrinking):

```sql theme={null}
SELECT pid, datname, application_name,
       backend_start, state, state_change,
       NOW() - backend_start AS age
FROM pg_stat_activity
WHERE usename = 'avnadmin'
ORDER BY backend_start;
```

## Related

* [Self-hosting / PostgreSQL](/self-hosting/databases/postgresql) — user-facing setup, sizing, and PgBouncer guidance.