Courses
Agent Harness with Spring AI
Part 3: Memory and Retrieval
11. Embeddings and hybrid storage

Chapter 11: Embeddings and hybrid storage in PostgreSQL

Use one PostgreSQL database with VectorChord for vectors, and its BM25 extension for keywords. The table holds the text, the metadata, the embedding, and the keyword vector.

CREATE TABLE IF NOT EXISTS corpus (
    id       TEXT PRIMARY KEY,
    service  TEXT NOT NULL,
    doc_type TEXT NOT NULL,
    source   TEXT NOT NULL,
    text     TEXT NOT NULL,
    emb      vector(1024),
    bm25     bm25vector
);

BGE-M3 runs in LM Studio and produces 1024-dimension vectors. Split documents into chunks of about 1200 characters with 150 overlapping. Embed each chunk, then insert it. A startup check confirms the vector size matches the table.

Figure 11.1. The ingest path for one document. The figure leaves out how the bm25vector column is filled; see the Verify note below.
chunk 1 · ~1200 charschunk 2 · ~1200 charschunk 3 · ~1200 chars150 shared150 shared
Figure 11.2. Overlap keeps a sentence that crosses a chunk border searchable from both sides. Not to scale.

Verify: Create the BM25 index with the statement in the VectorChord-BM25 repository. The reference article does not show it.

docs