Skip to main content
PostgreSQL or MongoDB — pick one. Prisme.ai’s three structured-data stores (users, permissions, collections) run on a single relational engine. The choice is per-deployment — you cannot mix Postgres and Mongo across these three.We recommend PostgreSQL for new deployments: cheaper managed offerings, ubiquitous ops know-how, Entra ID / IAM passwordless auth on Azure, and simpler backups. See MongoDB only if your team already runs Mongo at scale.

​
Role in the platform

PostgreSQL holds three logical databases:
  • users — user accounts and authentication metadata, plus all organization-level data: organizations themselves, memberships, groups, custom roles, API keys and service accounts. Used by prismeai-api-gateway.
  • permissions — workspace-level access-control rules, roles and memberships (workspaces, pages, apps…). Used by prismeai-workspaces, prismeai-events and prismeai-runtime. Note: org-level RBAC lives in users, not here.
  • collections — workspace data, automation state and product collections. Used by prismeai-runtime.

​
Version compatibility

  • Minimum: PostgreSQL 12+ (14+ recommended).
  • sslmode=require is mandatory on all connection strings if ssl is enforced by postgresql server.
Topology: primary + at least one standby replica, multi-AZ.
Minimum hardware: 2 vCPU, 2 GB RAM Minimum disk: 10 GB per node, to grow with usage. By default, the three databases (users, permissions, collections) live on a single shared cluster — keeps the initial deployment simple.
Under sustained high load, the auth path (users + permissions, critical on every request) can be impacted by collections growth — these have very different access patterns. If a single cluster proves insufficient, split into two PostgreSQL clusters: one for users + permissions, one for collections. They can then be sized and backed up independently.

​
Configuration

Three Helm keys change compared to MongoDB. Each is marked with a postgresql comment in the chart values.yaml. URL example: postgres://user:password@db.example.com:5432/permissions?sslmode=require.
prismeai-core-values.yml
See Helm install — PostgreSQL for the full install context.

​
Backup & restore

​
Backup with pg_dump

​
Restore with pg_restore

​
Managed services

  • RDS: automated daily snapshots + point-in-time recovery.
  • Azure Flexible Server: built-in backup with configurable retention.
  • Cloud SQL: automated backups with point-in-time recovery.
Operational strategy (RPO/RTO, retention) lives in Operations / Backup.

​
Updates

  • Schema migrations run automatically on backend startup.
  • For major Postgres version jumps (e.g. 12 → 14), follow the cloud provider’s blue/green or in-place upgrade procedure. Snapshot first.
See Operations / Updates.

​
Scaling

  • Vertical first: PostgreSQL scales well on a single beefy primary up to most workloads.
  • Read replicas when read load is heavy and your application can tolerate stale reads.
  • Connection pooling: deploy PgBouncer in front of Postgres for high-concurrency workloads.
  • Indexes: monitor with pg_stat_statements. Common hot paths are user lookups and workspace-scoped queries.

​
Azure Entra ID passwordless auth

On Azure DB for PostgreSQL Flexible Server, use Entra ID Workload Identity to inject short-lived tokens instead of static passwords. Configure azureManagedIdentityClientId on the relevant Helm storage blocks — see Azure deployment notes.

​
Troubleshooting

​
remaining connection slots are reserved for roles with the SUPERUSER attribute

The PostgreSQL cluster has reached its max_connections limit — non-superuser roles can no longer open new connections because the remaining slots are reserved for superusers (see superuser_reserved_connections).

​
How many connections does each pod open?

Every backend pod talking to PostgreSQL keeps one connection pool per database it consumes. Each pool grows on demand to serve concurrent queries, so the count varies between an idle baseline and a saturation cap. The figures below assume the default pool cap (max=10 sockets per pool): Notes:
  • prismeai-api-gateway does not connect to the permissions DB. Its org-level RBAC (organizations, memberships, service accounts) is stored in the users DB alongside the user accounts. The permissions DB only holds workspace-level RBAC, consumed by workspaces, events and runtime.
  • The runtime is multi-threaded: each worker thread opens its own pools in addition to the main thread, so a runtime pod with RUNNER_MAX_THREADS=4 (the recommended setting for large loads) holds 5 pools to permissions and 8 pools to collections — by itself it can consume 50 + 80 = 130 sockets at peak.
  • HPA scale-out multiplies these numbers linearly with the replica count of every service.
Worked example — RUNNER_MAX_THREADS=4 and 2 pods of each backend service: In practice, the steady-state usage stays very close to the Min column — pools rarely all hit max=10 simultaneously, because that requires every pool to face a concurrent burst at the same instant. The Max column is a theoretical worst case, not a sizing target. For this deployment, sizing max_connections ~= 100 (Min + a small headroom) is enough under normal traffic. You only need to approach the 340 ceiling if you regularly see large concurrent bursts hitting every service at once, or simply if you need to scale the runtime to many pods — each additional runtime replica adds 5 permissions pools + 8 collections pools, so the high-water mark grows fast.
At that point PgBouncer is a far better answer than raising max_connections, because it removes the linear coupling between pod count and Postgres backend count entirely.

​
Inspecting current usage

Check how connections are distributed across databases — this tells you whether one DB (typically collections) is responsible for the saturation:

​
Resolving the error

  • Cap the per-pod pool size via env vars — PERMISSIONS_STORAGE_POOL_MAX=5, COLLECTIONS_STORAGE_POOL_MAX=3, USERS_STORAGE_POOL_MAX=5. This is the lowest-friction fix and bounds each pod’s budget without restarting Postgres. See connection pool internals — tuning levers for the full list.
  • Raise max_connections on the cluster (requires a restart on most managed services). Add headroom for migrations and admin sessions on top of the steady-state usage.
  • Reduce replicas when possible — lower HPA maxReplicas, or RUNNER_MAX_THREADS on the runtime (each unit removed = -1 permissions pool and -2 collections pools per runtime pod).
  • Put a connection pooler in front of Postgres (see below). This is the durable fix for any deployment that scales horizontally.

​
Connection pooling with PgBouncer

For any deployment that scales horizontally, a connection pooler in front of Postgres is not optional — it is the structural fix that lets Prisme.ai scale at all without crushing the database.
Why Postgres can’t absorb the connections on its own
PostgreSQL uses a one-process-per-connection model: every open client connection spawns a dedicated OS backend process on the server, holding ~5–10 MB of RSS plus its own working mem allocations, file descriptors, and scheduler footprint. This design is excellent for query isolation but scales terribly with connection count:
  • Memory blows up linearly — 500 connections = 2.5–5 GB of backend RAM just to hold the processes open, before any query runs. On a small managed instance (4–8 GB total), that swallows most of the shared_buffers budget.
  • CPU context-switching degrades as the kernel juggles hundreds of mostly-idle processes.
  • Even idle connections cost — a connection sitting in idle state still holds its backend process; it doesn’t “go to sleep” for free.
This is why managed Postgres services cap max_connections aggressively (typically ~100 on small tiers, ~300–600 on medium ones): raising the cap doesn’t free you, it just shifts the wall further out before the same RAM limit hits. Now combine that with how Prisme.ai pods open connections ((1 + RUNNER_MAX_THREADS) × N_runtime permissions pools, 2 × RUNNER_MAX_THREADS × N_runtime collections pools, all multiplied by HPA replicas): the count grows on every scaling axis at once.
You hit max_connections long before you hit the CPU/RAM ceiling of the database itself — i.e. the cluster could happily serve the query load, but it runs out of connection slots first.
What PgBouncer changes
PgBouncer is a small, single-binary proxy that decouples client sockets from backend processes. The Prisme.ai pods open thousands of cheap client connections against PgBouncer, while PgBouncer maintains only a small fixed pool of expensive real connections against Postgres. In transaction mode — the recommended mode for Prisme.ai — PgBouncer keeps a fixed server pool of backend connections permanently open and warm against Postgres. When a pod begins a transaction, PgBouncer hands it one of these warm connections; on COMMIT/ROLLBACK, the connection is immediately returned to the pool and made available to the next caller. Between transactions the server connection sits idle but stays open — it never has to be re-established. Practical consequence: the size of your Postgres backend pool is decoupled from the number of pods you run. You can scale Prisme.ai horizontally (more api-gateway/workspaces/runtime replicas, more runtime worker threads) without changing the load on Postgres — only the number of concurrently executing queries matters, and that is bounded by default_pool_size, not by pods × pool_size. The MikroORM / pg pools inside each Prisme.ai pod work transparently against transaction-mode PgBouncer; the platform does not use session-scoped features (LISTEN/NOTIFY, advisory locks, SET outside a transaction) that would break it.
What happens when the pool is full
If default_pool_size=10 and an 11th query arrives concurrently, PgBouncer does not return an error — it queues the request until one of the 10 server connections is released. For Prisme.ai workloads (short auth lookups, RBAC checks, small writes) transactions typically last a few milliseconds, so the queue drains almost instantly even with a small server pool. A client only sees an error if its wait exceeds query_wait_timeout (default 120s). So default_pool_size should be read as “max queries simultaneously executing on Postgres” — not “max queries the platform can serve”. The hundreds of client sockets opened by the Prisme.ai pods are absorbed by PgBouncer (max_client_conn, typically set to a few thousand) and never reach Postgres.
Where to deploy it
When self-deploying, the Prisme.ai-side cluster is the most common location: lowest RTT from the client pools, decoupled from the DB lifecycle. Expose PgBouncer as a Service and point the Helm storage URLs at it instead of at the Postgres host.
When to deploy it
The pattern to watch for is recurring remaining connection slots are reserved... errors during traffic peaks, even though the Postgres CPU and memory usage stay moderate. That is the signature of connection-slot exhaustion rather than database overload — exactly what PgBouncer is built to absorb.