# Neon pgrag Standard

> **Scope:** neon
> **Layer:** 2 (on keyword)
> **Keywords:** pgrag, rag, chunking, embedding, reranking, retrieval
> **Load When:** pgrag or in-database RAG keywords detected

**Verified against:** Neon pgrag (`rag` extension family — experimental, see Core Rules). Last-verified: 2026-05-20.

---

Stack: Neon Serverless Postgres

## Core Rules

- pgrag is EXPERIMENTAL -- API may change between Neon releases
- ALWAYS enable unstable extensions before installing: `SET neon.allow_unstable_extensions='true'`
- Use pgrag for simple, self-contained RAG pipelines (PDF ingestion, chunking, search)
- Use application-level custom RAG (a typed MAF tool over pgvector — see standard ai-agents-rag-custom-pgvector) for complex workflows requiring custom logic, multi-model orchestration, or .NET integration
- NEVER use pgrag local embeddings (384 dims) when your application uses a different embedding model -- dimension mismatch breaks search

## Install pgrag

```sql
-- Enable unstable extensions (required)
SET neon.allow_unstable_extensions='true';

-- Core extension
CREATE EXTENSION IF NOT EXISTS rag CASCADE;

-- Local embedding model (bge-small-en-v1.5, 384 dimensions, 33M params)
CREATE EXTENSION IF NOT EXISTS rag_bge_small_en_v15 CASCADE;

-- Local reranker (jina-reranker-v1-tiny-en, 33M params)
CREATE EXTENSION IF NOT EXISTS rag_jina_reranker_v1_tiny_en CASCADE;
```

## Text Extraction

Extract text from binary documents directly in SQL:

```sql
-- Extract text from PDF
SELECT rag.text_from_pdf(pg_read_binary_file('/path/to/document.pdf'));

-- Extract text from DOCX
SELECT rag.text_from_docx(pg_read_binary_file('/path/to/document.docx'));

-- Convert HTML to Markdown
SELECT rag.markdown_from_html('<h1>Title</h1><p>Content here</p>');
```

### From a Documents Table

```sql
CREATE TABLE raw_documents (
  id SERIAL PRIMARY KEY,
  filename TEXT NOT NULL,
  content BYTEA NOT NULL,
  extracted_text TEXT
);

-- Extract text on insert
UPDATE raw_documents
SET extracted_text = rag.text_from_pdf(content)
WHERE filename LIKE '%.pdf' AND extracted_text IS NULL;
```

## Chunking Strategies

### Character-Based Chunking

Splits text by character count with overlap for context continuity:

```sql
-- Split into chunks of 1000 chars with 200 char overlap
SELECT unnest(
  rag.chunks_by_character_count(
    'Your long document text here...',
    1000,   -- max_chars per chunk
    200     -- overlap chars
  )
);
```

### Token-Based Chunking

Uses the bge-small-en-v1.5 tokenizer for model-aware splitting:

```sql
-- Split into chunks of 512 tokens with 50 token overlap
SELECT unnest(
  rag_bge_small_en_v15.chunks_by_token_count(
    'Your long document text here...',
    512,    -- max_tokens per chunk
    50      -- overlap tokens
  )
);
```

## Embedding Generation

### Local Embeddings (bge-small-en-v1.5)

Zero-cost, runs on the database server. Produces 384-dimension vectors:

```sql
-- Embedding for a document passage (includes "passage:" prefix internally)
SELECT rag_bge_small_en_v15.embedding_for_passage('Neon is a serverless Postgres platform');

-- Embedding for a search query (includes "query:" prefix internally)
SELECT rag_bge_small_en_v15.embedding_for_query('What is Neon?');
```

### OpenAI Embeddings (text-embedding-3-small)

Remote API call, produces 1536-dimension vectors:

```sql
-- Set API key (per session)
SET neon.ai_api_key = 'sk-...';

-- Generate embedding via OpenAI
SELECT rag.openai_text_embedding_3_small('Neon is a serverless Postgres platform');
```

## Full RAG Pipeline Example

```sql
-- 1. Create chunks table
CREATE TABLE chunks (
  id SERIAL PRIMARY KEY,
  document_id INT REFERENCES raw_documents(id),
  chunk_text TEXT NOT NULL,
  embedding vector(384)
);

-- 2. Chunk and embed a document
WITH doc AS (
  SELECT id, rag.text_from_pdf(content) AS full_text
  FROM raw_documents WHERE id = 1
),
chunked AS (
  SELECT doc.id AS document_id, unnest(
    rag.chunks_by_character_count(doc.full_text, 1000, 200)
  ) AS chunk_text
  FROM doc
)
INSERT INTO chunks (document_id, chunk_text, embedding)
SELECT document_id, chunk_text,
  rag_bge_small_en_v15.embedding_for_passage(chunk_text)
FROM chunked;

-- 3. Create HNSW index
CREATE INDEX idx_chunks_embedding ON chunks
  USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
```

## Similarity Search

```sql
-- Find top 10 similar chunks
SELECT id, chunk_text,
  1 - (embedding <=> rag_bge_small_en_v15.embedding_for_query('What is Neon?')) AS similarity
FROM chunks
ORDER BY embedding <=> rag_bge_small_en_v15.embedding_for_query('What is Neon?')
LIMIT 10;
```

## Reranking

Reranking refines initial vector search results using a cross-encoder model for higher accuracy:

```sql
-- Rerank top 10 vector results down to top 5
WITH vector_matches AS (
  SELECT id, chunk_text,
    1 - (embedding <=> rag_bge_small_en_v15.embedding_for_query('What is Neon?')) AS similarity
  FROM chunks
  ORDER BY embedding <=> rag_bge_small_en_v15.embedding_for_query('What is Neon?')
  LIMIT 10
)
SELECT id, chunk_text, similarity,
  rag_jina_reranker_v1_tiny_en.rerank_distance('What is Neon?', chunk_text) AS rerank_score
FROM vector_matches
ORDER BY rerank_score ASC
LIMIT 5;
```

## LLM Integration in SQL

Send context + question to an LLM directly from SQL:

```sql
-- Set API key
SET neon.ai_api_key = 'sk-...';

-- Ask a question with retrieved context
WITH context AS (
  SELECT string_agg(chunk_text, E'\n---\n') AS combined_context
  FROM (
    SELECT chunk_text FROM chunks
    ORDER BY embedding <=> rag_bge_small_en_v15.embedding_for_query('What is Neon?')
    LIMIT 5
  ) top_chunks
)
SELECT rag.openai_chat_completion(
  json_build_object(
    'model', 'gpt-4o',
    'messages', json_build_array(
      json_build_object('role', 'system', 'content',
        'Answer based on the provided context. If unsure, say so.'),
      json_build_object('role', 'user', 'content',
        format('Context:\n%s\n\nQuestion: What is Neon?', context.combined_context))
    )
  )
) FROM context;
```

## When to Use pgrag vs Application-Level RAG

| Factor | pgrag (In-Database) | SK + pgvector (Application) |
|--------|---------------------|----------------------------|
| Setup complexity | Minimal -- SQL only | Requires .NET service layer |
| Embedding model | Local (384d) or OpenAI | Any model via SK connectors |
| Reranking | Built-in (Jina) | Custom implementation |
| Custom logic | Limited to SQL | Full C# flexibility |
| Multi-model orchestration | Not supported | Native SK capability |
| Production readiness | Experimental | Stable (SK GA) |
| Cost | Zero for local models | API costs for remote models |
| Best for | Prototyping, simple RAG, SQL-only environments | Production .NET applications, complex pipelines |

**Recommendation:** Start with pgrag for prototyping and validation. Migrate to custom RAG (a typed MAF vector-search tool over pgvector — see ai-agents-rag-custom-pgvector) for production .NET applications that need custom retrieval logic, multi-model orchestration, or MAF agent integration.

## Common Mistakes

| Wrong | Right | Why |
|-------|-------|-----|
| Missing `neon.allow_unstable_extensions` | `SET neon.allow_unstable_extensions='true'` | pgrag won't install without it |
| Mixing local (384d) and OpenAI (1536d) embeddings | Use one model consistently per column | Dimension mismatch breaks similarity search |
| No HNSW index on chunks table | Create index after initial data load | Full table scan on every search |
| Using pgrag in production without testing | Validate with representative data first | Extension is experimental, may change |
| Storing API key in SQL scripts | Use session variables or environment config | Keys in scripts leak to version control |