Skip to article frontmatterSkip to article content
Site not loading correctly?

This may be due to an incorrect BASE_URL configuration. See the MyST Documentation for reference.

ADR-26039: pgvector as Ecosystem Database Standard

title: pgvector as Ecosystem Database Standard date: 2026-03-08 tags:

ADR-26039: pgvector as Ecosystem Database Standard

Date

2026-03-08

Status

proposed

Context

ADR-26038: Context Engineering as Core Design Principle establishes three-tier memory as the primary abstraction: working memory (context window), episodic memory (session history), and semantic memory (long-term knowledge in a vector store). The semantic and episodic tiers require a concrete storage decision: which database, how projects share it, and how vector and structured data coexist.

ADR-26032: Tiered Cognitive Memory: Procedural Skills vs. Declarative RAG separates procedural knowledge (skills — high-density logic injected into the context window) from declarative knowledge (RAG — low-density facts retrieved on demand). The declarative tier needs a persistent store that supports semantic search. Pure RAG for everything is rejected there because vector search is probabilistic — but for declarative knowledge (books, articles, historical logs), RAG is the right retrieval strategy. This ADR decides where that RAG data lives.

The ecosystem comprises multiple projects — documentation hubs, agent applications, generated repositories, and future tools — that need persistent storage. Today these projects use ad-hoc file-based storage (JSON, YAML). There is no shared database, which means no cross-project search, no transactional guarantees, and no consistent data lifecycle.

Evidence

S-26009: Gemini — Local Git Repo RAG Setup with pgvector documents a progression from simple RAG options (Open WebUI’s built-in ChromaDB, Aider’s repo map) to a server-side pgvector setup. The key finding: pgvector unifies structured and vector data in one database with ACID guarantees, SQL joins for metadata filtering, and schema-based isolation.

A-26006: Agent Runtime Architecture and RAG Infrastructure Decisions synthesizes this into a schema-per-project design and provides the scalability analysis:

A-26007: pgvector Viability Assessment and Logic Locality Decision provides external peer review (S-26011, Gemini 3 Flash Thinking) confirming the approach as production-ready. The critical reframing: pgvector solves referential drift — the root cause of most RAG system failures, where vector stores and metadata databases fall out of sync. ACID guarantees within one database eliminate this category of bugs entirely.

A-26005: Agentic OS Filesystem Architecture establishes the Postgres namespace model: schemas map to logical namespaces (like filesystem mount points), providing isolation without the operational overhead of multiple database instances.

Decision

We adopt PostgreSQL with pgvector as the single database for all ecosystem storage needs. One Postgres instance serves both structured data (session history, metadata, configuration) and vector data (document embeddings for RAG). This ADR is deployment-agnostic — Postgres may run as a system package (apt/dnf) or in a container; the deployment method is a separate decision.

1. Schema-Per-Project Isolation

Each ecosystem project gets its own Postgres schema:

CREATE EXTENSION vector;

-- Each project = one schema (logical namespace)
CREATE SCHEMA hub;           -- ai_engineering_book
CREATE SCHEMA project_alpha; -- any agent-created project

-- Document vectors for RAG
CREATE TABLE hub.doc_vectors (
    id serial PRIMARY KEY,
    file_path TEXT NOT NULL,
    content TEXT NOT NULL,
    commit_sha TEXT,
    embedding vector(768)  -- dimension matches embedding model
);

-- Structured data (session history, metadata) in the same database
CREATE TABLE project_alpha.session_history (
    id serial PRIMARY KEY,
    session_id UUID NOT NULL,
    timestamp TIMESTAMPTZ DEFAULT now(),
    role TEXT NOT NULL,
    content TEXT NOT NULL
);

Schema isolation provides: independent HNSW index rebuilds per project, instant cleanup via DROP SCHEMA CASCADE, RBAC per project via Postgres roles, and cross-project search by querying across schemas.

2. Hybrid Search as Default Retrieval

RAG queries combine vector similarity and keyword matching in a single SQL query:

SELECT file_path, content,
       1 - (embedding <=> $1::vector) AS similarity,
       ts_rank(to_tsvector('english', content), plainto_tsquery('english', $2)) AS keyword_rank
FROM hub.doc_vectors
WHERE to_tsvector('english', content) @@ plainto_tsquery('english', $2)
ORDER BY 0.7 * (1 - (embedding <=> $1::vector))
       + 0.3 * ts_rank(to_tsvector('english', content), plainto_tsquery('english', $2)) DESC
LIMIT 10;

The weight balance (0.7 vector / 0.3 keyword) is a starting default, tunable per use case. Postgres’s built-in tsvector provides keyword search without an additional service.

3. Configurable Embedding Model

The vector dimension (768 in examples) is tied to the embedding model. The ecosystem does not mandate a specific model:

Consequences

Positive

Negative / Risks

Alternatives

References

Participants

  1. Vadim Rudakov

  2. Claude Opus 4.6

Adr
ADR-26038: Context Engineering as Core Design Principle
Adr
ADR-26040: Podman Kube YAML as Deployment Standard