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

> PostgreSQL (recommended) for permissions, users and collections storage. Alternative to MongoDB. 

<Info>
  **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](/self-hosting/databases/mongodb) only if your team already runs Mongo at scale.
</Info>

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

## Recommended deployment

| Provider            | Recommended service                                                                   |
| ------------------- | ------------------------------------------------------------------------------------- |
| AWS                 | Amazon RDS for PostgreSQL, Multi-AZ.                                                  |
| Azure               | Azure Database for PostgreSQL — Flexible Server, with **Entra ID passwordless auth**. |
| GCP                 | Cloud SQL for PostgreSQL, regional HA.                                                |
| OpenShift / on-prem | Crunchy Postgres for Kubernetes operator, or self-managed with streaming replication. |

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

<Note>
  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.
</Note>

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

```yaml title="prismeai-core-values.yml" theme={null}
global:
  storage:
    permissions:
      driver: postgresql
      existingSecret: "core-permissions"     # `url` key

prismeai-api-gateway:
  storage:
    users:
      driver: postgresql
      existingSecret: "core-prismeai-api-gateway-users"

prismeai-runtime:
  storage:
    collections:
      driver: postgresql
      existingSecret: "core-prismeai-runtime-collections"
```

See [Helm install — PostgreSQL](/self-hosting/kubernetes/helm#6-postgresql) for the full install context.

## Backup & restore

### Backup with `pg_dump`

```bash theme={null}
pg_dump "postgres://user:password@host:5432/database?sslmode=require" \
  --format=custom \
  --file=/backup/pg/$(date +%Y-%m-%d)-database.dump
```

### Restore with `pg_restore`

```bash theme={null}
pg_restore --clean --if-exists \
  --dbname="postgres://user:password@host:5432/database?sslmode=require" \
  /backup/pg/<date>-database.dump
```

### 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](/self-hosting/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](/self-hosting/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](/self-hosting/cloud/azure).

## 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`](https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-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):

| Service                | Database      | Pools per pod                | Min (warm idle) | Max (saturated) |
| ---------------------- | ------------- | ---------------------------- | --------------- | --------------- |
| `prismeai-api-gateway` | `users`       | 2 (users storage + org RBAC) | \~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 per pool  | 10 per pool     |
| `prismeai-runtime`     | `collections` | `2 × RUNNER_MAX_THREADS`     | \~1–2 per pool  | 10 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:

| DB                         | Contributions                                                                   | Min (idle warm) | Max (theoretical cap) |
| -------------------------- | ------------------------------------------------------------------------------- | --------------- | --------------------- |
| `users`                    | api-gateway: 2 × (2–4 → 20)                                                     | **4–8**         | **40**                |
| `permissions`              | workspaces: 2 × (1–2 → 10) + events: 2 × (1–2 → 10) + runtime: 2 × (10–15 → 50) | **24–38**       | **140**               |
| `collections`              | runtime: 2 × (8–16 → 80)                                                        | **16–32**       | **160**               |
| **Total (shared cluster)** |                                                                                 | **44–78**       | **340**               |

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](#connection-pooling-with-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:

```sql theme={null}
SELECT
    datname,
    COUNT(*) AS nb_connexions
FROM pg_stat_activity
WHERE usename = 'avnadmin'  -- replace with the role used by Prisme.ai
GROUP BY datname
ORDER BY nb_connexions DESC;
```

#### 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](/technical/postgresql-connection-pools#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**](https://www.pgbouncer.org/) 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

| Environment                                  | Where PgBouncer lives                                        | Notes                                                                                                                                                                                                                                                                                                                 |
| -------------------------------------------- | ------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Azure DB for PostgreSQL Flexible Server**  | Managed, co-located with the DB                              | [Built-in PgBouncer](https://learn.microsoft.com/azure/postgresql/flexible-server/concepts-pgbouncer) — enable a server parameter, nothing to deploy.                                                                                                                                                                 |
| **AWS RDS / Aurora**                         | Managed (RDS Proxy) **or** in the app k8s cluster            | [**RDS Proxy**](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html) is the managed equivalent. Otherwise deploy PgBouncer as a Deployment in the Prisme.ai cluster.                                                                                                                                |
| **GCP Cloud SQL**                            | In the app k8s cluster, **or** use AlloyDB (built-in pooler) | Cloud SQL itself has no built-in pooler.                                                                                                                                                                                                                                                                              |
| **Self-hosted Postgres (Crunchy / on-prem)** | Either side                                                  | The [Crunchy PGO operator](https://access.crunchydata.com/documentation/postgres-operator/latest/tutorials/day-two/connection-pooling) deploys PgBouncer next to the Postgres pods. Otherwise deploy it standalone in the Prisme.ai cluster (e.g. [`edoburu/pgbouncer`](https://hub.docker.com/r/edoburu/pgbouncer)). |

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

| Deployment shape                                | Recommendation                                                                                                                               |
| ----------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------- |
| Single-replica, POC, dev                        | Raising `max_connections` is enough — PgBouncer is overkill.                                                                                 |
| 2–3 replicas per service, fixed                 | Borderline. Capping pool sizes via env vars usually buys enough headroom.                                                                    |
| HPA-driven scale-out, `RUNNER_MAX_THREADS >= 2` | **Deploy PgBouncer (or a managed equivalent) — it is the only path that scales linearly without resizing Postgres each time you add a pod.** |

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.