PostgreSQL connection pools — internals

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; 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 v6 as the ORM layer (instantiated from @prisme.ai/permissions → lib/databases/orm.ts).
MikroORM uses Knex.js + 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)

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

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

Typical tuning recipes

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

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:

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:

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:

RUNNER_MAX_THREADS=1   # default; remove if you previously raised it

Inspection queries

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

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

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

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;

Self-hosting / PostgreSQL — user-facing setup, sizing, and PgBouncer guidance.

​What sits between Prisme.ai and PostgreSQL

​How many pools does each pod open?

​prismeai-api-gateway

​prismeai-workspaces

​prismeai-events

​prismeai-runtime

​Connection budget per pod, per database

​Total connection envelope

​Why pools never shrink in practice

​Tuning levers

​Typical tuning recipes

​Inspection queries

​Related