Database

This page describes the PostgreSQL database that Foundation4 uses as the primary data store: the requirements for an external database, the connection settings, the connection budget, the bundled database of the evaluation profile and the schema migrations. Operators and database administrators who prepare or run the database of an installation use this page.

Database role

Foundation4 stores every pipeline, document, version, fragment, vector, API key and permission in one PostgreSQL database with the pgvector extension. The database and the application secret together form the persistent state of an installation. Architecture lists the stored data.

The database holds two schemas:

• public. The Foundation4 objects, such as pipelines, documents, API keys and permissions, and the migration history in the table schema_migrations.
• embeddings. A set of tables for each pipeline: classifications and their encryption keys, fragment text, full-text index entries and vectors. Foundation4 creates the tables of a pipeline when the pipeline is created.

Deployment profiles

Aspect	Evaluation	Production
Database	Bundled PostgreSQL chart, postgres.enabled: true	External PostgreSQL, postgres.enabled: false (the default)
Storage	Temporary pod volume, lost when the pod is deleted or rescheduled	Managed by the database administrator, with backups
Connection URL	Built by the core release from the secrets file	POSTGRES_URL in the secrets file
Transport Layer Security (TLS)	None (sslmode=disable)	sslmode=verify-full, with database.ca_cert for a private certificate authority
System ID	New whenever the database pod is recreated	Stable for the life of the database

External database requirements

Requirement	Value
PostgreSQL version	18, the version that the bundled chart and the Foundation4 test environment use. Earlier versions are untested.
pgvector version	0.8.0 or later. Similarity search sets the pgvector parameter hnsw.iterative_scan, which pgvector 0.8.0 introduced. The test environment uses 0.8.1.
Extension	vector, created in the Foundation4 database by an administrator before installation
Database	A dedicated database, owned by the Foundation4 database user
Schemas	public and embeddings. Foundation4 creates embeddings and runs CREATE SCHEMA IF NOT EXISTS for both schemas at every start.
Privileges	Ownership of the database, which grants CREATE on the database and, on PostgreSQL 15 and later, on the public schema
Connections	The connection budget described in Connection budget
Topology	One primary server. Foundation4 sends every read and write to one URL.

The following constraints apply to the database:

• Dedicated database. The migration command drops a table named schema_migrations that has a single column, the format of an earlier Foundation4 migration table and of some other migration tools. The Foundation4 database therefore holds no tables of other applications.
• Default schema names. database.schema and database.embeddings_schema keep the defaults public and embeddings. Some migrations, the license checks and the document count use the default names regardless of the configuration.
• Schema ownership. The owner of the public schema does not change after the license is installed, because the owner is part of the system ID. Licensing describes the system ID.
• Extension creation. The migration runs CREATE EXTENSION IF NOT EXISTS vector. With the extension already present, the Foundation4 user needs no right to create extensions.

Database preparation

A database administrator prepares the external database before the first installation. The commands read an administrative connection URL from the shell variable ADMIN_DATABASE_URL, and the password is a value from openssl rand -hex 24.

1. Create the user, the database and the extension, and check the pgvector version:

   psql "$ADMIN_DATABASE_URL" <<'SQL'
   CREATE ROLE foundation4ai LOGIN PASSWORD '<hexadecimal password>';
   CREATE DATABASE foundation4ai OWNER foundation4ai;
   \connect foundation4ai
   CREATE EXTENSION IF NOT EXISTS vector;
   SELECT extversion FROM pg_extension WHERE extname = 'vector';
   SQL

   Expected result: CREATE ROLE, CREATE DATABASE, a line confirming the connection to the database foundation4ai, CREATE EXTENSION, and an extversion of 0.8.0 or later.

2. Set POSTGRES_URL in foundation4ai.secrets.env, as described in Connection URL, and leave postgres.enabled at false in the values file.

   Expected result: grep -c '^POSTGRES_URL=postgres://.*sslmode=verify-full' foundation4ai.secrets.env prints 1, without showing the password.

3. After the first application install, confirm that the migration ran:

   kubectl logs -n foundation4ai job/foundation4ai-api-server-db-migration

   Expected result: Migrated to latest schema.

The keys POSTGRES_DATABASE, POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_SUPERUSER_PASSWORD configure the bundled PostgreSQL only, and the core release does not read the keys while postgres.enabled is false.

Connection URL

The connection URL of an external database has the following form:

postgres://foundation4ai:<hexadecimal password>@<host>:5432/foundation4ai?sslmode=verify-full

The core release inserts the URL into the configuration of the pods without changes, and the password is not URL-encoded anywhere. A hexadecimal password needs no encoding. Secrets and keys describes the secrets file.

The sslmode parameter controls TLS between Foundation4 and PostgreSQL:

sslmode	Encryption	Server certificate check
disable	None	None
prefer, the default when the parameter is absent	When the server offers TLS	None
require	Always	None, even when database.ca_cert is set
verify-ca	Always	Certificate signed by a trusted certificate authority
verify-full	Always	Certificate signed by a trusted certificate authority, and the host name matches the certificate

A production installation uses verify-full. When the database certificate is signed by a private certificate authority (CA), database.ca_cert holds the CA certificate in Privacy-Enhanced Mail (PEM) format.

Configuration keys

The database keys are set through a configuration file in api-server.configs. Foundation4 loads the configuration files in file name order, so a file named f4ai-10-database.yaml overrides the chart defaults in f4ai-00-defaults.yaml. The connection URL comes from f4ai-zz-database-credentials.yaml, which the application release writes from the Secret.

Key	Default	Description
database.url	None; required	Connection URL, set from POSTGRES_URL or by the bundled chart
database.pool_size	3 in the chart; 5 when unset	Maximum number of connections of each API server process. Workers always use 1.
database.ca_cert	None	CA certificate in PEM format, used with verify-ca and verify-full
database.schema	public	Schema of the Foundation4 objects. Keep the default.
database.embeddings_schema	embeddings	Schema of the pipeline tables. Keep the default.

The following values file sets the CA certificate and the pool size:

api-server:
  configs:
    f4ai-10-database.yaml: |
      database:
        pool_size: 3
        ca_cert: |
          -----BEGIN CERTIFICATE-----
          <certificate body>
          -----END CERTIFICATE-----

The installation Jobs and the pods load the same files. A change takes effect after an upgrade of the application release and a restart of the API server and the workers, as described in Secrets and keys. Configuration reference lists every configuration key.

Connection pool

Each API server and each worker holds a pool of database connections with the following settings:

Setting	API server	Worker
Maximum connections	database.pool_size: 3 in the chart, 5 when unset	1
Minimum connections	2	1
Connect timeout	10 seconds	10 seconds
Acquire timeout	10 seconds	10 seconds
Idle timeout	15 seconds	15 seconds
Maximum connection lifetime	15 seconds	15 seconds

The maximum lifetime closes each connection after 15 seconds at most, and the pool opens new connections as requests need them. PostgreSQL therefore authenticates new connections continuously, and connection logging on the database server records each one.

A request that finds every connection of the pool in use waits for a free connection. The API server logs a warning about slow connection acquisition, and after 10 seconds the request fails with HTTP 500 and a message that contains Connection pool timed out.

The API server also refreshes the counts that the license limits use. At startup and every 3 hours, each API server runs ANALYZE on the documents table and, when the license limits fragments, on the fragment tables of every pipeline. Licensing describes the counts.

Connection budget

The number of database connections of an installation follows from the replica counts:

connections = (API server replicas × pool size) + (worker replicas × 1) + installation Jobs

The installation Jobs run one at a time during an install or upgrade: the master key Job uses up to 3 connections, and the migration and license check Jobs use 1 or 2 each. A rolling update adds pods while the update runs: with the Kubernetes default strategy, which the charts keep, 25 percent of the replica count of each Deployment, rounded up.

Scenario	API servers	Workers	Connections
Chart defaults	1 × 3	3 × 1	6, plus up to 3 during installation
4 API servers, 10 workers	4 × 3	10 × 1	22, plus rolling update and Jobs
Autoscaling maximums of the chart	100 × 3	100 × 1	400

The budget stays below the PostgreSQL setting max_connections, which defaults to 100, less the connections reserved for superusers and the connections of backup, monitoring and administration tools. Autoscaling of the API server raises the connection count by the pool size for each replica, so the maximum replica count is set from the budget. Scaling and performance describes replica counts and autoscaling.

Bundled PostgreSQL

The evaluation profile installs PostgreSQL with pgvector from the core release, with postgres.enabled: true. The bundled database has the following characteristics:

• Image. pgvector/pgvector:pg18-trixie, as a StatefulSet named foundation4ai-core-postgres with the pod foundation4ai-core-postgres-0.
• Storage. The data directory is a temporary pod volume. Deleting or rescheduling the pod deletes every pipeline and document, and the recreated database has a new system ID that needs a new license. The postgres.persistence values of the core chart have no effect.
• Initialization. When the database is created, the chart creates the database POSTGRES_DATABASE, owned by POSTGRES_USER, and the vector extension. The passwords are set only at that time.
• Connection. The core release builds the URL postgres://<user>:<password>@foundation4ai-core-postgres/<database>?sslmode=disable.

Open a SQL session in the bundled database:

kubectl exec -it -n foundation4ai foundation4ai-core-postgres-0 -c postgres -- \
  psql -U foundation4ai -d foundation4ai

Expected result: the prompt foundation4ai=>.

The bundled database is not used for production. An evaluation database is moved to an external database with a logical dump and restore, which produces a new system ID and needs a new license.

Schema migrations

Foundation4 changes the database schema through numbered migrations, recorded in the table schema_migrations:

• Migration Job. At every install and upgrade of the application release, the Job foundation4ai-api-server-db-migration applies pending migrations before the license check and before the new pods start.
• Startup. Each API server, worker and master key Job also applies pending migrations when the process starts.
• Transactions. Each migration runs in one transaction. A failed migration leaves no partial changes, and the migrations applied before the failure remain applied.
• Direction. Migrations only move forward. No command reverts a migration, and a Foundation4 version older than the database schema does not start.

Backup, restore and upgrades describes upgrades and rollback.

Replicas and failover

Foundation4 connects to one database URL and sends every query, including searches, to that server. Read replicas receive no Foundation4 traffic.

High availability comes from the database service: a failover that moves the primary role behind a stable host name keeps the URL unchanged. A failover to a physical replica keeps the catalog, and is therefore expected to keep the system ID. The license check log confirms the system ID after the first failover test, as described in Licensing.

Troubleshooting

Migration Job failing with a connection error

• Symptoms. The application install stops at a failed pre-install or pre-upgrade hook, and kubectl logs -n foundation4ai job/foundation4ai-api-server-db-migration shows Failed connecting to database.

• Diagnosis. The message does not state the cause. A temporary pod tests the connection with the same host and user, and psql asks for the password:

  kubectl run psql-check -n foundation4ai --rm -it --restart=Never \
    --image=pgvector/pgvector:pg18-trixie -- \
    psql "host=<host> port=5432 dbname=foundation4ai user=foundation4ai sslmode=require"

  In an air-gapped cluster, the image reference names the mirrored copy of the image. A foundation4ai=> prompt means that the network path and the credentials work. Otherwise psql names the cause, such as a host name that cannot be resolved, a refused connection, a rejected password or a missing pg_hba.conf entry.

• Cause. The database is unreachable from the namespace, the credentials in POSTGRES_URL are wrong, the password contains characters that break the URL, or the certificate check fails.

• Resolution. Correct POSTGRES_URL or the network path, apply the Secret, upgrade the core release and repeat the application install.

• Actions to avoid. Setting sslmode=disable to pass the check on a network that requires encryption.

API server failing at startup with a permission error

• Symptoms. The migration Job completes, but the server container restarts, and the log shows Failed to create database connection followed by a PostgreSQL permission denied message.
• Diagnosis. kubectl logs -n foundation4ai deploy/foundation4ai-api-server -c server --previous names the object, such as the database or the schema public. psql "$ADMIN_DATABASE_URL" -c '\l foundation4ai' shows the owner of the database.
• Cause. The Foundation4 user does not own the database, so the CREATE SCHEMA IF NOT EXISTS statements at startup or the creation of the pipeline tables fail.
• Resolution. Transfer ownership with ALTER DATABASE foundation4ai OWNER TO foundation4ai, then restart the API server and the workers.
• Actions to avoid. Changing the owner of the public schema to fix the error after the license is installed. The change produces a new system ID.

HTTP 500 with a pool timeout under load

• Symptoms. Requests fail with HTTP 500 and a message that contains Connection pool timed out, often after the API server was scaled or during bulk ingestion.
• Diagnosis. kubectl logs -n foundation4ai deploy/foundation4ai-api-server -c server shows warnings about slow connection acquisition before the failures. psql "$ADMIN_DATABASE_URL" -c "SELECT count(*) FROM pg_stat_activity WHERE datname = 'foundation4ai'" shows the connection count, compared with SHOW max_connections.
• Cause. The requests of one API server need more connections than database.pool_size allows, or PostgreSQL refuses new connections because max_connections is reached.
• Resolution. Raise database.pool_size or add API server replicas, within the connection budget, and raise max_connections when the budget exceeds the setting.
• Actions to avoid. Raising the pool size and the replica count together without recomputing the budget, which moves the failure to PostgreSQL.

Certificate errors after enabling certificate verification

• Symptoms. After sslmode changes to verify-full, the migration Job reports Failed connecting to database, and the API server logs a TLS or certificate error.
• Diagnosis. kubectl get configmap foundation4ai-api-server -n foundation4ai -o yaml shows whether a configuration file sets database.ca_cert. The host name in POSTGRES_URL is compared with the names in the database certificate.
• Cause. The database certificate is signed by a CA that database.ca_cert does not contain, or the URL uses a host name or address that the certificate does not name.
• Resolution. Set database.ca_cert to the CA certificate and use the host name that the certificate names. Then upgrade the application release and restart the API server and the workers.
• Actions to avoid. Switching to sslmode=require, which encrypts the connection without checking the server certificate.