PostgreSQL

Operational SQL database component used by the Foundation platform.

Agentic Friendly

Component Category

Data and storage / relational database

Component Description

PostgreSQL is the relational database used to store structured operational data across the platform.

Why It Is Used

In BullSequana AI Foundation, PostgreSQL supports platform services that require durable, transactional, and queryable storage for metadata, configuration, and application state.

Learn More

Deployment notes

PostgreSQL deploys as a CloudNativePG (CNPG) cluster. The operator installs in the cnpg-operator namespace at sync wave 5, the Barman Cloud plugin at sync wave 6, and the cluster itself in the cnpg namespace at sync wave 7, all in the common tier. A single instance runs PostgreSQL 17 with 50Gi of persistent storage. Two-replica PgBouncer poolers provide transaction-mode and session-mode connection pooling. Automated backups run daily at 02:00 UTC through the Barman Cloud plugin to the configured object storage (s3://cnpg-backups/cnpg), either the in-cluster Rook-Ceph RGW or the site's external S3 service, with 30-day retention. On Rook-Ceph, a post-sync job restarts the cluster when its backup credentials rotate.

Per-service database roles are provisioned declaratively through CNPG DatabaseRole custom resources. Each DatabaseRole references the shared cnpg-cluster, a password secret, and the desired capabilities (such as login or createdb). The operator reconciles roles directly — no imperative Jobs or psql commands are involved. A separate permissions Job per service grants database-level privileges after the role and database exist. All role and database resources deploy at negative sync waves so they are ready before the consuming application starts.

The platform revokes PUBLIC access on the databases it manages. On those, an ordinary role can connect only to a database it owns or was granted CONNECT on. PostgreSQL superusers keep administrative access to every database.

  • Platform services: each service's permissions Job revokes PUBLIC on its database before granting its role.
  • Existing clusters: a CNPG Sync hook revokes PUBLIC on the platform components' databases, such as llm_backend and keycloak, and on template1 and app. It then ends any session there from a role that may no longer connect.
  • Tenant databases: the tenant operator closes them itself.
  • postgres: this empty maintenance database stays open, because the tenant operator and the PgBouncer authentication query use it.
  • Databases created by hand on the cluster: the platform does not manage these. Revoke their PUBLIC access yourself with REVOKE ALL ON DATABASE <name> FROM PUBLIC;.

Deleting or pruning a DatabaseRole keeps the PostgreSQL role (databaseRoleReclaimPolicy: retain). Only the Kubernetes resource and its password secret are removed. Databases are kept the same way. GitOps never drops a role or a database: a platform operator does that in PostgreSQL.

Connection budget

The BullSequana AI API connects straight to the CNPG read-write service (cnpg-cluster-rw), not through PgBouncer. Each API process can open up to 55 connections:

  • Up to 15 for ordinary work (a pool of 5 plus 10 overflow).
  • Up to 40 for chat answers. Each answer being generated holds one for its whole length, as a transaction-level advisory lock that stops a second answer on the same chat. The tenant concurrent chat limits stay below this pool, so one tenant cannot take all of it.

Multiply by the number of API processes: the default two replicas can hold 110 connections. Add the other platform services, then compare the total with PostgreSQL's max_connections, which defaults to 400 (cnpg_postgresql_max_connections). If you route the API through PgBouncer, keep its query_timeout, idle_transaction_timeout and transaction_timeout disabled, because any of them can release a chat lock while the answer is still running.

Remove a component's database role

A retained role can still log in with its last password. When you remove a component for good, revoke the role, then drop it once its data is no longer needed. Run the statements as the postgres superuser on the current primary:

export PG_PRIMARY=$(kubectl -n cnpg get cluster cnpg-cluster -o jsonpath='{.status.currentPrimary}')
kubectl -n cnpg exec -it "$PG_PRIMARY" -c postgres -- psql -U postgres
-- 1. Revoke access and end open sessions.
ALTER ROLE <role> NOLOGIN;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = '<role>';

-- 2. When the data is no longer needed, run in each database where the role owns objects.
REASSIGN OWNED BY <role> TO postgres;
DROP OWNED BY <role>;

-- 3. Drop the role.
DROP ROLE <role>;

Take a backup before step 2. A role listed in pg_roles with no matching DatabaseRole resource (kubectl -n cnpg get databaserole) is left over from a removal that skipped these steps.

Interacts With

  • PgAdmin, as the operational administration console for PostgreSQL instances.
  • Keycloak, OpenFGA, Temporal, and Grafana, all of which rely on PostgreSQL-backed state in this platform.
  • Argo Workflows and higher-layer platform services, which also use PostgreSQL for durable metadata and operational data.

On this page