PostgreSQL Driver

PostgreSQL is the default and recommended database driver for production. It delivers high concurrency, robust transactions, and native vector search through the pgvector extension.

Why PostgreSQL

High Concurrency

Connection pooling and row-level locking support 100+ concurrent workflows.

pgvector Integration

Store embeddings inside the same database with first-class support for `pgvector`.

Managed Services

Works seamlessly with RDS, Cloud SQL, Azure Database, Neon, Supabase, and more.

Configuration Options

PostgreSQL is selected when database.driver is omitted or set to postgres. You can configure via either a connection string or individual parameters.

compozy.yaml (PostgreSQL)

database:
  driver: postgres  # default, can be omitted
  host: localhost
  port: 5432
  user: compozy
  password: ${DB_PASSWORD}
  dbname: compozy
  sslmode: require
  max_open_conns: 25
  max_idle_conns: 5

knowledge:
  vector_dbs:
    - id: main
      provider: pgvector
      dimension: 1536

Connection String Support

Using a single DSN

database:
  driver: postgres
  conn_string: postgres://compozy:${DB_PASSWORD}@db.internal:5432/compozy?sslmode=require

Parameter Reference

Field	Required	Description
`host`	Yes*	Hostname or IP of the PostgreSQL server (*omit when using `conn_string`).
`port`	No	Server port (default `5432`).
`user`	Yes*	Database user used for all connections.
`password`	Yes*	Password or secret for the database user.
`dbname`	Yes*	Database name that stores Compozy metadata.
`sslmode`	No	TLS mode: `disable`, `require`, `verify-ca`, or `verify-full`.
`max_open_conns`	No	Upper bound for concurrent connections (defaults to driver auto-tuning).
`max_idle_conns`	No	Idle pool size to keep warm for bursty workloads.

Environment variables are supported via the CLI (COMPOZY_DB_*) and align with the configuration fields listed above.

pgvector for Knowledge Bases

Installing the pgvector extension is mandatory when using PostgreSQL as your vector database provider.

Enable pgvector (once per database)

CREATE EXTENSION IF NOT EXISTS vector;

Set knowledge.vector_dbs[].provider to pgvector.
Ensure dimension matches the embedding model size (e.g., 1536 for OpenAI text-embedding-3-large).
Use managed services like Neon or Supabase that ship with pgvector to avoid manual installation.

Performance Tuning

Connection Pooling
Tune max_open_conns and max_idle_conns to match database limits; start with 25/5 and monitor saturation.
Prepared Statements
Compozy uses prepared statements via pgx, reducing parse overhead for repetitive workflow queries.
Autovacuum
Keep autovacuum enabled; repositories write frequently and benefit from automatic cleanup.
Index Strategy
Migrations ship with indexes tuned for workflow/task lookups. Add partial indexes for custom workload filters if needed.

Production Deployment Guide

Provision the Database

Create a managed PostgreSQL instance with TLS enabled and provision the `compozy` database.

Install pgvector

Enable the `vector` extension and verify privileges for the Compozy user.

Configure Secrets

Store credentials in your secret manager and map them to `COMPOZY_DB_*` environment variables.

Run Migrations

Execute `compozy migrate up` or `compozy migrate status` during deployment pipelines.

Scale Intelligently

Monitor CPU, I/O, and connection pool metrics; enable read replicas if analytical workloads grow.

CLI Reference

# Override default driver (explicit but optional)
compozy start --db-driver=postgres

# Supply connection string on the command line
COMPOZY_DB_CONN_STRING="postgres://..." compozy start