Skip to main content

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 table schema_migrations. The setting database.schema names 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 setting database.embeddings_schema names this schema.
  • bm25_catalog and tokenizer_catalog. The schemas of the extensions vchord_bm25 and pg_tokenizer, with the tokenizers that BM25 indexes use.

Deployment profiles​

AspectEvaluationProduction
DatabaseBundled PostgreSQL chart, postgres.enabled: trueExternal PostgreSQL, postgres.enabled: false (the default)
StoragePersistent volume claim of 8 GiB, kept until the claim is deletedManaged by the database administrator, with backups
Connection URLBuilt by the core release from the secrets filePOSTGRES_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 IDStable while the volume claim existsStable for the life of the database cluster

External database requirements​

RequirementValue
PostgreSQL version18, the version that the bundled chart and the Foundation4 test environment use. Earlier versions are untested.
pgvector version0.8.0 or later. Every database connection sets the pgvector parameter hnsw.iterative_scan, which pgvector 0.8.0 introduced.
Extensionsvector, 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
DatabaseA dedicated database, owned by the Foundation4 database user
Schemaspublic and embeddings by default. Foundation4 runs CREATE SCHEMA IF NOT EXISTS for both schemas at every migration and start.
PrivilegesOwnership 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
ConnectionsThe connection budget described in Connection budget
TopologyOne 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_migrations table of an earlier Foundation4 release, and stops with Failed 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.schema and database.embeddings_schema are 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, and database.schema is part of the system ID.
  • Schema ownership. For licenses of versions 1 and 2, the owner of the schema in database.schema does 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 EXISTS for the four extensions and create the bert tokenizer 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.

  1. 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, vector and pg_tokenizer. A change of shared_preload_libraries takes effect after a restart of PostgreSQL.

  2. Create the user, the database, the extensions and the bert tokenizer, 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 foundation4ai
    CREATE 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 $$
    BEGIN
    IF NOT EXISTS (SELECT 1 FROM tokenizer_catalog.tokenizer WHERE name = 'bert') THEN
    PERFORM 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;
    SQL

    Expected result: CREATE ROLE, CREATE DATABASE, a line confirming the connection to the database foundation4ai, four CREATE EXTENSION lines, DO, four GRANT lines, and a list that names pg_tokenizer, vchord, vchord_bm25 and vector, with a vector version of 0.8.0 or later. On a service that revokes access to pg_control_system(), the administrator also runs the GRANT statement of External database requirements.

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

  4. 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:

sslmodeEncryptionServer certificate check
disableNoneNone
prefer, the default when the parameter is absentWhen the server offers TLSNone
requireAlwaysNone without database.ca_cert; with database.ca_cert, as verify-ca
verify-caAlwaysCertificate signed by a trusted certificate authority
verify-fullAlwaysCertificate 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.

KeyDefaultDescription
database.urlNone; requiredConnection URL, set from POSTGRES_URL or by the bundled chart
database.pool_size3 in the chart; 5 when unsetMaximum number of connections of each API server process. Workers always use 1.
database.ca_certNoneCA certificate in PEM format, used with verify-ca and verify-full, and with require
database.schemapublicSchema of the Foundation4 objects. Set before the first installation; part of the system ID.
database.embeddings_schemaembeddingsSchema of the pipeline tables. Set before the first installation.
database.statement_timeout_seconds300Longest 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_levelapp.log_levelLog 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:

SettingAPI serverWorker
Maximum connectionsdatabase.pool_size: 3 in the chart, 5 when unset1
Minimum connections21
Connect timeout10 seconds10 seconds
Acquire timeout10 seconds10 seconds
Idle timeout15 minutes15 minutes
Maximum connection lifetime60 minutes60 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.

ScenarioAPI serversWorkersConnections
Chart defaults1 × 33 × 16, plus up to 2 during installation
4 API servers, 10 workers4 × 310 × 122, plus rolling update and Jobs
Autoscaling maximums of the chart100 × 3100 × 1400

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 extensions vector, vchord, pg_tokenizer and vchord_bm25 preloaded, as a StatefulSet named foundation4ai-core-postgres with the pod foundation4ai-core-postgres-0.
  • Storage. The persistent volume claim postgres-data-foundation4ai-core-postgres-0, of 8 GiB from postgres.storage.requestedSize, with the storage class in postgres.storage.className or the cluster default. Deleting or rescheduling the pod keeps every pipeline and document and the system ID. helm uninstall leaves 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 by POSTGRES_USER, and runs the script in postgres.customScripts as the superuser. The script creates the four extensions and the bert tokenizer and grants read access to bm25_catalog and tokenizer_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-migration applies 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 report The 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-migration shows Failed 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.conf entry. 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.image of the core chart; in an air-gapped cluster, the image reference names the mirrored copy. A foundation4ai=> prompt means that the network path and the credentials work.

  • Cause. The database is unreachable from the namespace, the credentials in POSTGRES_URL are wrong, the password contains characters that are not percent-encoded, 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.

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, and psql "$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_libraries and restart PostgreSQL, run step 2 of Database preparation from \connect foundation4ai on, 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.schema holds the migration table of another application.
  • Resolution. Give Foundation4 a dedicated database, or set database.schema to 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> or Failed applying migrations followed by a PostgreSQL permission denied message.
  • 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 EXISTS statements 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.schema after 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_search set to bm25, or adding a BM25 index to a pipeline, returns HTTP 500 Internal server error, while vector and full-text indexes are created. The API server log shows the error permission denied for table tokenizer.
  • Diagnosis. kubectl logs -n foundation4ai deploy/foundation4ai-api-server -c server shows 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 for PUBLIC that holds only r (SELECT), such as =r/postgres, confirms the cause.
  • Cause. Each BM25 index registers its tokenizer in tokenizer_catalog as 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; and GRANT 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 error or HTTP 503 Unable to validate the API key; try again later, often after the API server was scaled or during bulk ingestion, and the API server log contains Connection pool timed out.
  • 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, or database.ca_cert is set with sslmode=require, the migration Job reports Failed connecting to database: <reason> with 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, with verify-full, 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, which restarts the API server and the workers.
  • Actions to avoid. Switching to sslmode=require without database.ca_cert, which encrypts the connection without checking the server certificate.