AkurAI-Notes / docs/adr/001-postgres-pgvector.md

Latest change b77c3b08fbd20b54aab76a630a41f7b53ddde2c8 - OLI-113: ADR 001 (Postgres+pgvector), ADR 008 (hindsight post-mortem) by Ólafur Búi Ólafsson

# ADR 001: Postgres 17 + pgvector as the single store

Status: Accepted (Platform Engineer, 2026-09-30) · Epic OLI-112 · Task OLI-113

## Context
Code index and semantic memory need vectors, lexical search and relational metadata with transactional consistency. A prior store (hindsight, see ADR 008) lost trust because durability and restore were never proven.

## Decision
1. One dedicated Postgres 17 cluster with pgvector (`pgvector/pgvector:pg17`), unit `akurai-pg.service`, loopback 127.0.0.1:5433, separate from the hindsight volume.
2. Durability settings fixed on: `fsync`, `synchronous_commit`, `full_page_writes`, data checksums, WAL archiving (`archive_mode=on`) to a separate WAL directory.
3. Nightly logical dump (`akurai-pg-dump.timer`) and weekly restore drill (`akurai-pg-drill.timer`); a backup counts only if the drill restores it.
4. Extra extensions (e.g. ParadeDB) are deferred, see ADR 002.
5. Embeddings and rerank are served by llama.cpp on loopback (8081/8082), outside the DB.

## Consequences
One system to back up, monitor and restore. Vector scale limits are those of pgvector (HNSW, RAM-bound); revisit if corpus outgrows the 3g container limit. Rollback: stop the unit; data stays under `~/Projects/data/akurai-pg`.