Postgres + pgvector

Requirements

Postgres 15 is the floor because Recall uses MERGE (15+) and JSONB path operators introduced in that version. pgvector 0.8 adds HNSW build-time tuning we expose as configuration.

Installing pgvector

The official pgvector/pgvector Docker image ships the extension preinstalled. For managed Postgres:

• AWS RDS — pgvector available via Aurora/RDS Postgres 15.5+. Enable with CREATE EXTENSION vector;
• GCP Cloud SQL — available via flag enablement on Postgres 15+
• Supabase — preinstalled, enable in dashboard
• Neon — preinstalled
• Self-managed — apt install postgresql-15-pgvector (Debian/Ubuntu) or build from source

Verify after install:

CREATE EXTENSION IF NOT EXISTS vector;
SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';
-- Expect: vector | 0.8.0

Connection setup

Point Recall at the database via DATABASE_URL:

DATABASE_URL=postgres://recall:recall@db.internal:5432/recall?sslmode=require

For multi-tenant deployments with per-namespace database attach (PUT /v1/namespaces/{id}/database), the default URL is the control-plane database. Per-namespace data planes are configured at runtime via the namespace admin API and resolved by the TenantPoolManager.

Schema migrations

Migrations are forward-only and shipped inside the binary. They run automatically on recall-server boot. To apply manually:

recall-cli db migrate --database-url "$DATABASE_URL"
# → applied 12 migrations: 0001_schema.sql, …, 0003_obs_caller.sql

Migrations live at crates/recall-storage/src/postgres/migrations/. There are no down migrations — rollback is "restore from backup."

Schema overview

The data plane has eight first-class tables:

Every table has the same audit columns: id (UUID v7), created_at, updated_at, deleted_at (soft delete).

HNSW indexing

The memories.embedding column is vector(1536) indexed by HNSW. HNSW outperforms IVFFlat on read latency at the cost of build time and RAM.

Default index parameters at create time:

CREATE INDEX memories_embedding_hnsw_idx
  ON memories USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

• m = 16 — number of bidirectional links per node. Higher = better recall, more RAM.
• ef_construction = 64 — search depth during build. Higher = better index quality, slower build.

At query time, Recall sets ef_search per request via SET LOCAL hnsw.ef_search = 80. Higher recall, slightly slower response. The pipeline doesn't expose this knob to callers — it's tuned per release.

Full-text search (BM25)

The memories.tsv column is a generated tsvector. Updates happen via trigger when content changes. The BM25 retriever uses Postgres's built-in ts_rank_cd for scoring.

Language: English by default ('english'::regconfig). Multilingual setups should change the trigger to use 'simple' or a language-detection layer at the application level — there's no automatic language detection in Postgres.

Temporal index (BRIN)

Events use a BRIN index on event_at. BRIN is space-efficient (~1 KB per million rows) and well-suited to time-series queries that scan recent windows. Random-access lookups on event_at are slower than B-tree, but the temporal retriever queries are always range-scanned.

The TenantPoolManager

For deployments with per-namespace database attach, the TenantPoolManager (in recall-storage) maintains a HashMap<NamespaceId, PgPool>. When a request arrives, the auth middleware resolves the namespace from the API key, the manager looks up the pool, and the pipeline runs against that pool.

Pools are constructed lazily and cached. A namespace whose database_url has not been accessed in 30 minutes is evicted to free connections. Reconnects are transparent on next use.

The control-plane database (DATABASE_URL) holds the namespaces table itself — it's how the manager learns which namespace points where. Don't repurpose the control-plane DB for namespace data.

Backup posture

Backup the Postgres database. Recall stores nothing on local disk. Recommended:

• Nightly pg_dump --format=custom --compress=9 to S3 / GCS / Azure Blob
• WAL archiving for point-in-time recovery (RPO < 5 minutes)
• Restore drill quarterly — practice the recovery, not just the backup

Detailed cadence and restore steps in Backups.

Performance tuning

For deployments past ~10M memories:

-- Increase shared_buffers to fit HNSW + hot data
ALTER SYSTEM SET shared_buffers = '8GB';

-- Bigger work_mem for sort + RRF post-processing
ALTER SYSTEM SET work_mem = '32MB';

-- Allow more parallel workers for full-text + temporal scans
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_worker_processes = 16;

-- pgvector ef_search runtime default — Recall sets per-query, but
-- this is the floor when it doesn't.
ALTER SYSTEM SET hnsw.ef_search = 40;

SELECT pg_reload_conf();