Reference: pgvector RAG Store

CREATE EXTENSION IF NOT EXISTS vector;
 
CREATE TABLE document_chunks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id uuid NOT NULL,
  document_id uuid NOT NULL,
  chunk_index int NOT NULL,
  content text NOT NULL,
  embedding vector(1536) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, document_id, chunk_index)
);
 
ALTER TABLE document_chunks ENABLE ROW LEVEL SECURITY;
CREATE POLICY chunks_tenant ON document_chunks
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE INDEX document_chunks_embedding_hnsw_idx
  ON document_chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
 
-- Query-time recall vs latency
SET hnsw.ef_search = 100;

SELECT id, document_id, content,
       embedding <=> $1::vector AS distance
FROM document_chunks
WHERE tenant_id = $2::uuid
ORDER BY embedding <=> $1::vector
LIMIT 20;

OLTP primary (accounts, billing)
        │
        └── Logical replication subset → search instance (chunks only)