Architecting Code-Agent Retrieval & Memory: Beyond Naive Vector Search to Single-Store PostgreSQL 16 Hybrid Systems

1. The Dual-Store Problem: Why Single-Store PostgreSQL Wins for Agents

A standard design pattern in early AI prototypes is the dual-store architecture: relational data (users, repositories, commit history, permissions, audit logs) lives in PostgreSQL, while vector embeddings live in an external vector SaaS (Pinecone, Chroma, external Qdrant clusters). For a static documentation site or read-only FAQ bot, this setup is tolerable. For an active, mutating coding agent, it introduces immediate operational friction:

In enterprise multi-tenant deployments, co-locating vectors in PostgreSQL enables native Row-Level Security (RLS) so that retrieval queries cannot accidentally leak code across tenant or repository boundaries:

-- Multi-Tenant Row-Level Security (RLS) Pattern
ALTER TABLE blobs ENABLE ROW LEVEL SECURITY;
ALTER TABLE code_symbols ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy ON blobs
    FOR ALL
    TO app_user
    USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

2. Production Indexing Mechanics: HNSW Realities & Memory Sizing

For active agent memory and frequently updated codebases, selecting the right vector index family is critical. pgvector supports two index implementations: IVFFlat and HNSW.

*Note on Vector Graph Recall: In vector indexing, HNSW graph recall measures the probability of retrieving the true exact nearest neighbors in the high-dimensional vector space for a given ef_search setting. It is an intrinsic metric of graph traversal completeness, completely distinct from downstream retrieval accuracy or question-answering precision (which depend on embedding semantics and query formulation).

Production Schema & Storage Layout

In krusch-context-mcp, Git blobs and extracted code symbols utilize 1,024-dimensional dense vectors (matching models such as baai/bge-large-en-v1.5 or Gemini embeddings). The schema couples relational keys, full-text inverted indexes, and HNSW cosine distance:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Codebase Blobs: Relational Metadata, Full-Text Tsvector, and Dense Vector
CREATE TABLE IF NOT EXISTS blobs (
    id VARCHAR(40) PRIMARY KEY,              -- SHA1 Git Object Hash
    repository_id INTEGER REFERENCES repositories(id) ON DELETE CASCADE,
    file_name VARCHAR(255),
    file_path TEXT NOT NULL,
    summary TEXT,
    content BYTEA NULL,                     -- Pointer or raw source storage
    size INTEGER NOT NULL,
    embedding vector(1024),                 -- Dense semantic coordinate
    last_seen_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    -- Full-text cover-density tsvector (Postgres native ranking)
    tsv tsvector GENERATED ALWAYS AS (
        to_tsvector('simple', coalesce(file_name, '') || ' ' || 
                              coalesce(file_path, '') || ' ' || 
                              coalesce(summary, ''))
    ) STORED
);

-- 1. HNSW Vector Index with tuned connection parameters
CREATE INDEX IF NOT EXISTS blobs_embedding_hnsw_idx 
ON blobs USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- 2. GIN Inverted Index for fast text term lookups
CREATE INDEX IF NOT EXISTS blobs_tsv_gin_idx 
ON blobs USING gin(tsv);

-- 3. Composite B-Tree Index for single-pass relational filtering
CREATE INDEX IF NOT EXISTS idx_blobs_repo_path 
ON blobs (repository_id, file_path);

3. Code Chunking: The Zero-Dependency Structural Lexer

Fixed-size token chunking (e.g., sliding 500-token windows with 50-token overlap) is fundamentally flawed for source code. Code has rigid syntactic hierarchies. Slicing on arbitrary token counts causes two immediate failure modes:

The Engineering Trade-Off: Pragmatic Lexer Heuristic vs Full AST / Tree-sitter

A frequent critique of code chunkers is the tension between a lightweight regex/balanced-brace scanner and a full Concrete Syntax Tree (CST) compiled via Tree-sitter or language compiler frontends (Babel, Roslyn, PyAST). It is important to be precise about what krusch-context-mcp does and does not do:

• Why not Tree-sitter everywhere? Tree-sitter provides 100% grammar compliance, but compiling native C/C++ grammars via node-gyp creates severe operational friction for cross-platform, zero-dependency MCP tools across diverse developer environments (Windows, Alpine Linux, macOS). It also adds multi-megabyte daemon RSS memory overhead for multi-language parser grammars.
• The Pragmatic Trade-off: krusch-context-mcp implements a zero-dependency state-machine balanced-brace scanner (src/ast-chunker.js) with comment and string literal masking. It extracts discrete function, class, and method blocks with ~92% accuracy across standard JavaScript, TypeScript, and Go definitions, eliminating the catastrophic mid-function cuts of naive 500-token sliding windows.
• Where the Heuristic Falls Down: It is fundamentally a structural heuristic, not an AST. Calling a regex-based brace scanner an "AST" is an overclaim. Complex macros, indentation-scoped languages (Python), and nested closures in template literals require graduating to an out-of-process Tree-sitter or Language Server Protocol (LSP) daemon.

In krusch-context-mcp, the balanced-brace scanner operates as follows:

/**
 * State-Machine Balanced Brace Scanner (from krusch-context-mcp ast-chunker.js)
 * Pragmatic trade-off: 0 native bindings, instant cold startup, ~92% symbol fidelity.
 */
function findMatchingBrace(content, startIdx) {
    let depth = 0;
    let inString = null;
    let inComment = false;
    
    for (let i = startIdx; i < content.length; i++) {
        const char = content[i];
        const next = content[i + 1];

        // Mask out string literals to avoid counting interior braces
        if (inString) {
            if (char === '\\') { i++; } // Skip escape sequence
            else if (char === inString) { inString = null; }
            continue;
        }

        // Mask out single-line and multi-line comments
        if (inComment) {
            if (inComment === '//' && char === '\n') { inComment = false; }
            else if (inComment === '/*' && char === '*' && next === '/') {
                inComment = false;
                i++;
            }
            continue;
        }

        if (char === '"' || char === "'" || char === '`') {
            inString = char;
            continue;
        }
        if (char === '/' && next === '/') { inComment = '//'; i++; continue; }
        if (char === '/' && next === '*') { inComment = '/*'; i++; continue; }

        // Track structural scope depth
        if (char === '{') depth++;
        else if (char === '}') {
            depth--;
            if (depth === 0) return i;
        }
    }
    return -1;
}

Two-Tier Indexing: Coarse File Blobs vs Fine-Grained Symbols

A subtle but critical failure mode in code search is indexing the wrong granularity into full-text vectors. Notice in Section 2 that blobs.tsv is generated from file_name, file_path, and summary. If a developer searches for an exact internal identifier like auth_v2_session_token, a summary-level index will fail if the identifier was not explicitly surfaced in the file-level summary.

In krusch-context-mcp, the structural lexer extracts discrete symbols into the code_symbols table, where full-text cover-density vectors index the exact symbol name, signature, and body content:

CREATE TABLE IF NOT EXISTS code_symbols (
    id SERIAL PRIMARY KEY,
    blob_id VARCHAR(40) REFERENCES blobs(id) ON DELETE CASCADE,
    repository_id INTEGER REFERENCES repositories(id) ON DELETE CASCADE,
    file_path TEXT NOT NULL,
    symbol_name VARCHAR(255) NOT NULL,
    symbol_type VARCHAR(50) NOT NULL,    -- 'function', 'class', 'method', 'route'
    start_line INTEGER NOT NULL,
    end_line INTEGER NOT NULL,
    signature TEXT,                      -- e.g. "export async function searchBlobs(...)"
    content TEXT,                        -- Fully contained body block
    embedding vector(1024),
    -- Full-text cover-density index indexing exact symbol names, signatures & code bodies
    tsv tsvector GENERATED ALWAYS AS (
        to_tsvector('simple', coalesce(symbol_name, '') || ' ' || 
                              coalesce(signature, '') || ' ' || 
                              coalesce(content, ''))
    ) STORED,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_code_symbols_embedding ON code_symbols USING hnsw (embedding vector_cosine_ops);
CREATE INDEX idx_code_symbols_tsv ON code_symbols USING gin(tsv);

When searching for exact code identifiers, the engine queries code_symbols where exact tokens match directly against method signatures and bodies. blobs serves as the coarse routing and file-summary plane, while code_symbols provides surgical identifier grounding.

4. Production Hybrid Search: RRF in SQL & The BM25 Distinction

A pure dense vector search stack suffers from exact identifier blindness: if an agent searches for CVE-2024-38077, res.clearCookie, or a specific database migration hash, a dense vector embedder maps those tokens to generic conceptual embeddings for "security vulnerabilities" or "cookie auth", missing the exact symbol.

Conversely, pure lexical search suffers from paraphrase blindness: searching for "how does our system mitigate memory leakage during long conversations" returns zero matches against a function named compactEpisodicHistory().

The solution is Hybrid Search via Reciprocal Rank Fusion (RRF) in SQL.

PostgreSQL Full-Text: Cover Density (`ts_rank_cd`) vs Okapi BM25

In industry blogs, PostgreSQL's full-text search is frequently mislabeled as "BM25". It is important to be precise:

• PostgreSQL ts_rank_cd: Uses Cover Density Ranking based on term proximity and frequency within document extents. It rewards documents where query terms appear close together, but does not implement BM25's non-linear term saturation ($k_1$) or length normalization ($b$).
• Okapi BM25: The established standard in Lucene and Elasticsearch. In PostgreSQL, achieving true Okapi BM25 requires specialized extensions like ParadeDB (pg_search).

For code-agent retrieval, ts_rank_cd combined with simple dictionaries is remarkably effective because symbol names and identifiers are short, making cover density an excellent proxy for exact matches without requiring external search daemons.

The Production PostgreSQL 16 Hybrid Fusion Query

Here is the actual query executed in production by searchBlobs() in krusch-context-mcp/src/git-engine.js:

/**
 * Production PostgreSQL 16 Hybrid Retrieval Query (from krusch-context-mcp git-engine.js)
 * Combines pgvector HNSW Cosine Distance with tsvector Cover-Density Ranking
 * Evaluated via Reciprocal Rank Fusion (RRF) with Exponential Recency Decay
 */
WITH dense_matches AS (
    -- 1. Dense Semantic Nearest Neighbors via HNSW Index
    SELECT 
        b.id,
        (1 - (b.embedding <=> $1::vector)) AS dense_sim,
        ROW_NUMBER() OVER (ORDER BY (b.embedding <=> $1::vector) ASC) AS dense_rank
    FROM blobs b
    WHERE b.embedding IS NOT NULL
      AND ($2::integer IS NULL OR b.repository_id = $2)
    LIMIT $3 * 4
),
lexical_matches AS (
    -- 2. Lexical Keyword Matches via tsvector GIN Index (Cover Density)
    SELECT 
        b.id,
        ts_rank_cd(b.tsv, plainto_tsquery('simple', $4)) AS bm25_score,
        ROW_NUMBER() OVER (ORDER BY ts_rank_cd(b.tsv, plainto_tsquery('simple', $4)) DESC) AS bm25_rank
    FROM blobs b
    WHERE b.tsv @@ plainto_tsquery('simple', $4)
      AND ($2::integer IS NULL OR b.repository_id = $2)
    LIMIT $3 * 4
),
fused AS (
    -- 3. Reciprocal Rank Fusion (k = 60) Merging Dense & Lexical Strata
    SELECT 
        COALESCE(d.id, l.id) AS id,
        (
            COALESCE(1.0 / (60.0 + d.dense_rank), 0.0) +
            COALESCE(1.0 / (60.0 + l.bm25_rank), 0.0)
        ) AS rrf_score,
        COALESCE(d.dense_sim, 0.0) AS dense_sim,
        COALESCE(l.bm25_score, 0.0) AS bm25_score
    FROM dense_matches d
    FULL OUTER JOIN lexical_matches l ON d.id = l.id
)
SELECT 
    b.id,
    b.repository_id,
    r.name AS project,
    COALESCE(b.summary, substring(encode(b.content, 'escape') from 1 for 500)) AS summary,
    b.storage_mode,
    b.last_seen_at,
    COALESCE(b.file_name, b.id) AS file_name,
    b.file_path,
    -- 4. Mathematical Temporal Recency Decay: e^(-0.01 * age_in_days)
    (f.rrf_score * exp(-0.01 * EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - COALESCE(b.last_seen_at, b.created_at))) / 86400.0)) AS similarity,
    f.dense_sim,
    f.bm25_score
FROM fused f
JOIN blobs b ON b.id = f.id
JOIN repositories r ON r.id = b.repository_id
ORDER BY similarity DESC
LIMIT $3;

5. Multi-Hop Graph Walks: Bridging Vector Seeds to Dependency Edges

Semantic embeddings surface isolated candidate files. But a coding agent tasked with refactoring an API endpoint cannot work on an island: it requires the route handler, the service it calls, the data models it imports, and the test suite that validates it.

Instead of hoping the embedding model miraculously bundles these disparate files into the top-5 nearest neighbors, krusch-context-mcp extracts dependency imports during indexing and performs a bounded relational graph walk:

CREATE TABLE IF NOT EXISTS code_symbol_edges (
    id SERIAL PRIMARY KEY,
    repository_id INTEGER REFERENCES repositories(id) ON DELETE CASCADE,
    source_path TEXT NOT NULL,
    target_path TEXT NOT NULL,
    relation VARCHAR(50) NOT NULL,       -- 'imports', 'requires', 'references'
    symbols TEXT[],                      -- e.g. ['pool', 'query', 'authMiddleware']
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_edges_lookup ON code_symbol_edges(repository_id, source_path);

6. Re-Ranking & Diversity Pruning: Greedy Jaccard Minimal Cover (MMR Approximation)

In large codebases, the top 20 retrieval results frequently suffer from semantic redundancy: 8 of the top 10 items might be identical logging wrappers, boilerplate error handlers, or repeated test fixtures. Consuming context tokens on repetitive boilerplate degrades LLM reasoning.

While recent information retrieval literature explores LLM-based setwise reranking, production code-agent memory planes require deterministic, sub-millisecond execution. In krusch-context-mcp, we implement a greedy token Jaccard diversity pruning filter (a lightweight lexical approximation of Maximal Marginal Relevance) to suppress near-duplicates without incurring LLM call latencies:

/**
 * Greedy Jaccard Minimal Covering Filter (MMR Approximation)
 * Suppresses near-duplicate candidates sharing high token Jaccard similarity.
 */
export function selectMinimalCoveringSet(candidates, targetCount = 6, redundancyThreshold = 0.88) {
    if (!candidates || candidates.length === 0) return [];

    const sorted = [...candidates].sort((a, b) => (b.score || 0) - (a.score || 0));
    const selected = [];

    for (const item of sorted) {
        if (selected.length >= targetCount) break;

        let isRedundant = false;
        for (const sel of selected) {
            // Compute token-level lexical overlap
            const overlap = tokenJaccardSimilarity(item.content, sel.content);
            if (overlap >= redundancyThreshold) {
                isRedundant = true;
                break;
            }
        }

        if (!isRedundant) {
            selected.push(item);
        }
    }

    return selected;
}

Setting the Jaccard redundancy threshold to 0.88 safely suppresses copy-pasted boilerplate without discarding legitimate method overloads or distinct implementations.

7. Server-Side Token Budget Packing: Preventing Context Truncation

The standard failure mode in client-driven RAG is returning an array of JSON objects to the calling agent and delegating context management to client scripts. When the retrieved documents exceed the LLM's context window, naive string truncation cuts code mid-expression, creating syntax errors and hallucinations.

In our architecture, the retrieval server acts as a Deterministic Token Budget Accumulator:

/**
 * Server-Side Greedy Token Budget Accumulator (packTokenBudget)
 * Packs ranked candidates under an exact token ceiling with safety margin.
 */
export function packTokenBudget(items, limitTokens = 4000) {
    const sorted = [...items].sort((a, b) => b.score - a.score);
    
    let currentTokens = 0;
    const packed = [];
    
    for (const item of sorted) {
        const header = `### [${item.type.toUpperCase()}] ${item.title} (Score: ${(item.score * 100).toFixed(1)}%)\n`;
        const cleanedBody = (item.content || '').trim() + '\n\n';
        
        // Character heuristic with 10% safety buffer (3.6 chars/token)
        const itemTokens = Math.ceil((header.length + cleanedBody.length) / 3.6);
        
        // Hard budget guard: prevent overflow even if the very first item is oversized
        if (currentTokens + itemTokens > limitTokens) {
            if (packed.length === 0) {
                // First item exceeds budget on its own: slice safely rather than blowing prompt
                const maxChars = Math.floor(limitTokens * 3.6) - header.length;
                if (maxChars > 200) {
                    packed.push(header + cleanedBody.substring(0, maxChars) + '\n...[TRUNCATED TO FIT BUDGET]');
                    currentTokens = limitTokens;
                }
            }
            break; // Stop packing once budget is reached
        }
        
        packed.push(header + cleanedBody);
        currentTokens += itemTokens;
    }

    return {
        contextText: packed.join('---\n'),
        packedCount: packed.length,
        totalTokens: currentTokens
    };
}

8. Architectural Trade-Off Matrix: A Steelmanned Comparison

A serious systems evaluation does not compare against unconfigured toy scripts or strawmen. Instead, we compare single-store PostgreSQL (whether self-hosted or managed via platforms like Neon and Supabase) against mature production alternatives: dual-store vector SaaS topologies and dedicated distributed search clusters:

9. Operational Scaling Limits: When to Graduate from PostgreSQL

A senior architect does not treat "1M–5M vectors" as an immutable law. In production, the viability of PostgreSQL + pgvector is governed by three continuous engineering variables:

10. Empirical Retrieval Evaluation: Directional Smoke-Test Ablations

A serious retrieval claim requires an empirical evaluation protocol, not anecdotal laptop timings. In krusch-context-mcp, retrieval performance is audited via a formal benchmark harness (scripts/eval_accuracy.js) comparing three retrieval strategies:

1. Pure Lexical Search: PostgreSQL ts_rank_cd cover-density ranking over blobs.tsv.
2. Pure Dense Vector: Cosine distance on 1,024-d bge-large embeddings (blobs.embedding <=> vector).
3. Hybrid Reciprocal Rank Fusion: krusch-context-mcp's core searchBlobs() fusing dense ranks, lexical ranks, and temporal recency decay ($k=60$).

A. Third-Party Codebase Benchmark: `expressjs/express`

To ensure evaluation validity outside our own repositories, we evaluated on the public expressjs/express repository (206 files, 167 indexed blobs, 3,354 extracted code symbols) using 10 frozen queries (5 abstract semantic concepts + 5 exact code identifiers):

B. In-Corpus Architectural Ablation: Local Tooling Monorepo

Across the local repository stack (pg-git, krusch-context-mcp, krusch-cascade-router) indexing 190 content-addressed blobs, 14 frozen ablation queries were evaluated to test dense degradation on exact configuration flags, CVE identifiers, and architecture patterns:

11. Conclusion & Open Source Reproducibility

High-reliability AI systems do not require complex, brittle multi-database topologies for code retrieval. By pairing single-store PostgreSQL 16 + pgvector with structural symbol extraction, SQL-native Reciprocal Rank Fusion, dependency graph expansion, and server-side token budget packing, developers achieve state-of-the-art code retrieval within an atomic, ACID-compliant database.

All benchmarks, frozen test fixtures, and source code are open-source and reproducible:

• Open-Source Repository: krusch-context-mcp on GitHub
• Evaluation Harness & Fixtures: docs/EVALS.md & scripts/eval_accuracy.js
• Companion Architecture: Postgres as the State Plane: Why Side Effects Require a Transaction Manager
• Cloud Elasticity Deep Dive: Local-First Core, Hybrid Cloud Elasticity: Decoupling Agent Memory