Skip to main content

​
Role in the platform

PostgreSQL is an alternative to MongoDB for the three structured-data stores: users, permissions and collections. The choice is per-deployment — you cannot mix Mongo and Postgres across these three. If you don’t have a strong preference, MongoDB is the default — see MongoDB.

​
Version compatibility

  • Minimum: PostgreSQL 12+ (14+ recommended).
  • sslmode=require is mandatory on all connection strings if ssl is enforced by postgresql server.
ProviderRecommended service
AWSAmazon RDS for PostgreSQL, Multi-AZ.
AzureAzure Database for PostgreSQL — Flexible Server, with Entra ID passwordless auth.
GCPCloud SQL for PostgreSQL, regional HA.
OpenShift / on-premCrunchy Postgres for Kubernetes operator, or self-managed with streaming replication.
Topology: primary + at least one standby replica, multi-AZ. 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
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 for the full install context.

​
Backup & restore

​
Backup with pg_dump

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

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.

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