Skip to content

Complete RAG with PostgreSQL and pgvector

This is where the separate lessons become one request path. You are not starting a second implementation. You are connecting the functions, schemas, and contracts you have already tested.

Follow a Source into the system

When ingestion discovers docs/rollbacks.md, it creates a stable Source ID and content hash. The Markdown parser preserves headings and lines. The chunker creates bounded Chunks with deterministic IDs. PostgreSQL stores those Chunks beside full-text vectors and embeddings.

flowchart LR
    S[Source file] --> I[Identify and parse]
    I --> C[Traceable Chunks]
    C --> P[(PostgreSQL)]
    P --> F[Full-text index]
    P --> V[pgvector index]

If the file changes, the Source ID stays the same, the content hash changes, and ingestion rebuilds its derived Chunks. If the file disappears, its Chunks disappear too.

Follow a Query back out

For How can I undo a bad release?, the request path is:

  1. validate the Query and access scope;
  2. run full-text and vector retrieval over eligible Sources;
  3. fuse the ranked Chunk IDs with RRF;
  4. rerank only if your evaluation kept that stage;
  5. select bounded Evidence with Source diversity;
  6. ask the model to Answer or abstain;
  7. verify cited Evidence IDs and map them to Source lines;
  8. record timings, statuses, and retrieval diagnostics.

A structured Query such as How many build minutes come with Pro? can take the structured lookup path instead of competing with prose.

flowchart LR
    Q[Query] --> F[Visibility and metadata filters]
    F --> L[Full-text]
    F --> V[pgvector]
    F --> S[Structured lookup]
    L --> R[RRF and optional rerank]
    V --> R
    R --> E[Evidence selection]
    S --> E
    E --> G[Generate or abstain]
    G --> C[Verified Citations]

Keep the boundaries in code

Use psycopg with explicit SQL for the PostgreSQL operations. Keep retrieval, Evidence selection, generation, and Citation verification in separate functions. The API and agent should call those functions rather than reimplementing them.

Run exact vector search before HNSW, then compare indexed recall and latency with the exact baseline. Run the 40 Evaluation Queries after every ranking or chunking change.

The system is complete when supported Queries return cited Answers, unanswerable Queries abstain, private Sources remain filtered, and every stage leaves enough diagnostics to explain a failure.

A fixed pipeline chooses retrieval in application code. In the next section, you will let a model choose among the same read-only methods.

Continue to Agentic Retrieval