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 and VectorChord extensions for vector indexes and the pg_tokenizer and vchord_bm25 extensions for BM25 indexes. The database and the application secret together form the persistent state of an installation. Architecture lists the stored data.
The database holds the following schemas:
public. The Foundation4 objects, such as pipelines, documents, API keys and permissions, and the migration history in the tableschema_migrations. The settingdatabase.schemanames this schema.embeddings. A set of tables for each pipeline: classifications and their encryption keys, fragment text, text index entries and vectors. Foundation4 creates the tables of a pipeline when the pipeline is created. The settingdatabase.embeddings_schemanames this schema.bm25_catalogandtokenizer_catalog. The schemas of the extensionsvchord_bm25andpg_tokenizer, with the tokenizers that BM25 indexes use.
Deployment profiles
| Aspect | Evaluation | Production |
|---|---|---|
| Database | Bundled PostgreSQL chart, postgres.enabled: true | External PostgreSQL, postgres.enabled: false (the default) |
| Storage | Persistent volume claim of 8 GiB, kept until the claim is deleted | 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 | Stable while the volume claim exists | Stable for the life of the database cluster |
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. Every database connection sets the pgvector parameter hnsw.iterative_scan, which pgvector 0.8.0 introduced. |
| Extensions | vector, vchord, pg_tokenizer and vchord_bm25, installed on the server, listed in shared_preload_libraries, and created in the Foundation4 database by a superuser before installation, together with the bert tokenizer |
| Database | A dedicated database, owned by the Foundation4 database user |
| Schemas | public and embeddings by default. Foundation4 runs CREATE SCHEMA IF NOT EXISTS for both schemas at every migration and start. |
| Privileges | Ownership of the database, which grants CREATE on the database and, on PostgreSQL 15 and later, on the public schema; read access to bm25_catalog; read and write access to the tables and sequences of tokenizer_catalog, because creating a BM25 index registers the tokenizer of the index there; permission to run pg_control_system(), which stock PostgreSQL grants to every user |
| Connections | The connection budget described in Connection budget |
| Topology | One primary server. Foundation4 sends every read and write to one URL. |
A managed PostgreSQL service meets these requirements only when the service offers the four extensions and preloads the extensions. A service that revokes access to pg_control_system() needs GRANT EXECUTE ON FUNCTION pg_control_system() TO foundation4ai; from an administrator, because the system ID of the license reads the PostgreSQL system identifier through that function, as Licensing describes.
The following constraints apply to the database:
- Dedicated database. The Foundation4 database holds no tables of other applications. The migration command drops a one-column
schema_migrationstable of an earlier Foundation4 release, and stops withFailed applying migrations: the schema has a schema_migrations table of another tool. Give Foundation4 its own schema (database.schema).when the table belongs to another migration tool. - Schema names.
database.schemaanddatabase.embeddings_schemaare set before the first installation and keep their values afterwards. The migrations, the license checks, the API server and the workers use the configured names, anddatabase.schemais part of the system ID. - Schema ownership. For licenses of versions 1 and 2, the owner of the schema in
database.schemadoes not change after the license is installed, because the owner is part of the system ID of these versions. - Extension creation. The migrations run
CREATE EXTENSION IF NOT EXISTSfor the four extensions and create theberttokenizer when the tokenizer is absent. Only a superuser can create the extensions, so with the extensions and the tokenizer 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 a superuser connection URL from the shell variable ADMIN_DATABASE_URL, and the password is a value from openssl rand -hex 24.
-
Check that the server preloads the extension libraries:
psql "$ADMIN_DATABASE_URL" -c 'SHOW shared_preload_libraries'Expected result: a list that contains
vchord,vchord_bm25,vectorandpg_tokenizer. A change ofshared_preload_librariestakes effect after a restart of PostgreSQL. -
Create the user, the database, the extensions and the
berttokenizer, grant access to the extension schemas, and check the extension versions:psql "$ADMIN_DATABASE_URL" <<'SQL'CREATE ROLE foundation4ai LOGIN PASSWORD '<hexadecimal password>';CREATE DATABASE foundation4ai OWNER foundation4ai;\connect foundation4aiCREATE EXTENSION IF NOT EXISTS vector CASCADE;CREATE EXTENSION IF NOT EXISTS vchord CASCADE;CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;DO $$BEGINIF NOT EXISTS (SELECT 1 FROM tokenizer_catalog.tokenizer WHERE name = 'bert') THENPERFORM tokenizer_catalog.create_tokenizer('bert', 'model = "bert_base_uncased"');END IF;END$$;GRANT USAGE ON SCHEMA bm25_catalog, tokenizer_catalog TO PUBLIC;GRANT SELECT ON ALL TABLES IN SCHEMA bm25_catalog, tokenizer_catalog TO PUBLIC;GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tokenizer_catalog TO PUBLIC;GRANT USAGE ON ALL SEQUENCES IN SCHEMA tokenizer_catalog TO PUBLIC;SELECT extname, extversion FROM pg_extension ORDER BY extname;SQLExpected result:
CREATE ROLE,CREATE DATABASE, a line confirming the connection to the databasefoundation4ai, fourCREATE EXTENSIONlines,DO, fourGRANTlines, and a list that namespg_tokenizer,vchord,vchord_bm25andvector, with avectorversion of0.8.0or later. On a service that revokes access topg_control_system(), the administrator also runs theGRANTstatement of External database requirements. -
Set
POSTGRES_URLinfoundation4ai.secrets.env, as described in Connection URL, and leavepostgres.enabledatfalsein the values file.Expected result:
grep -c '^POSTGRES_URL=postgres://.*sslmode=verify-full' foundation4ai.secrets.envprints1, without showing the password. -
After the first application install, confirm that the migrations ran:
psql "$ADMIN_DATABASE_URL" -c '\connect foundation4ai' \-c 'SELECT version FROM schema_migrations ORDER BY version'Expected result: one row for each applied migration, starting with
m20250120_030022_initial_migration.
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 copies POSTGRES_URL into the Secret foundation4ai-core without changes, and the pods read the URL from the mounted file database.url. A password that contains characters with a meaning in URLs, such as @, /, #, ? or %, is therefore percent-encoded in POSTGRES_URL. 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 without database.ca_cert; with database.ca_cert, as verify-ca |
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 the file database.url, which the application release mounts from the Secret foundation4ai-core.
| 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, and with require |
database.schema | public | Schema of the Foundation4 objects. Set before the first installation; part of the system ID. |
database.embeddings_schema | embeddings | Schema of the pipeline tables. Set before the first installation. |
database.statement_timeout_seconds | 300 | Longest time a single SQL statement may run while the API server or a worker serves requests; 0 disables the limit. Migrations are not limited. |
database.log_level | app.log_level | Log level of the database driver |
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 at the upgrade of the application release, which restarts 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 minutes | 15 minutes |
| Maximum connection lifetime | 60 minutes | 60 minutes |
The pool closes a connection after 15 minutes without use and after 60 minutes at most, and opens new connections as requests need them.
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. A failure during the API key check returns HTTP 503 Unable to validate the API key; try again later, and a later failure returns HTTP 500 Internal server error. The API server log line of the failure contains Connection pool timed out.
The API server also refreshes the counts that the license limits use: the document and fragment counts every 10 seconds, from the live row counts of the PostgreSQL statistics without ANALYZE, and the other counts every 10 minutes. 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 and use 1 or 2 connections 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 2 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 from the core release, with postgres.enabled: true. The bundled database has the following characteristics:
- Image.
tensorchord/vchord-suite, PostgreSQL 18 with the extensionsvector,vchord,pg_tokenizerandvchord_bm25preloaded, as a StatefulSet namedfoundation4ai-core-postgreswith the podfoundation4ai-core-postgres-0. - Storage. The persistent volume claim
postgres-data-foundation4ai-core-postgres-0, of 8 GiB frompostgres.storage.requestedSize, with the storage class inpostgres.storage.classNameor the cluster default. Deleting or rescheduling the pod keeps every pipeline and document and the system ID.helm uninstallleaves the claim in place, and deleting the claim deletes the database. - Initialization. When the data directory is first created, the chart creates the database
POSTGRES_DATABASE, owned byPOSTGRES_USER, and runs the script inpostgres.customScriptsas the superuser. The script creates the four extensions and theberttokenizer and grants read access tobm25_catalogandtokenizer_catalog. The passwords are set only at that time. - Tokenizer grants. The script of the 1.1.0 chart grants no write access to
tokenizer_catalog, which every BM25 index needs, as described in BM25 index creation failing with a permission error. A superuser runs the two grants once in the bundled database, before the first BM25 index. - Connection. The core release builds the URL
postgres://<user>:<password>@foundation4ai-core-postgres/<database>?sslmode=disable, with each value percent-encoded.
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=>.
Run the tokenizer grants in the bundled database as the superuser postgres:
kubectl exec -i -n foundation4ai foundation4ai-core-postgres-0 -c postgres -- \
psql -U postgres -d foundation4ai <<'SQL'
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tokenizer_catalog TO PUBLIC;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA tokenizer_catalog TO PUBLIC;
SQL
Expected result: two GRANT lines.
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. An evaluation installation of an earlier release, whose bundled database had no volume claim, moves to the volume claim with a dump and restore, as Backup, restore and upgrades describes.
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-migrationapplies pending migrations before the license check and before the new pods start. Helm deletes the Job after the Job succeeds. - Startup. Each API server and worker, and the command
foundation4ai data check-models, 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, and no command reverts a migration. A Foundation4 release older than the database schema does not start: the migration Job reports
Failed applying migrations: the database was migrated by a newer Foundation4 release (<reason>), and the API server and the workers reportThe database was migrated by a newer Foundation4 release, which this version cannot run against.
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 PostgreSQL system identifier, and therefore the system ID of the license, as Licensing describes. foundation4ai license check in an API server pod confirms the license after a 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-migrationshowsFailed connecting to database: <reason>. -
Diagnosis. The reason names the cause, such as a host name that cannot be resolved, a refused connection, a rejected password, a TLS failure or a missing
pg_hba.confentry. 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=<PostgreSQL client image> -- \psql "host=<host> port=5432 dbname=foundation4ai user=foundation4ai sslmode=require"Any image with psql serves, such as the image in
postgres.imageof the core chart; in an air-gapped cluster, the image reference names the mirrored copy. Afoundation4ai=>prompt means that the network path and the credentials work. -
Cause. The database is unreachable from the namespace, the credentials in
POSTGRES_URLare wrong, the password contains characters that are not percent-encoded, or the certificate check fails. -
Resolution. Correct
POSTGRES_URLor the network path, apply the Secret, upgrade the core release and repeat the application install. -
Actions to avoid. Setting
sslmode=disableto pass the check on a network that requires encryption.
Migration Job failing on missing extensions
- Symptoms. The application install or upgrade stops at a failed hook, and the log of the migration Job ends with
The database needs the vector, vchord, pg_tokenizer and vchord_bm25 extensions, which only a superuser can create. - Diagnosis.
psql "$ADMIN_DATABASE_URL" -c 'SHOW shared_preload_libraries'shows the preloaded libraries, andpsql "$ADMIN_DATABASE_URL" -c '\connect foundation4ai' -c 'SELECT extname, extversion FROM pg_extension ORDER BY extname'shows the extensions of the Foundation4 database. - Cause. An extension is not installed on the server, not preloaded, or not created in the Foundation4 database, and the Foundation4 user cannot create the extension.
- Resolution. Install the missing extensions on the server, add the libraries to
shared_preload_librariesand restart PostgreSQL, run step 2 of Database preparation from\connect foundation4aion, then repeat the application install or upgrade. - Actions to avoid. Making the Foundation4 user a superuser so that the migrations create the extensions.
Migration Job failing on another migration table
- Symptoms. The log of the migration Job shows
Failed applying migrations: the schema has a schema_migrations table of another tool. Give Foundation4 its own schema (database.schema). - Diagnosis.
psql "$ADMIN_DATABASE_URL" -c '\connect foundation4ai' -c '\d schema_migrations'shows the table and the single column of the table. - Cause. The schema in
database.schemaholds the migration table of another application. - Resolution. Give Foundation4 a dedicated database, or set
database.schemato an unused schema before the first installation, then repeat the application install. - Actions to avoid. Dropping the table of the other application.
Migration Job failing with a permission error
- Symptoms. The application install stops at a failed hook, and the log of the migration Job shows
Failed creating schema <schema>orFailed applying migrationsfollowed by a PostgreSQLpermission deniedmessage. - Diagnosis. The message 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 EXISTSstatements or the creation of the tables fail. - Resolution. Transfer ownership with
ALTER DATABASE foundation4ai OWNER TO foundation4ai, then repeat the application install or upgrade. - Actions to avoid. Changing the owner of the schema in
database.schemaafter a license of version 1 or 2 is installed. The change produces a new system ID for these license versions.
BM25 index creation failing with a permission error
- Symptoms. Creating a pipeline with
full_text_searchset tobm25, or adding a BM25 index to a pipeline, returns HTTP 500Internal server error, while vector and full-text indexes are created. The API server log shows the errorpermission denied for table tokenizer. - Diagnosis.
kubectl logs -n foundation4ai deploy/foundation4ai-api-server -c servershows the error at the time of the request. In a superuser session in the Foundation4 database,\dp tokenizer_catalog.*lists the privileges on the tables of the schema. An entry forPUBLICthat holds onlyr(SELECT), such as=r/postgres, confirms the cause. - Cause. Each BM25 index registers its tokenizer in
tokenizer_catalogas the Foundation4 database user. A database prepared with read access to the schema only, as the initialization script of the 1.1.0 chart does, refuses the write. - Resolution. A superuser runs
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tokenizer_catalog TO PUBLIC;andGRANT USAGE ON ALL SEQUENCES IN SCHEMA tokenizer_catalog TO PUBLIC;once in the Foundation4 database: on an external database as in Database preparation, and on the bundled database as in Bundled PostgreSQL. The next create request succeeds without a restart. - Actions to avoid. Granting the Foundation4 database user superuser rights, where the two grants suffice.
HTTP 500 with a pool timeout under load
- Symptoms. Requests fail with HTTP 500
Internal server erroror HTTP 503Unable to validate the API key; try again later, often after the API server was scaled or during bulk ingestion, and the API server log containsConnection pool timed out. - Diagnosis.
kubectl logs -n foundation4ai deploy/foundation4ai-api-server -c servershows 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 withSHOW max_connections. - Cause. The requests of one API server need more connections than
database.pool_sizeallows, or PostgreSQL refuses new connections becausemax_connectionsis reached. - Resolution. Raise
database.pool_sizeor add API server replicas, within the connection budget, and raisemax_connectionswhen 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
sslmodechanges toverify-full, ordatabase.ca_certis set withsslmode=require, the migration Job reportsFailed connecting to database: <reason>with a TLS or certificate error. - Diagnosis.
kubectl get configmap foundation4ai-api-server -n foundation4ai -o yamlshows whether a configuration file setsdatabase.ca_cert. The host name inPOSTGRES_URLis compared with the names in the database certificate. - Cause. The database certificate is signed by a CA that
database.ca_certdoes not contain, or, withverify-full, the URL uses a host name or address that the certificate does not name. - Resolution. Set
database.ca_certto the CA certificate and use the host name that the certificate names. Then upgrade the application release, which restarts the API server and the workers. - Actions to avoid. Switching to
sslmode=requirewithoutdatabase.ca_cert, which encrypts the connection without checking the server certificate.