Skip to main content

Database configuration

Synjar uses PostgreSQL with the pgvector extension for storing documents and embeddings.

PostgreSQL setup​

Minimum version​

PostgreSQL 14 or higher is required.

Creating the database​

CREATE DATABASE synjar;
CREATE USER synjar WITH PASSWORD 'your-secure-password';
GRANT ALL PRIVILEGES ON DATABASE synjar TO synjar;

pgvector extension​

The pgvector extension is required for semantic search:

CREATE EXTENSION IF NOT EXISTS vector;

Synjar migrations automatically create this if permissions allow.

Connection string​

Format:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE

Example:

DATABASE_URL="postgresql://synjar:password@localhost:5432/synjar"

Connection options​

ParameterDescription
schema=publicDatabase schema (default: public)
sslmode=requireRequire SSL (recommended for remote)
connection_limit=10Max connections

Example with options:

DATABASE_URL="postgresql://synjar:pass@host:5432/synjar?schema=public&sslmode=require"

Connection pooling​

For production, use a connection pooler like PgBouncer:

# Connect to PgBouncer instead of PostgreSQL directly
DATABASE_URL="postgresql://synjar:pass@pgbouncer:6432/synjar"

PgBouncer configuration​

[databases]
synjar = host=postgres-host dbname=synjar

[pgbouncer]
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

Security​

SSL/TLS​

For remote connections, enable SSL:

DATABASE_URL="postgresql://user:pass@host:5432/synjar?sslmode=require"

SSL modes:

  • disable - No SSL
  • require - Use SSL, don't verify certificate
  • verify-ca - Verify CA certificate
  • verify-full - Verify CA and hostname

Row-Level Security (RLS)​

Synjar uses PostgreSQL RLS for workspace isolation. This is configured automatically during migrations.

Migrations​

Running migrations​

# With Docker Compose
docker-compose exec synjar npx prisma migrate deploy

# With Node.js
npx prisma migrate deploy

Migration status​

npx prisma migrate status

Backup​

See Backup guide for detailed backup procedures.

Quick backup:

pg_dump -h localhost -U synjar -d synjar > backup.sql

Performance tuning​

# postgresql.conf
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 64MB
maintenance_work_mem = 128MB

Vacuum​

Schedule regular vacuum:

-- Analyze for query optimization
VACUUM ANALYZE;

See also​