Full-Text Search for Agent Knowledge Bases: PostgreSQL tsvector and Trigram Search

Why Agents Need Full-Text Search

AI agents frequently need to retrieve relevant information from a knowledge base before generating a response. While vector similarity search handles semantic queries well, full-text search excels at exact term matching, phrase queries, and structured document retrieval. A production agent system typically combines both approaches.

PostgreSQL provides two complementary text search systems built in — no external services required. The tsvector/tsquery system handles linguistic full-text search with stemming and ranking. The pg_trgm extension handles fuzzy matching and typo tolerance. Together, they cover the full spectrum of text retrieval needs.

Setting Up tsvector Search

Create a knowledge base table with a generated tsvector column:

CREATE TABLE knowledge_articles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    category TEXT,
    source TEXT,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(content, '')), 'B')
    ) STORED,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- GIN index on the search vector
CREATE INDEX idx_knowledge_search
    ON knowledge_articles USING gin(search_vector);

The GENERATED ALWAYS AS clause automatically maintains the search vector when title or content changes. The setweight function assigns weight A to titles and B to content, so title matches rank higher.

Querying with tsquery

Search the knowledge base with ranked results:

SELECT
    id,
    title,
    ts_rank(search_vector, query) AS rank,
    ts_headline('english', content, query,
        'StartSel=<mark>, StopSel=</mark>, MaxFragments=3'
    ) AS snippet
FROM knowledge_articles,
     to_tsquery('english', 'agent & memory & retrieval') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

The ts_headline function generates highlighted snippets showing where matches occur — useful for displaying search results to users or providing context to the agent.

Building Search Queries from User Input

User input rarely matches tsquery syntax. Parse natural language into a proper tsquery:

import asyncpg


def build_tsquery(user_input: str) -> str:
    """Convert user input into a tsquery string."""
    words = user_input.strip().split()
    sanitized = [
        w.replace("'", "").replace("\\", "")
        for w in words
        if w.strip()
    ]
    if not sanitized:
        return ""
    # Join with & for AND semantics, use :* for prefix matching
    return " & ".join(f"{word}:*" for word in sanitized)


async def search_knowledge(
    pool: asyncpg.Pool, query_text: str, limit: int = 10
) -> list[dict]:
    tsquery = build_tsquery(query_text)
    if not tsquery:
        return []

    rows = await pool.fetch(
        """
        SELECT id, title, category,
            ts_rank(search_vector, to_tsquery('english', $1)) AS rank,
            ts_headline('english', content, to_tsquery('english', $1),
                'MaxFragments=2, MinWords=20, MaxWords=60'
            ) AS snippet
        FROM knowledge_articles
        WHERE search_vector @@ to_tsquery('english', $1)
        ORDER BY rank DESC
        LIMIT $2
        """,
        tsquery,
        limit,
    )
    return [dict(r) for r in rows]

The :* suffix enables prefix matching, so "retriev" matches "retrieval", "retrieve", and "retrieving". This provides a more forgiving search experience.

Trigram Search for Fuzzy Matching

Enable the pg_trgm extension for similarity-based search that handles typos:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN trigram index on title
CREATE INDEX idx_knowledge_title_trgm
    ON knowledge_articles USING gin(title gin_trgm_ops);

-- Fuzzy search: find articles with titles similar to input
SELECT id, title,
    similarity(title, 'agnet memroy') AS sim_score
FROM knowledge_articles
WHERE title % 'agnet memroy'  -- % operator uses similarity threshold
ORDER BY sim_score DESC
LIMIT 5;

The default similarity threshold is 0.3. Adjust it for your use case:

SET pg_trgm.similarity_threshold = 0.2;  -- More permissive

Hybrid Search: Combining tsvector and Trigram

For the best user experience, combine both approaches. Use tsvector for precise ranked results and fall back to trigram when tsvector returns no matches:

async def hybrid_search(
    pool: asyncpg.Pool, query_text: str, limit: int = 10
) -> list[dict]:
    tsquery = build_tsquery(query_text)

    rows = await pool.fetch(
        """
        WITH fts AS (
            SELECT id, title, category,
                ts_rank(search_vector, to_tsquery('english', $1)) AS score,
                'fts' AS match_type
            FROM knowledge_articles
            WHERE search_vector @@ to_tsquery('english', $1)
        ),
        trgm AS (
            SELECT id, title, category,
                similarity(title || ' ' || content, $2) AS score,
                'trigram' AS match_type
            FROM knowledge_articles
            WHERE (title || ' ' || content) % $2
              AND id NOT IN (SELECT id FROM fts)
        )
        SELECT * FROM fts
        UNION ALL
        SELECT * FROM trgm
        ORDER BY score DESC
        LIMIT $3
        """,
        tsquery,
        query_text,
        limit,
    )
    return [dict(r) for r in rows]

This query first collects full-text matches, then adds trigram matches that were not already found, giving you both precision and typo tolerance.

Integration with Agent Tools

Expose the search as a tool that your agent can call:

from agents import Agent, function_tool


@function_tool
async def search_knowledge_base(query: str) -> str:
    """Search the internal knowledge base for relevant articles."""
    pool = get_db_pool()
    results = await hybrid_search(pool, query, limit=5)
    if not results:
        return "No relevant articles found."
    return "\n\n".join(
        f"**{r['title']}** ({r['category']})\nScore: {r['score']:.2f}"
        for r in results
    )

FAQ

How does PostgreSQL full-text search compare to Elasticsearch?

PostgreSQL full-text search handles most knowledge base scenarios well — up to millions of documents with sub-second query times. Elasticsearch is warranted when you need distributed search across billions of documents, complex faceted navigation, or real-time log analysis. For agent knowledge bases under ten million documents, PostgreSQL avoids the operational complexity of a separate search cluster.

Can I combine full-text search with vector similarity search?

Yes. Store both a tsvector column and a vector embedding column on the same table. Query with a CTE that scores both approaches and combines them with weighted ranking. This hybrid retrieval pattern consistently outperforms either approach alone in RAG (Retrieval Augmented Generation) benchmarks.

How do I keep the search index updated when articles change?

The GENERATED ALWAYS AS ... STORED column updates automatically on INSERT and UPDATE. For bulk imports, insert the data normally and the search vector regenerates. There is no separate indexing step required — PostgreSQL maintains the GIN index incrementally.

---

#FullTextSearch #PostgreSQL #Tsvector #KnowledgeBase #AIAgents #AgenticAI #LearnAI #AIEngineering