Skip to content

Database

Trimus stores everything except uploaded files in postgres. This page covers the operator-facing parts: setup, migrations, backups, connection-string tuning, and what to do when things go sideways.

Required version

PostgreSQL 16 or later with the pgvector extension available. Earlier versions might appear to work but are not tested.

The migrations rely on:

  • vector (pgvector) — required by migration 103 (note_embeddings). The CREATE EXTENSION IF NOT EXISTS vector will fail at boot if the extension isn't installed in the server, so use a Postgres image/build that ships pgvector.
  • pg_trgm — fuzzy issue search; auto‑created by migration 001 (the extension must be available).
  • gen_random_uuid() (pgcrypto) — also available in core Postgres 16.
  • JSONB and FOR UPDATE SKIP LOCKED — standard Postgres‑16 features (the worker pool claims jobs with SELECT … FOR UPDATE SKIP LOCKED).

Setting up postgres

Local development

The shipped docker-compose.yml runs pgvector/pgvector:pg16 — a drop‑in superset of stock postgres:16 with pgvector preinstalled:

user: trimus
password: trimus
db: trimus_dev
port: 5432

docker compose up -d

That's it. Set DATABASE_URL=postgres://trimus:trimus@localhost:5432/trimus_dev?sslmode=disable in your environment.

Production

Run postgres on its own host (or a managed service like RDS, Cloud SQL, Supabase, Neon). Trimus is happy with any of them that can load pgvector — RDS, Cloud SQL, Supabase, and Neon all support the extension; confirm it's enabled before first boot.

Minimum sizing for a small team:

  • CPU: 2 vCPU
  • RAM: 4 GB
  • Disk: 20 GB SSD, growing with run-event retention

Heavy heartbeat traffic stresses heartbeat_runs and run_events tables. Bump RAM and disk before you bump CPU.

Connection string

postgres://<user>:<password>@<host>:<port>/<db>?sslmode=<mode>

Recommended for production:

  • sslmode=require minimum.
  • sslmode=verify-full if you have the CA bundle (most managed providers offer this — set sslrootcert=path/to/ca.pem).
  • Pin a connection-pool size with the pgbouncer-friendly format if you front postgres with PgBouncer.

Username permissions: Trimus owns its schema. The user in DATABASE_URL needs CREATE / SELECT / INSERT / UPDATE / DELETE on all Trimus tables. The simplest setup is to make Trimus the owner of the database.

Migrations

Migrations live at internal/db/migrations/<NNN>_<name>.sql (176 files as of v5.0.2; highest is 176_worker_shared_models.sql), managed by goose.

The server applies migrations on boot

trimus-server runs db.RunMigrations at startup before the pool is opened, applying every pending migration up to the binary's latest. So an ordinary upgrade is just "restart the server" — you do not need a separate migration step in production. See upgrades.md.

trimus-seed is the dev seeder. It also runs goose up (so it's a convenient way to migrate a dev database) and then inserts default fixtures only if they don't already exist — idempotent, safe to re‑run:

DATABASE_URL='postgres://...' trimus-seed

For migrations only, from a source checkout:

goose -dir internal/db/migrations postgres "$DATABASE_URL" up

What's in trimus-seed's seed step

A default user, a sample company, two sample agents, three sample issues. Useful for poking at a fresh install. Skip the seed in production by writing a tiny migration-only wrapper, or just delete the seed company after first run.

Migration ordering

Migrations are forward-only; goose down is supported but not expected. Each migration has both -- +goose Up and -- +goose Down blocks so reverts are technically possible — but the operational expectation is "always roll forward". See upgrades.md for upgrade strategy.

Backups

See backup-and-restore.md for the complete operator runbook — pg_dump is only one of four components a full Trimus backup must capture (the other three are TRIMUS_HOME, TRIMUS_STORAGE_DIR, and the TRIMUS_ENCRYPTION_KEY). That document also covers the cold restore + verification procedure, common pitfalls, and a quarterly verification cadence.

A 30-second summary lives there. The TL;DR for postgres alone:

pg_dump -Fc "$DATABASE_URL" > /backups/trimus-$(date +%F).dump

But that alone is not a complete backup. Read the linked doc.

Routine maintenance

VACUUM / ANALYZE

Postgres autovacuum is on by default. Trust it. Only intervene if you see bloat reports or your hosting provider warns you.

Heavy tables to watch

  • heartbeat_runs — one row per run. Grows fast. Terminal rows are pruned by TRIMUS_HEARTBEAT_RUN_RETENTION_DAYS (default 90; set TRIMUS_HEARTBEAT_RUN_RETENTION=disable to opt out).
  • heartbeat_run_events — many rows per run; the streaming event log. Grows fastest. Pruned for succeeded runs by TRIMUS_RUN_EVENTS_RETENTION_DAYS (default 30); failed‑run events are retained indefinitely. ON DELETE CASCADE from heartbeat_runs means reaping a parent run also reaps its events.
  • activity — append-only audit log. Slow but steady growth.
  • comments — humans + agents, slow growth.
  • documents + document_revisions — growth depends on how often agents revise documents.

If postgres is creaking under one of these, the levers are:

  • Lower TRIMUS_RUN_EVENTS_RETENTION_DAYS and/or TRIMUS_HEARTBEAT_RUN_RETENTION_DAYS.
  • Use the Trash UI to soft-delete and then hard-delete old issues / projects / agents.
  • pg_dump + restore into a fresh DB if you really need to compact.

Connection pool tuning

trimus-server uses pgx's connection pool. Default settings are pgx's defaults — fine for most workloads. Overrideable via the DATABASE_URL query parameters:

DATABASE_URL=postgres://...?pool_max_conns=10&pool_min_conns=2

Watch for:

  • Connection-exhausted errors under heavy heartbeat load → bump pool_max_conns.
  • Postgres connection-limit-reached errors → check max_connections on the postgres side; lower pool_max_conns × number-of-trimus-instances if you're hitting it.

Common problems

"DATABASE_URL is required"

Set it. The server refuses to start without one.

"column 'xxx' does not exist"

Migrations are out of date — but the server applies them on boot, so this usually means the old binary is still running. Restart trimus-server on the new binary (or run trimus-seed / the goose incantation) against this database. Check the applied version with:

SELECT version_id FROM goose_db_version ORDER BY id DESC LIMIT 1;

Compare against the highest file in internal/db/migrations/ (176 as of v5.0.2).

"extension \"vector\" is not available"

The Postgres server doesn't have pgvector installed. Use the pgvector/pgvector:pg16 image (or enable the extension on your managed provider) and restart. Migration 103 runs CREATE EXTENSION … vector.

"duplicate key value violates unique constraint" on startup

Probably running two trimus-seed instances simultaneously, or the seed is running while the server is also running. Run seed once, serially.

Cross-company queries returning unexpectedly empty results

Trimus deliberately returns 404 (not 403) on cross-company access. If your admin queries look empty, check that you're scoped to the right company.

Where to next