PostgreSQL RAG pipeline

Data model

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  source_uri text NOT NULL,
  source_version text NOT NULL,
  title text NOT NULL,
  access_scope text[] NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, source_uri, source_version)
);

CREATE TABLE document_chunks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  ordinal integer NOT NULL CHECK (ordinal >= 0),
  content text NOT NULL,
  token_count integer NOT NULL CHECK (token_count > 0),
  embedding vector(1536) NOT NULL,
  embedding_model text NOT NULL,
  search_vector tsvector GENERATED ALWAYS AS
    (to_tsvector('simple', content)) STORED,
  UNIQUE (document_id, ordinal)
);

Ingestion must be replayable

Store source version, chunker version, embedding model, and dimension. Derive deterministic document/chunk keys for idempotent upsert. When changing models, build a new embedding column or table and dual-write during rebuild; never mix incomparable vectors in one index.

Retrieval order

1. Filter tenant, permission, document state, and time in SQL.
2. Produce bounded candidates independently from full text and vectors.
3. Merge with rank fusion or application reranking.
4. Fetch a small number of neighboring chunks for continuity.
5. Return source URI, version, chunk id, and excerpt for citation.

An illustrative vector candidate query:

SELECT
  c.id,
  c.document_id,
  c.ordinal,
  c.content,
  c.embedding <=> $1::vector AS distance
FROM document_chunks AS c
JOIN documents AS d ON d.id = c.document_id
WHERE d.tenant_id = $2
  AND d.access_scope && $3::text[]
ORDER BY c.embedding <=> $1::vector
LIMIT 40;

Index type and parameters depend on scale, recall, latency, and write pattern. Establish an exact-search baseline before evaluating HNSW or IVFFlat; demo data is not enough.

Approximate indexes and filters

HNSW/IVFFlat normally apply tenant, ACL, and other predicates after the index produces candidates, so a query can return fewer rows than its LIMIT. That is not a reason to weaken authorization filters. pgvector 0.8.0+ iterative scans can expand candidate scanning; large tenants may also justify partitions, partial indexes, or separate tables. Measure recall@k in real tenant/ACL buckets for every design.

See Vector search in production and the official pgvector filtering guidance for index DDL, parameters, and evaluation.

Security and citation

Authorization predicates stay inside SQL/RLS so the database applies them before rows leave the boundary. Never return global candidates to the application and filter there; unauthorized text can leak through logs, caches, or model context. Final answers carry verifiable citations and report insufficient evidence when retrieval is weak.