Choosing a Metadata Store for Kubeflow Pipelines: PostgreSQL, MySQL, or SQLite

Problem: Where to Persist Pipeline Metadata?

Kubeflow Pipelines (KFP) needs a relational database to store experiment data, run status, and artifact references. The default Helm chart ships with SQLite, but production workloads often demand a more robust, concurrent store. This guide helps you decide between PostgreSQL, MySQL, and SQLite, then shows how to deploy the chosen database with Helm and verify that KFP writes to it.

Decision Constraints

• Scale: Number of concurrent users, pipelines, and data volume.
• Availability: Desired uptime and fault‑tolerance.
• Operational Overhead: Existing database expertise, backup, and monitoring tooling.
• Compatibility: The KFP version you are installing and the JDBC driver it expects.
• Security: Credential storage, network isolation, and encryption at rest.

Supported Options

Database	ACID Support	Concurrency	JSON Handling	Operational Footprint
PostgreSQL	Full ACID	High – row‑level locks, MVCC	Native JSONB, efficient indexing	Requires a separate cluster or managed service
MySQL	Full ACID (InnoDB)	Good – row‑level locks, but lock escalation can impact heavy writes	JSON support added in 5.7, less mature than PostgreSQL	Compatible with existing MySQL infra
SQLite	ACID on a single process	Single‑writer, multi‑reader – not safe for concurrent writes	Limited JSON support via TEXT	No external service; embedded in the KFP pod

Trade‑Offs

PostgreSQL

• Best for large, multi‑user, production deployments.
• Strong JSONB support allows storing complex metadata.
• Requires external provisioning, backup, and monitoring.
• Higher operational cost but provides scalability and resilience.

MySQL

• Similar transactional guarantees to PostgreSQL.
• Existing MySQL infrastructure can reduce setup effort.
• JSON handling is adequate but less feature‑rich than PostgreSQL.
• Lock semantics differ; be cautious if you rely on row‑level isolation.

SQLite

• Zero‑configuration – no separate service.
• Single‑writer limitation makes it unsuitable for concurrent pipeline runs.
• Data is stored in a single file; backup is a simple file copy.
• Best for local dev, demos, or very low‑traffic test environments.

Concrete Helm Deployment Example

Below we illustrate how to install KFP with an external PostgreSQL instance. The same pattern applies to MySQL; just adjust the driver and connection string.

# Create a Kubernetes Secret to hold the DB password
kubectl create secret generic pg-pass --from-literal=password='YOUR_PG_PASSWORD' -n kubeflow

# Deploy KFP with Helm, pointing to the external PostgreSQL
helm install kfp kubeflow/pipelines \
  --namespace kubeflow \
  --create-namespace \
  --set metadataStore.type=postgresql \
  --set metadataStore.host=pg-host.example.com \
  --set metadataStore.port=5432 \
  --set metadataStore.database=kfp \
  --set metadataStore.user=postgres \
  --set metadataStore.password=$(kubectl get secret pg-pass -n kubeflow -o jsonpath='{.data.password}' | base64 --decode) \
  --set metadataStore.driver=com.mysql.jdbc.Driver \
  --set metadataStore.jndi=jdbc:postgresql://pg-host.example.com:5432/kfp

Key points:

• Use a Kubernetes Secret to avoid hard‑coding credentials.
• Replace pg-host.example.com with the actual host or service name of your PostgreSQL instance.
• The driver value must match the JDBC driver bundled with the KFP Helm chart; for PostgreSQL it is typically org.postgresql.Driver (adjust if using a different chart version).
• The jndi URL follows the JDBC format for PostgreSQL: jdbc:postgresql://host:port/db.

Verification Steps

1. Confirm the PostgreSQL pod (if you are running it inside the cluster) is running:

   kubectl get pods -n kubeflow | grep postgres
   

2. Test connectivity from the KFP pod to the database:

   # Find a running KFP pod
   KFP_POD=$(kubectl get pods -n kubeflow -l app=katib-controller -o jsonpath='{.items[0].metadata.name}')
   
   # Execute a simple query inside the pod
   kubectl exec -it $KFP_POD -n kubeflow -- psql -U postgres -c "SELECT 1;"
   

   Expect a successful SELECT 1; result. If you see connection errors, double‑check the host, port, and credentials.

3. Run a quick pipeline to generate metadata entries:

   # Apply the built‑in quickstart example
   kubectl apply -f https://raw.githubusercontent.com/kubeflow/pipelines/master/docs/examples/quickstart.yaml
   
   # Wait for the pipeline to finish
   kubectl get pods -n kubeflow | grep kfp-quickstart
   

4. Query the metadata_store table to see the new run record:

   kubectl exec -it $KFP_POD -n kubeflow -- psql -U postgres -c "SELECT * FROM metadata_store WHERE name='Quickstart';"
   

   Seeing a row indicates KFP is writing to the external PostgreSQL instance.

Operational Caveats

• Back up the database regularly; use snapshots or logical dumps.
• For high availability, consider a managed PostgreSQL service with replication.
• Ensure the JDBC driver version matches the database server version to avoid driver‑compatibility failures.
• When using SQLite, remember that concurrent writes will fail; avoid running multiple pipeline workers on the same node.

Conclusion

When scaling Kubeflow Pipelines, the choice of metadata store is critical. PostgreSQL offers the strongest feature set and scalability, MySQL is a solid alternative if you already own MySQL infrastructure, and SQLite remains a lightweight option for isolated test environments. By configuring Helm correctly and verifying connectivity, you can confidently deploy KFP with the database that matches your operational constraints.